?
Solved

Adding another filter on VBA code in combo box

Posted on 2008-11-18
3
Medium Priority
?
278 Views
Last Modified: 2013-11-27
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.
0
Comment
Question by:jammin140900
3 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22990363
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 22990373
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
 

Author Closing Comment

by:jammin140900
ID: 31518071
Thank you for your help. I tried all three solutions and this was the one that worked.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question