need help with complex data validation (1 Viewer)

[Angelflower]

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

'Set strSQL
strSQL = "SELECT Training, CaregiverName, Count(CaregiverName)AS NameCount, ClassName, Description FROM Incoming_Invoices " & _
"GROUP BY Training, CaregiverName, sum(NameCount), ClassName, Description " & _
"HAVING (((Training)=True))"

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
End If

Exit Sub
Error_Handler:
MsgBox "An error occurred. The error number is " & Err.number & _
" and the description is " & Err.Description
Exit Sub

End Sub

 

[Guus2005]

Your dlookup statement is incorrect.

If Me.ComboClassName.column (0) = 2 And DLookup(ClassName, strSQL) Like "*FOC*" > 1 Then

Check F1 (Access help)

 

[Angelflower]

I think the problem other than have the dlookup wrong was that I was trying to the reference strSQL. I changed my code to reference an actual query in the database and it seems to be working better now.

Private Sub ComboClassName_BeforeUpdate(Cancel As Integer)
'Declare and instantiate the object variables
Dim dbs As Database

On Error GoTo Error_Handler

'Set connetion to current database
Set dbs = CurrentDb

If Me.ComboClassName.Column(0) = 2 And DLookup("[ClassName]", "CaregiverTraining", "[NameCount] > 1") Then
MsgBox "Caregiver maybe not be eligible for payment. Please check past invoices for other classes taken."
Cancel = True
End If

Exit Sub
Error_Handler:
MsgBox "An error occurred. The error number is " & Err.number & _
" and the description is " & Err.Description
Exit Sub

End Sub

 

[Angelflower]

I am getting a data type mismatch on this code. In the debugger tool when I hover of the dolookup nothing is showing... not sure I am doing this right. The dolookup is refering to an actual query in my database.

'Declare and instantiate the object variables
Dim dbs As Database, stDocName As String, stLinkCriteria As String


On Error GoTo Error_Handler

'Set connetion to current database
Set dbs = CurrentDb
stDocName = "CaregiverTrainingListing"
stLinkCriteria = "[DE_memid]=" & "'" & Me![DE_memid] & "'"


If Me.ComboClassName.Column(0) = 2 And DLookup("[CaregiverName]", "CaregiverTraining", "[NameCount] > 1") Then
MsgBox "Caregiver maybe not be eligible for payment. Please check past invoices for other classes taken."
DoCmd.OpenForm stDocName, , , stLinkCriteria
ElseIf Me.ComboClassName.Column(0) = 3 And DLookup("[CaregiverName]", "CaregiverTraining", "[NameCount] > 2") Then
MsgBox "Caregiver maybe not be eligible for payment. Please check past invoices for other classes taken."
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

Exit Sub
Error_Handler:
MsgBox "An error occurred. The error number is " & Err.number & _
" and the description is " & Err.Description
Exit Sub

End Sub

 

[Rich]

"[NameCount] > 1")
"[NameCount]") > 1

 

[Angelflower]

THANKS! That worked! Funny how something as small as putting commas and brackets can mess you up. lol...