Private Sub JobComplete_Click()
Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb
'Define SQL Query
strSQL = "SELECT qryReferenceData.tbl_batches.id, qryReferenceData.customer_id, qryReferenceData.batch_number, qryReferenceData.tbl_batches.batch_reference, qryReferenceData.batch_output_location, qryReferenceData.create_date, qryReferenceData.tbl_reference_data.id, qryReferenceData.tbl_reference_data.batch_reference, qryReferenceData.reference_1, qryReferenceData.reference_2, qryReferenceData.reference_3, qryReferenceData.reference_4, qryReferenceData.reference_5 FROM qryReferenceData LEFT JOIN tbl_scan_data ON qryReferenceData.[tbl_reference_data].[id] = tbl_scan_data.[Reference ID] WHERE (((tbl_scan_data.[Reference ID]) Is Null)) AND tbl_scan_data.[Batch ID] = Me.id;"
Set rs = db.OpenRecordset(strSQL)
'Return Result in Loop until end of results
Do While Not rs.EOF
'Insert code here to list elements
'We will loop through and popup a message box
'for this example
MsgBox "Job: " & rs.Fields(0) & " " & rs.Fields(1) & ": " & rs.Fields(2)
'This would pop a message box for each loop
'and display each Job called from the
'SQL query
'move to the next record for the next loop
rs.MoveNext
Loop
'Close the Database
db.Close
Set db = Nothing
Set rs = Nothing
End Sub