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

<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

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

"That's not necessarily true.'
Can't say I've ever seen it happen ...

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

Select allOpen in new window

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.

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.