Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA Loop when doing SQL Insert

Posted on 2014-07-31
5
Medium Priority
?
176 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

571 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