Use multiple ListBoxes to filter records?

Some time ago with a lot of help from this group I made a reasonably
successful database to keep track of our shop drawings. However the
searching mechanism is too complicated for the occasional user and I
would like to change the whole process.

I would like to duplicate the layout of my form... but each field
becomes a simple "searching" ListBox. On completing any one search
field all the rest are filtered to this value. Subsequent entries in
other ListBoxes further filter the remainder until the number of
records is down to a manageable level.

Is such a searching scheme possible? I use this process to go directly
to a single specific record based on the (unique) drawing number using
code from O'Reilly's Access Cookbook. However in my attempts to use
more than one instance ends in failure. I suspect the solution would
involve dynamically building a query which then would become the basis
for the next ListBox... But I need some major help, advice or possibly
a sample that uses this searching method where I can study the code to
use in my application.

My DB has some 6000 and counting records with 9 fields in each one but
not all the fields have data in them.

Thanks for any help, advice or encouragement.
Ralph

re: Use multiple ListBoxes to filter records?

Rather than the user interface you propose, how about a filter by form setup
(different from the data entry form) that uses a collection of combo boxes
to set a query's criteria.

In the header of your selection/filter form, put unbound combo boxes for
each field you wish to search on. In the query (which is the record source
of the form) make the criteria something like:
Like IIf([forms]![myForm]![myCombo] Is
Null,"*",[forms]![myForm]![myCombo])

Add a command button to run the query, and set the form's detail section to
"continuous form" to show multiple records that mat result from the
selection. In the form's footer, you could add a command button to open a
linked single record form for any record in the continuous detail section.

-Ed


"Ralph2" <nonefound@hotmail.com> wrote in message
news:1von21t32j49hk5t6gb1ue9f5l359371ce@4ax.com...[color=blue]
> Some time ago with a lot of help from this group I made a reasonably
> successful database to keep track of our shop drawings. However the
> searching mechanism is too complicated for the occasional user and I
> would like to change the whole process.
>
> I would like to duplicate the layout of my form... but each field
> becomes a simple "searching" ListBox. On completing any one search
> field all the rest are filtered to this value. Subsequent entries in
> other ListBoxes further filter the remainder until the number of
> records is down to a manageable level.
>
> Is such a searching scheme possible? I use this process to go directly
> to a single specific record based on the (unique) drawing number using
> code from O'Reilly's Access Cookbook. However in my attempts to use
> more than one instance ends in failure. I suspect the solution would
> involve dynamically building a query which then would become the basis
> for the next ListBox... But I need some major help, advice or possibly
> a sample that uses this searching method where I can study the code to
> use in my application.
>
> My DB has some 6000 and counting records with 9 fields in each one but
> not all the fields have data in them.
>
> Thanks for any help, advice or encouragement.
> Ralph[/color]

re: Use multiple ListBoxes to filter records?

Ralph,
normally, one would use comboboxes for this. using listboxes is
possible, but a lot harder, because you have to build the filter for
the next listbox on the fly through code. The problem is that if you
have the MultiSelect property set to anything but None, then you have
to loop through that collection and write the Where clause for
yourself.
At any rate, explanations/examples of doing what you want with
comboboxes and listboxes are here:

http://www.mvps.org/access/forms/frm0028.htm

re: Use multiple ListBoxes to filter records?

pietlinden@hotmail.com wrote:
[color=blue]
> At any rate, explanations/examples of doing what you want with
> comboboxes and listboxes are here:
>
> http://www.mvps.org/access/forms/frm0028.htm[/color]

This is a great article, but I prefer to use the on got focus event for
the combo rather than the afer update event of the combo just selected.
I find it much easier to manage this way, especially if you have
several combos affecting the recordsource of another.

Also, the idea of having an initially empty recordsource for the
table/query rowsources of the dynamic combo boxes is an important idea a
lot of people miss, especially when dealing with a lot of records. If
you have your boxes with an initial rowsource, these all have to be
calculated and run before the form is fully loaded. With empty
rowsources, the form loads very quickly.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Use multiple ListBoxes to filter records?

Tim Marshall wrote:
[color=blue]
> This is a great article, but I prefer to use the on got focus event for
> the combo rather than the afer update event of the combo just selected.[/color]

To follow up - some may say this requeries the combo box even if you've
made no new choices when you go back to it. So I do this (air code):

Private Sub cboBldg_GotFocus()

Dim strSql As String
Dim strCurRowSource As String 'current row source
Dim booC As Boolean 'is it necessary to create/recreate the row
source?
Dim strMsg As String

SysCmd acSysCmdSetStatus, "Wait, retreiving building list"

strCurRowSource = Me.cboBldg.RowSource 'Current rowsource SQL

<snip construction of strSql for new row source which depends on
value of other combo boxes and possibly other controls (option groups
for sort order, etc>

'check to see if current row source SQL is the same - if different,
revise rowsource
'if no row source (strcurrowsource = "") create new SQL

booC = False

If strCurRowSource = "" Then

booC = True

Elseif strCurRowSource <> strSql Then

booC = True

End If

If booC = True Then Me.cboBldg.RowSource = strCurRowSource

Exit_Proc:

SysCmd acSysCmdClearStatus

Exit Sub

End Sub

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Use multiple ListBoxes to filter records?

Crumbs, forgot the essential requery (that's what you get with air code!):

Tim Marshall wrote:
[color=blue]
> Private Sub cboBldg_GotFocus()
>
> Dim strSql As String
> Dim strCurRowSource As String 'current row source
> Dim booC As Boolean 'is it necessary to create/recreate the row
> source?
> Dim strMsg As String
>
> SysCmd acSysCmdSetStatus, "Wait, retreiving building list"
>
> strCurRowSource = Me.cboBldg.RowSource 'Current rowsource SQL
>
> <snip construction of strSql for new row source which depends on
> value of other combo boxes and possibly other controls (option groups
> for sort order, etc>
>
> 'check to see if current row source SQL is the same - if different,
> revise rowsource
> 'if no row source (strcurrowsource = "") create new SQL
>
> booC = False
>
> If strCurRowSource = "" Then
>
> booC = True
>
> Elseif strCurRowSource <> strSql Then
>
> booC = True
>
> End If
>
> If booC = True Then[/color]

Me.cboBldg.RowSource = strCurRowSource

me.cboBldg.Requery

End if[color=blue]
>
> Exit_Proc:
>
> SysCmd acSysCmdClearStatus
>
> Exit Sub
>
> End Sub
>[/color]


--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Use multiple ListBoxes to filter records?

On Mon, 07 Mar 2005 05:19:59 GMT, Ralph2 <nonefound@hotmail.com>
wrote:
[color=blue]
>Some time ago with a lot of help from this group I made a reasonably
>successful database to keep track of our shop drawings. However the
>searching mechanism is too complicated for the occasional user and I
>would like to change the whole process.
>
>I would like to duplicate the layout of my form... but each field
>becomes a simple "searching" ListBox. On completing any one search
>field all the rest are filtered to this value. Subsequent entries in
>other ListBoxes further filter the remainder until the number of
>records is down to a manageable level.
>
>Is such a searching scheme possible? I use this process to go directly
>to a single specific record based on the (unique) drawing number using
>code from O'Reilly's Access Cookbook. However in my attempts to use
>more than one instance ends in failure. I suspect the solution would
>involve dynamically building a query which then would become the basis
>for the next ListBox... But I need some major help, advice or possibly
>a sample that uses this searching method where I can study the code to
>use in my application.
>
>My DB has some 6000 and counting records with 9 fields in each one but
>not all the fields have data in them.
>
>Thanks for any help, advice or encouragement.
>Ralph[/color]
Thanks Ed R; pietlinden and Tim... will try your solutions and see if
I can make something work
Thanks again
Ralph