Filter Form Data by Using Cmb Boxes

J

Julie

I’m trying to filter my data in a form by 3 combo boxes (can use list boxes
if necessary). For instance, my user will choose a store from the Store
combo box. Once the store is chosen, I want the other 2 combo boxes, First
Name and Last Name, to only include those names for the particular store
already chosen. I want the data in the form to be filtered in this way. Do
I need to use code to do this? If so, anyone have that kind of info handy?
Is there a way to do it w/o code?

Thank you!
 
A

Allen Browne

Julie, the basic idea is to set the RowSource property of the combo. You set
it to a query statement limited to the right people.

There's some example code in this link:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

For this particluar case, I'm wondering if you might prefer to have the 2
names combined into one. This example assumes that the combo gets its
records from a staff table named tblEmployee, which has fields EmployeeID,
Last Name, First Name, and StoreID (the store this employee is associated
with.) Also that the combo's first field (the EmployeeID) is zero-width, so
the full name shows instead:

Private Sub cboStoreID_AfterUpdate()
strSql = "SELECT EmployeeID, [Last Name] & "", "" & [First Name] AS
FullName FROM tblEmployee "
If Not IsNull(Me.cboStoreID) Then
strSql = strSql & " WHERE StoreID = " & Me.cboStoreID
End If
strSql = strSql & " ORDER BY [Last Name], [First Name];"
Me.cboEmployeeID.RowSource = strSql
End Sub

Note: If StoreID is a text field (not a Number field), you need extra
quotes:
strSql = strSql & " WHERE StoreID = """ & Me.cboStoreID & """"
 
Top