Adding another filter on VBA code in combo box

Hi Experts,

I have two combo boxes on a form. One with a Company Details and the second with the workers for that company. When a user selects the first Combo, it requeries and populates the second one. I had the following code that worked perfectly..

Private Sub cboAuditCompany_AfterUpdate()
Me.cboAuditor.RowSource = "SELECT AuditorID, AuditorSign, AuditorSubID FROM tblAuditors WHERE [AuditorID] = " & Me.cboAuditCompany & " " & _
"ORDER BY AuditorSign;"

End Sub

I now realised that I want another filter. Some of the workers may have left the company and therefore I don't want those worker's names to be a choice for the combo box. Therefore, I've added another field in the same workers table (tblAuditors) called "Active" (which is a yes/no field). I now need to update the code to also filter on populating workers names only where they are active. I therefore, tried this...

Private Sub cboAuditCompany_AfterUpdate()
Me.cboAuditor.RowSource = "SELECT AuditorID, AuditorSign, AuditorSubID FROM tblAuditors WHERE [AuditorID] = " & Me.cboAuditCompany And [Active] = "True" & " " & _
"ORDER BY AuditorSign;"

End Sub

It doesn't work and gives me "Run Time Error 13- Data Mismatch". Could you please advise what I'm doing wrong?

Thanks
Jammin.
jammin140900Asked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
try this

Private Sub cboAuditCompany_AfterUpdate()
Me.cboAuditor.RowSource = "SELECT AuditorID, AuditorSign, AuditorSubID FROM tblAuditors WHERE [AuditorID] = " & Me.cboAuditCompany & " And [Active] = " & True & " " & _
"ORDER BY AuditorSign;"

End Sub
0
 
Rey Obrero (Capricorn1)Commented:
Private Sub cboAuditCompany_AfterUpdate()
Me.cboAuditor.RowSource = "SELECT AuditorID, AuditorSign, AuditorSubID FROM tblAuditors WHERE [AuditorID] = " & Me.cboAuditCompany & " And [Active] = True & " " & _
"ORDER BY AuditorSign;"

End Sub


or

Private Sub cboAuditCompany_AfterUpdate()
Me.cboAuditor.RowSource = "SELECT AuditorID, AuditorSign, AuditorSubID FROM tblAuditors WHERE [AuditorID] = " & Me.cboAuditCompany & " And [Active] = -1 & " " & _
"ORDER BY AuditorSign;"

End Sub
0
 
jammin140900Author Commented:
Thank you for your help. I tried all three solutions and this was the one that worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.