Solved

VBA Loop when doing SQL Insert

Posted on 2014-07-31
5
171 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 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