The Listbox displays a StepID (number) and a StepName (text).I want to filter the Listbox based upon the values in the comboboxes. After each combobox selection the List becomes shorter and shorter.
Next, the user selects a StepID number from the Listbox and presses the Button to set a Temporary Variable (StepRecord).
To make things complicated: The combobox cboSelectOperation is Cascaded from cboSelectDepartment and cboSelectVariant is Cascaded from cboSelectModel. So I the user selects a Department, the Operation combobox list is limited, the same with Model and Variants.
I've found a tutorial database ("Cascading Combo Boxes") where all the comboboxes are cascaded. Next, I've copied the code and adapted it to my database. But now I'm stuck with the SQL code for filtering (see Private Sub filterList()). This is the code I have now:
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Option Explicit
- Private Sub filterList()
- Dim strRS As String
- ' Filter the list box appropriately based on the combo box selection(s)
- strRS = "SELECT qryFilterList.StepID, qryFilterList.StepName FROM qryFilterList"
- If Not IsNull(Me.cboSelectVariant) Then
- strRS = strRS & " WHERE VariantID = " & Me.cboSelectVariant
- ElseIf Not IsNull(Me.cboSelectModel) Then
- strRS = strRS & " WHERE ModelID = " & Me.cboSelectModel
- ElseIf Not IsNull(Me.cboSelectOperation) Then
- strRS = strRS & " WHERE OperationID = " & Me.cboSelectOperation
- ElseIf Not IsNull(Me.cboSelectDepartment) Then
- strRS = strRS & " WHERE DeptID = " & Me.cboSelectDepartment
- End If
- strRS = strRS & " ORDER BY qryFilterList.StepName;"
- Me.selectionList.RowSource = strRS
- Me.selectionList.Requery
- End Sub
- Private Sub cboSelectDepartment_AfterUpdate()
- Me.cboSelectOperation.RowSource = "SELECT tblOperations.OperationID,tblOperations.Operation, tblOperations.Description FROM tblOperations " & _
- " WHERE DeptID = " & Nz(Me.cboSelectDepartment) & _
- " ORDER BY Operation"
- Me.cboSelectOperation = Null
- filterList
- End Sub
- Private Sub cboSelectModel_AfterUpdate()
- Me.cboSelectVariant.RowSource = "SELECT qryVariant.variantID, qryVariant.Variant FROM qryVariant " & _
- " WHERE ModelID = " & Nz(Me.cboSelectModel) & _
- " ORDER BY Variant"
- Me.cboSelectVariant = Null
- filterList
- End Sub
- Private Sub cboSelectOperation_AfterUpdate()
- filterList
- End Sub
- Private Sub cboSelectVariant_AfterUpdate()
- filterList
- End Sub
- Private Sub Form_Load()
- filterList
- Me.selectionList.RowSource = ""
- End Sub
- Private Sub EnableControls()
- ' Clear the combo boxes
- If IsNull(Me.cboSelectDepartment) Then
- Me.cboSelectOperation = Null
- End If
- If IsNull(Me.cboSelectOperation) Then
- Me.cboSelectModel = Null
- End If
- If IsNull(Me.cboSelectModel) Then
- Me.cboSelectVariant = Null
- End If
- ' Enable or disable combo boxes based on whether the combo box preceeding it has a value.
- Me.cboSelectOperation.Enabled = (Not IsNull(Me.cboSelectDepartment))
- Me.cboSelectModel.Enabled = (Not IsNull(Me.cboSelectOperation))
- Me.cboVariant.Enabled = (Not IsNull(Me.cboSelectModel))
- End Sub
I think I need to put a AND operator in the SQL part to combine all the filters. Unfortunately I don't have any experience with creating a SQL code. Can yo guys help me or point me in the right direction?
Thanks!