Using multiple comboboxes to populate one listbox

[Exl2Acc]

Hello...



The database I working with is a simple employee database and I have several ComboBoxes populated with various components of employees profile. E.g., EmpID's; First Names; Last Names; Supervisors; Shift Start Times; etc.

What I am trying to achieve is, for example, if the user selects a Shift Start Time from the Shift Start Time ComboBox I would like the ListBox to display all employee records with that Shift Start Time. If the user selects or types an employee's first name in the First Name ComboBox, I would like the ListBox to show all employees with that first name and so on.

I have this concept working with the Supervisor ComboBox using VBA and a Requery event. But I am unable to get this idea to work across multiple ComboBoxes

Thanks in advance for guidance.

[Bulzie]

Here is some code to check multiple fields for a search and creates a Where clause based on which fields have data, then populates a listbox with the results. Change the field names to your own. You can see examples of how to do numbers, text and Like conditions of you want it to do some fuzzy searches. Put the function on your form and call it using =QueryData() in the AfterUpdate of each combo box search field.

Public Function QueryData()

Dim strWhere, strsqlproject As String

'** Create a dynamic Where statement from all the search criteria fields that they enter data into on the form.

If Not IsNull(Me!ProjectID) Then strWhere = strWhere & " AND ProjectID=" & Me!ProjectID
If Not IsNull(Me!ProjectDescription) Then strWhere = strWhere & " AND ProjectDescription Like '*" & Me!ProjectDescription & "*'"
If Not IsNull(Me!RiskID) Then strWhere = strWhere & " AND RiskID=" & Me!RiskID
If Not IsNull(Me!RiskStatusDateLT) Then strWhere = strWhere & " AND RiskStatusDate<=#" & Me!RiskStatusDateLT & "#"

'** Create SQL statement
strsqlproject = "Select ProjectID, ProjectName from qryAllData"


'** Trim leading " AND " from the where clause, then add a GroupBy and OrderBy
If Not IsNull(strWhere) And strWhere <> "" Then
strsqlproject = strsqlproject & " WHERE " & Mid$(strWhere, 6)
End If

strsqlproject = strsqlproject & " GROUP BY qryAllData.ProjectID, qryAllData.ProjectName"
strsqlproject = strsqlproject & " ORDER BY qryAllData.ProjectName"

'** Update the RowSource of the Listbox and requery
Forms![frmMain]![frmQuery].Form![ProjectList].RowSource = strsqlproject
Forms![frmMain]![frmQuery].Form![ProjectList].Requery

End Function