Solved

VBA Loop when doing SQL Insert

Posted on 2014-07-31
5
167 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now