Access 2007 - VBA continues to run but SQL query not finish updating

Hi Experts,

I have the following sub routine which consist of many other sub routines. It work as follows:

1

Each sub routine has its own sql query to pull the relevant information from various tables and queries.

2

One or two of the routine requires populating a textbox and then using that textbox to update a table in order to get around the ms access non-compliant update query problem.

3

The problem i'm having is that the sql query hadn't finish running or updating and the VBA code just continues to run therefore no value is populated in the text box for it to be updated into the tables.

4

I've tried to use DoEvents as suggested on another post but doesn't seem to make much difference.
Can you please help suggest a technique i can use to make sure the query finish updating before VBA code continues.

Thanks


Private Sub UpdateSubfrm_Click()

Call UpdateInvoiceType
DoEvents
Call TextBoxRecal
DoEvents
Call FillSubfrm
DoEvents
Call CalTxtTotalAmount
DoEvents
Call UpdateInvoiceStdID
DoEvents
Call UpdateTotalTransactionAmount
DoEvents
Call DiscountQuery
DoEvents
Call DiscountQuery
DoEvents
Call DiscToFinancial
Call NavButtonActivate
Me.Refresh

End Sub

Open in new window

Ricky NguyenITAsked:
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" the ms access non-compliant update query problem. "
And what exactly are you referring to ?

Also, until a given query completes, no other code is going to run. So, something else is going on ...

mx

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<Also, until a given query completes, no other code is going to run. >

That's not necessarily true. If Query1 is especially long running, then Query2 can certainly start before Query1 is finished.

<The problem i'm having is that the sql query hadn't finish running or updating and the VBA code just continues to run therefore no value is populated in the text box >

If your query requires a value in a Textbox, then check that Textbox before you run the query:

If Nz(Forms("SomeForm").YourTextbox, "") = "" Then
  Msgbox "No value in the textbox"
Else
  '/run your query
End If
ste5anSenior DeveloperCommented:
VBA code is executed sequential.  If you're using DAO.Recordsets and CurrentDb.Execute SQL, dbFailOnError then the code will only continue, if the queries are 'finished'. The only problem I can see here, is the fact that you're populating controls (TextBox'es) and you're calling DoEvents.

This means when you fill in your first sub routine a TextBox, exit this sub and then call DoEvents, any awaiting event method for this control is executed before your code continues. So I suspect you're having here some event havoc.

You should inspect your calling stack of sub methods and all involved event methods carefully. I use normally a Debug.Print "MethodName() entered." at the beginning of each involved method. This will output a calling stack in the immediate window. In your case, you should also track when your code leaves your methods.


mfG
--> stefan <--

btw, calling the sub DiscountQuery twice indicates poor code design. Use at least a parameter for that.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Rey Obrero (Capricorn1)Commented:
i will try to change each  routines into a boolean function

Private Sub UpdateSubfrm_Click()

if UpdateInvoiceType=false then
    msgbox "UpdateInvoiceType failed"
    exit sub
elseif TextBoxRecal =false then
    msgbox "TextBoxRecal failed"
    exit sub    


etc...
end if
end sub


for each function test for results and return a true for successful run


DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"That's not necessarily true.'
Can't say I've ever seen it happen ...

Ricky NguyenITAuthor Commented:
thanks everyone for your comments. I have the following calling stack:

Form_Current() entered                'Click in date textbox to select date
Form_Current() left                       '
Form_Current() entered                'Clicked on StudentID textbox to select student
UpdateInvoiceType() entered        'Clicked on Updatesubform button to initiate calling stack
UpdateInvoiceType() Left
TextBoxRecal() entered
TextBoxRecal() left
FillSubfrm() entered
SQL INSERT Query                        'Does not insert data to table & populate subform, however when
                                                       testing insert query it gives valid result (see (i) below for full query)
FillSubfrm() left
CalTxtTotalAmount() entered
CalTxtTotalAmount() left
UpdateInvoiceStdID() entered
UpdateInvoiceStdID() left
UpdateTotalTransactionAmount() entered


Not sure why its falling over when calling fillsubfrm(). It works when called on its own but not when part of a calling stack.




(i) INSERT INTO tblInvoiceLineItems ( TransactionID, AttendanceID ) SELECT 90 AS Exp1, OutstandingAttendance.tblAttendance.AttendanceID FROM (SELECT tblStudents.StudentID, tblClassMeeting.WeekID, tblAttendance.AttendanceID, tblAttendance.ClassMeetingID, tblAttendance.Attendance, tblClassMeeting.ClassDate, tblClassProducts.ClassProductID, tblInvoiceLineItems.TransactionID FROM (tblStudents INNER JOIN (tblClassProducts INNER JOIN (tblClassMeeting INNER JOIN tblAttendance ON tblClassMeeting.ClassMeetingID=tblAttendance.ClassMeetingID) ON tblClassProducts.ClassProductID=tblClassMeeting.ClassProductID) ON tblStudents.StudentID=tblAttendance.StudentID) LEFT JOIN tblInvoiceLineItems ON tblAttendance.AttendanceID=tblInvoiceLineItems.AttendanceID WHERE (((tblStudents.StudentID)=2) AND ((tblAttendance.Attendance)=Yes)))  AS OutstandingAttendance WHERE (((OutstandingAttendance.tblAttendance.AttendanceID) Not In (select tblInvoiceLineItems.AttendanceID from tblInvoiceLineItems)));

Private Sub FillSubfrm()
Dim SQL1 As String
Dim SQL2 As String
Dim SQL3 As String

On Error GoTo Err
Debug.Print "FillSubfrm() entered"

'RunCommand acCmdSaveRecord

'Copy outstanding AttendanceID (ie. student attended a class but have not been invoiced)
'with corresponding TransactionID to InvoiceLineItems table.
SQL1 = "INSERT INTO tblInvoiceLineItems ( TransactionID, AttendanceID ) " & _
"SELECT " & Me.TransactionID & " AS Exp1, OutstandingAttendance.tblAttendance.AttendanceID " & _
"FROM (SELECT tblStudents.StudentID, tblClassMeeting.WeekID, tblAttendance.AttendanceID, tblAttendance.ClassMeetingID, " & _
"tblAttendance.Attendance, tblClassMeeting.ClassDate, tblClassProducts.ClassProductID, " & _
"tblInvoiceLineItems.TransactionID FROM (tblStudents INNER JOIN (tblClassProducts INNER JOIN " & _
"(tblClassMeeting INNER JOIN tblAttendance ON tblClassMeeting.ClassMeetingID=tblAttendance.ClassMeetingID) ON " & _
"tblClassProducts.ClassProductID=tblClassMeeting.ClassProductID) ON tblStudents.StudentID=tblAttendance.StudentID) " & _
"LEFT JOIN tblInvoiceLineItems ON tblAttendance.AttendanceID=tblInvoiceLineItems.AttendanceID " & _
"WHERE (((tblStudents.StudentID)=" & Me.cbStudentID.Column(0) & ") AND ((tblAttendance.Attendance)=Yes)))  AS OutstandingAttendance " & _
"WHERE (((OutstandingAttendance.tblAttendance.AttendanceID) Not In (select tblInvoiceLineItems.AttendanceID from tblInvoiceLineItems)));"

SQL2 = "INSERT INTO tblInvoiceLineItems ( TransactionID, AttendanceID ) " & _
"SELECT " & Me.TransactionID & " AS Exp1, OutstandingAttendance.tblAttendance.AttendanceID " & _
"FROM (SELECT tblStudents.StudentID, tblClassMeeting.WeekID, tblAttendance.AttendanceID, tblAttendance.ClassMeetingID, " & _
"tblAttendance.Attendance, tblClassMeeting.ClassDate, tblClassProducts.ClassProductID, " & _
"tblInvoiceLineItems.TransactionID FROM (tblStudents INNER JOIN (tblClassProducts INNER JOIN " & _
"(tblClassMeeting INNER JOIN tblAttendance ON tblClassMeeting.ClassMeetingID=tblAttendance.ClassMeetingID) ON " & _
"tblClassProducts.ClassProductID=tblClassMeeting.ClassProductID) ON tblStudents.StudentID=tblAttendance.StudentID) " & _
"LEFT JOIN tblInvoiceLineItems ON tblAttendance.AttendanceID=tblInvoiceLineItems.AttendanceID " & _
"WHERE (((tblStudents.StudentID)=" & Me.cbStudentID.Column(0) & ") AND ((tblClassMeeting.WeekID)<=" & Me.txtLastWeek & ") AND ((tblAttendance.Attendance)=Yes)))  AS OutstandingAttendance " & _
"WHERE (((OutstandingAttendance.tblAttendance.AttendanceID) Not In (select tblInvoiceLineItems.AttendanceID from tblInvoiceLineItems)));"

SQL3 = "INSERT INTO tblInvoiceLineItems ( TransactionID, AttendanceID ) " & _
"SELECT " & Me.TransactionID & " AS Exp1, OutstandingAttendance.tblAttendance.AttendanceID " & _
"FROM (SELECT tblStudents.StudentID, tblClassMeeting.WeekID, tblAttendance.AttendanceID, tblAttendance.ClassMeetingID, " & _
"tblAttendance.Attendance, tblClassMeeting.ClassDate, tblClassProducts.ClassProductID, " & _
"tblInvoiceLineItems.TransactionID FROM (tblStudents INNER JOIN (tblClassProducts INNER JOIN " & _
"(tblClassMeeting INNER JOIN tblAttendance ON tblClassMeeting.ClassMeetingID=tblAttendance.ClassMeetingID) ON " & _
"tblClassProducts.ClassProductID=tblClassMeeting.ClassProductID) ON tblStudents.StudentID=tblAttendance.StudentID) " & _
"LEFT JOIN tblInvoiceLineItems ON tblAttendance.AttendanceID=tblInvoiceLineItems.AttendanceID " & _
"WHERE (((tblStudents.StudentID)=" & Me.cbStudentID.Column(0) & ") AND ((tblClassMeeting.WeekID)<=" & Me.txtLastWeek & ") AND ((tblClassMeeting.WeekID)>=" & Me.txtStartWeek & ") AND ((tblAttendance.Attendance)=Yes)))  AS OutstandingAttendance " & _
"WHERE (((OutstandingAttendance.tblAttendance.AttendanceID) Not In (select tblInvoiceLineItems.AttendanceID from tblInvoiceLineItems)));"


If IsNull(Me.txtStartWeek) And IsNull(Me.txtLastWeek) Then
    Debug.Print "SQL1: "
    Debug.Print SQL1
    Me.subInvoiceLineItems.Form.DataEntry = True
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL1, True
    DoCmd.SetWarnings True

    Me.subInvoiceLineItems.Form.DataEntry = False
    Me.subInvoiceLineItems.Requery  '<<< ADD this line
    
ElseIf IsNull(Me.txtStartWeek) And Not IsNull(Me.txtLastWeek) Then
    Debug.Print "SQL2: "
    Debug.Print SQL2
    Me.subInvoiceLineItems.Form.DataEntry = True
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL2, True
    DoCmd.SetWarnings True

    Me.subInvoiceLineItems.Form.DataEntry = False
    Me.subInvoiceLineItems.Requery  '<<< ADD this line

ElseIf Not IsNull(Me.txtStartWeek) And Not IsNull(Me.txtLastWeek) Then
    Debug.Print "SQL3: "
    Debug.Print SQL3
    Me.subInvoiceLineItems.Form.DataEntry = True
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL3, True
    DoCmd.SetWarnings True

    Me.subInvoiceLineItems.Form.DataEntry = False
    Me.subInvoiceLineItems.Requery  '<<< ADD this line

Else
    MsgBox "You must have end date"

End If

Debug.Print "FillSubfrm() left"

ExitPoint:
    Exit Sub

Err:
        Call MsgBox("Error Description: " & Err.Description & vbCrLf & _
        "Error Number: " & Err.Number, , "Error")
        Resume ExitPoint

End Sub

Open in new window

Ricky NguyenITAuthor Commented:
I should add that the very last sub routine is when the vba code stops because of the sub routine:
UpdateTotalTransactionAmount()

This above sub routine would try to update a null value to the relevant tables (therefore error caused) because of FillSubfrm() not able to pull the line items to the subform for the total amounts to be calculated.
Ricky NguyenITAuthor Commented:
Thanks very much mx & Stefan. Your comments really helped me. I'm new to Access so Stefan's comment to analyse the call stacking really help with pinpointing the problem and mx is right, the problem was not with sql query not running sequentially. It was a problem with the textbox value not being saved to the relevant table for the query to use.

I apologise for misleading everyone with my question. I assumed it was a problem with the running of the query because of reading some other posts that mentioned the running of queries to be the problem as is also mentioned by LSMConsulting.
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
Microsoft Access

From novice to tech pro — start learning today.