How to filter List Box based on multiple Combo Boxes

[Adnan]

Good day Everyone,

I have a List Box (lstData) and three Combo Boxes (cmb1, cmb2 & cmb3).
I want the List Box to auto-populate based on the selection made from
the three combo boxes (or one, or what ever many combo boxes were selected).

In the beginning, display all the data and then narrow then down as you
select from the Combo Boxes.

Thank you,
Adnan

 

[Michael Gramelspacher]

Good day Everyone,

I have a List Box (lstData) and three Combo Boxes (cmb1, cmb2 & cmb3).
I want the List Box to auto-populate based on the selection made from
the three combo boxes (or one, or what ever many combo boxes were selected).

In the beginning, display all the data and then narrow then down as you
select from the Combo Boxes.

Thank you,
Adnan

OK, since you did not supply any real data and data types aand row sources. etc., I assume you are
just asking about the general idea.

Private Sub cbo1_AfterUpdate()

Dim s As String
Dim c As Variant

' add to WHERE clause criteria
If Not IsNull(Me.cbo1) Then
c = (c + " AND ") & "customer_id = '" & Me.cbo1 & "'"
End If

' add to WHERE clause criteria
If Not IsNull(Me.cbo2) Then
c = (c + " AND ") & "project_id = '" & Me.cbo2 & "'"
End If

' add to WHERE clause criteria
If Not IsNull(Me.cbo3) Then
c = (c + " AND ") & "subproject_id = '" & Me.cbo3 & "'"
End If


' eliminate initial AND
If Left(c, 5) = " AND " Then
c = Mid(c, 6)
End If

' if c is empty then use zero
If IsNull(c) Then c = "0"

' combine initial SQL string and WHERE clause criteria
s = "SELECT * FROM CustomerProjects WHERE " & c & ";"


Me.lstData.RowSource = s

End Sub

Private Sub cbo2_AfterUpdate()

cbo1_AfterUpdate

End Sub

Private Sub cbo3_AfterUpdate()

cbo1_AfterUpdate

End Sub

 

[tina]

easy enough. in the listbox's RowSource property, click on the ellipsis
(...) button at the right side. when the query builder opens, set criteria
on each field where you want to use the value of a combobox control, as

Forms!FormName!cmb1 Or Forms!FormName!cmb1 Is Null

replace FormName with the correct name of the form, of course. close the
query builder, saving the changes when the message box asks. in each
combobox's AfterUpdate event procedure, requery the listbox control, as

Me!lstData.Requery

hth

 

[Adnan]

Michael,

Thanks for your prompt response. I now understand the general ides but still
need a little push.
The data type for list box are (Row Source) “SELECT tblPPRs.EstID,
tblPPRs.TO, tblPPRs.WorkType, tblPPRs.TrackingNr, tblPPRs.SoW,
tblPPRs.Status, tblPPRs.AllocatedTO FROM tblPPRs ORDER BY
tblPPRs.DateClientIssued DESC;â€

The Combo Boxes to search are as follows:
cbo1 = to filter TrackingNr
cbo2 = to filter Status
cbo3 = to filter TO

I also failed to monition on my previous post that all these Combo Boxes and
List Box are on a subForm called (frmPPRs).

Thanks for all your help,
Adnan

 

[Adnan]

Tina,

Thanks for your great help. Sorry I forgot to mentioned on my previous post
that all these objects (Combo Boxes and List Box) are on a subform. How do I
implement this now?

subForm's name is "frmPPRs"

 

[tina]

still easy enough. for illustration purposes, i'll call the mainform
"frmMain". next you need the *name of the subform control within the
mainform*. to get that, open the mainform in Design view. click ONCE on the
subform *within the mainform* to select it. open the Properties box, select
the Other tab, and look at the Name property; that's the name of the subform
control within the mainform. again for illustration purposes, i'll call it
ChildDetails. (for step-by-step instructions with screen prints, in PDF
format, go to http://home.att.net/~california.db/instructions.html and click
on the SubformControlName link.)

now let's get back to the criteria for the SQL string in the listbox
control's RowSource, that i posted previously. change the reference to

Forms!frmMain!ChildDetails.Form!cmb1 Or Forms!frmMain!ChildDetails.Form!cmb1
Is Null

the above goes all on one line in the query builder grid, regardless of
line-wrap here. and replace the "dummy" names i used, with the correct names
of your mainform and subform control, of course.

hth

 

[Adnan]

Tina,

It worked. Thank you so much for your great help provided.

Adnan

 

[tina]

you're welcome

Tina,

It worked. Thank you so much for your great help provided.

Adnan