Post reply

Access VBA Defies Logic

  • July 2, 2005 at 5:13 pm

    #67722

    Hi,

    Access VBA is baffling my mind.  I'll give you the full details below, but the gist of the problem is when I insert a record programetically into a table using ADO, and open a form that uses that table as its record source, the new record doesn't show up until I close the form and open it again.  Even the record count is off: when I programetically open the form it shows 877 records, when I manually close it and open it again it shows 878.

    Here's the details of my problem, read on if you care to...

    I'm doing a marketing database for a consulting firm.  They need to keep track of Opportunities, Proposals and Projects.  Here is a simplified data schema...

    Database schema

    As you can see, each Opportunity CAN lead to 1 or more proposals, but it doesn't have to (in reality only about 1/3 do).  The same situation exists with Proposals to Projects.

    Some of you who work for yourself may find this familiar.  Anyway, in tbl_Opportunities, the OpportunityStatus field is a Combo box.  The possible statuses are 'New Opportunity', 'Undecided', 'Passed', and 'Proposal'.  The onChange event for that combo box checks to see if the new value is 'Proposal'.  If it is, it

    1. Inserts a new record into tbl_Proposals using an ADO Insert Command.
    2. Opens up the Proposals form so the user can enter in the pertinent Proposal data.

    Here is the VBA code that is supposed to do that...

    Private Sub OpportunityStatus_Change()
       If Me.OpportunityStatus.Value = 5 Then  'Proposal

          'Check to see if there already is a proposal (omited)

          cnxn.Open CurrentProject.Connection

                Dim cmdCommand As New ADODB.Command

                cmdCommand.ActiveConnection = cnxn

                cmdCommand.CommandText = "INSERT INTO tblProposals " _

                   (fkOpportunityID) VALUES (" & Me.pkOpportunityID.Value & ")"

                cmdCommand.Execute

                Set cmdCommand = Nothing

                cnxn.Close

                Set cnxn = Nothing

     
                'Go to new Proposal

                DoCmd.OpenForm "Proposals"

                DoCmd.GoToRecord , , acLast

                DoCmd.Close acForm, "Opportunities"
       End If
    End Sub

    The first part of the code works fine, the record is inserted, but 99% of the time the second part does not take the user to the record that was just inserted, as I mentioned above, the record count is off until I close the form and re-open it.

     
    When it does work it's always the first time I attempt to do this after opening the MDB file, that always works, subsequent attemps to not, even if I close the form and re-open it.
     
    I've also tried doing a search in the Proposals form, instead of going to the last record, as in...
     
                Forms!Proposals.fkOpportunityID.SetFocus

                DoCmd.FindRecord OID, , , , acSearchAll, , acCurrent

                DoCmd.Close acForm, "MainFormOpps"

  • July 3, 2005 at 12:05 pm

    #570536

    Please do not cross-post.

    Finish the thread here : http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=131&messageid=196547

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply