VBA Loop when doing SQL Insert

When I execute the following code in the On Current Event when opening a form I get into a loop.  I set a Breakpoint on the second line in this example.  All Me. fields are defined on the form and are unlocked.  I get no warning messages.

Upon the Breakpoint, the code executes until it gets to the Me.Requery and then execution goes back up to the Breakpoint statement.

Dim SRID As Long
    If IsNull(OpenArgs) Then    'ServiceID
        MsgBox ("No Service Request ID passed.")
        Exit Sub
    Else    'Set up for form updated Input
        SRID = OpenArgs
        MsgBox ("SRID = " & OpenArgs)

        Me.SCnt.Locked = False
        Me.EMiles.Locked = False
        Me.ServiceID.Locked = False
        Me.HomeCity.Locked = False
       
        DoCmd.SetWarnings False
        'blank temp table
        DoCmd.RunSQL ("DELETE * FROM tmpServiceStops")
        'load SRs Service Stops record
        DoCmd.RunSQL ("INSERT INTO tmpServiceStops (ServiceID, HomeCity, ServiceStopTypeID1, ServiceStop1, ServiceStopCity1, ServiceStopTypeID2, ServiceStop2, ServiceStopCity2,ServiceStopTypeID3, ServiceStop3, ServiceStopCity3,ServiceStopTypeID4, ServiceStop4, ServiceStopCity4, ServiceStopTypeID5, ServiceStop5, ServiceStopCity5, ServiceStopTypeID6, ServiceStop6, ServiceStopCity6, ServiceStopCount, EstTotalMileage) SELECT ServiceID, HomeCity, ServiceStopTypeID1, ServiceStop1, ServiceStopCity1, ServiceStopTypeID2, ServiceStop2, ServiceStopCity2,ServiceStopTypeID3, ServiceStop3, ServiceStopCity3,ServiceStopTypeID4, ServiceStop4, ServiceStopCity4, ServiceStopTypeID5, ServiceStop5, ServiceStopCity5, ServiceStopTypeID6, ServiceStop6, ServiceStopCity6, ServiceStopCount, EstTotalMileage FROM tblServiceStops WHERE ServiceID = " & OpenArgs)
        DoCmd.SetWarnings True
        Me.Requery
        Me.HomCityTxt = DLookup("Destination", "refDestination", "DestinationID = " & Me.HomeCity)
       
        Me.SCnt.Locked = True
        Me.EMiles.Locked = True
        Me.ServiceID.Locked = True
        Me.HomeCity.Locked = True
       
    End If
End Sub
JudithARyanTech SupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
"...Me.Requery and then execution goes back up to the Breakpoint statement."

The requery is triggering the OnCurrent event so it fires again?
OM Gang
0
omgangIT ManagerCommented:
From http://msdn.microsoft.com/en-us/library/office/aa211356(v=office.11).aspx

The Current event also occurs when you refresh a form or requery the form's underlying table or query — for example, when you click Remove Filter/Sort on the Records menu or use the Requery action in a macro or the Requery method in Visual Basic code.

OM Gang
0
JudithARyanTech SupportAuthor Commented:
Ok.  The fields in my form are filled by the INSERT into the tmp table I'm doing (In other words, the Data Source is the tmp table).  By eliminating the Me.Requery, the form opens without showing the new info in the tmp table.  Is there an event after On Current that I can use for the requery?

Sorry for the delay in answering your comments.  Judith
0
omgangIT ManagerCommented:
I'd try moving the procedure to the OnOpen or OnLoad event of the form instead.
OM Gang
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JudithARyanTech SupportAuthor Commented:
It worked!  Thanks for your help.  I need to do some research on what each Event does and what can/cannot be done in each.

Thanks so much!  Judith
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.