Solved

VBA Loop when doing SQL Insert

Posted on 2014-07-31
5
169 Views
Last Modified: 2014-08-06
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
0
Comment
Question by:JudithARyan
  • 3
  • 2
5 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 40233049
"...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
 
LVL 28

Expert Comment

by:omgang
ID: 40233052
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
 

Author Comment

by:JudithARyan
ID: 40244493
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
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 40244530
I'd try moving the procedure to the OnOpen or OnLoad event of the form instead.
OM Gang
0
 

Author Closing Comment

by:JudithARyan
ID: 40244999
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question