need help with complex data validation
I am developing a form to process training taken by caregivers. On the form I have a field where the user will select the class. After the class is selected I must validate if the caregiver can actually take the class or not. This depends on how many times they have tried taking the class or if they took another version of the class. I am at a lost here of how to work through my flow chart PDF file that I am attaching.
The form is populating a table in my code I tried to create a query for use as a lookup for my validation but when I run the code I get an error message that says it cant find the query I am looking for.
Here is the code I have so far:
Private Sub ComboClassName_BeforeUpdate(Cancel As Integer)
'Declare and instantiate the object variables
Dim dbs As Database, strSQL As String
On Error GoTo Error_Handler
'Set connetion to current database
Set dbs = CurrentDb
strSQL = "SELECT Training, CaregiverName, Count(CaregiverName)AS NameCount, ClassName, Description FROM Incoming_Invoices " & _
"GROUP BY Training, CaregiverName, sum(NameCount), ClassName, Description " & _
If Me.ComboClassName.column (0) = 2 And DLookup(ClassName, strSQL) Like "*FOC*" > 1 Then
MsgBox "Caregiver maybe not be eligible for payment. Please check past invoices for other classes taken."
Cancel = True
MsgBox "An error occurred. The error number is " & Err.number & _
" and the description is " & Err.Description