Filter a List Box Using a Combo Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a dialog box that contains a list box of all my records; basically
this is used as a way for a person to find/edit a previously-entered record
(select from list box and click to edit/view). The list box is based on a
simple query.

I want to add a combo box that will allow me to filter the list box by one
of the fields - so the user could select what type of record he/she would
like to see, and the list box would automatically filter. I know I need an
After Update event on the combo box, but I can't figure out the code.

The complicating factor is that there are other filters at work on the same
list box: I also have a toggle button that makes the list include/not include
items marked as "archived", and an option group that allows the user to
change the sort order. Therefore, I don't want to go back to the query to get
the SQL for the list box, as it won't include any of the other filters that
have been applied in the form. I need to grab the SQL from the list box as it
currently exists, then modify this to filter by type of record.

Hope this makes sense and that someone can offer some guidance on how to go
about this!

Thanks,
Jen
 
Have one form function that builds the required SQL, taking into account all
of the filtering controls and then applies it to the list box. Call this from
the AfterUpdate of each of the controls.

HTH
John
##################################
Don't Print - Save trees
 
Thanks John. Sorry for my late response - I never got notification of a
reply, so I've gone all this time thinking no one answered!

I'm not sure how to go about doing what you describe. How can I grab the
current filtering information from the page and then put it all together into
a SQL statement? Your further guidance would be much appreciated!

Jen
 
You will need to substitute your own names but something along these lines
should get you started:

Private Sub BuildFilter()
Dim SQL As String
SQL = ""
If Nz(Text1) <> "" then SQL = "field1 = '" & Text1 & "'"
If Nz(Text2) <> "" then
If SQL <> "" then SQL = SQL & " AND "
SQL = "field2 = '" & Text2 & "'"
End If
If Nz(Text3) <> "" then
If SQL <> "" then SQL = SQL & " AND "
SQL = "field3 = '" & Text3 & "'"
End If
If Not ToggleBtn then
If SQL <> "" then SQL = SQL & " AND "
SQL = "archived = False"
End If
If SQL = "" Then
Me.FilterOn = False
Else
Me.Filter = SQL
Me.FilterOn = True
End If
End Sub

It's air-code so sorry for any typos!

You can either call this from the AfterUpdate of each of the controls or if
the user would make several changes to the filter and then want to see the
records then call from a command button.

HTH
John
##################################
Don't Print - Save trees
 
Back
Top