472,353 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

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
Nov 13 '05 #1
6 4506
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" <no*******@hotmail.com> wrote in message
news:1v********************************@4ax.com...
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

Nov 13 '05 #2
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

Nov 13 '05 #3
pi********@hotmail.com wrote:
At any rate, explanations/examples of doing what you want with
comboboxes and listboxes are here:

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


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
Nov 13 '05 #4
Tim Marshall wrote:
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.


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
Nov 13 '05 #5
Crumbs, forgot the essential requery (that's what you get with air code!):

Tim Marshall wrote:
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

me.cboBldg.Requery

End if
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
Nov 13 '05 #6
On Mon, 07 Mar 2005 05:19:59 GMT, Ralph2 <no*******@hotmail.com>
wrote:
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

Thanks Ed R; pietlinden and Tim... will try your solutions and see if
I can make something work
Thanks again
Ralph
Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: softengine | last post by:
Can and how do you alter a data view to include a look up field from another data table? The data table of the dataview only has the key, the value...
3
by: arthur-e | last post by:
I can filter a query for a report on a field of a subform BUT now.... I'd like to be able to select more than one item in a multi-list box to...
0
by: Terry D | last post by:
I'm having an issue with an ASP.NET page (VS.NET 2003, VB.NET, Oracle back end). The page uses the standard VS.NET grid to display the records from...
1
by: Big E | last post by:
I'm using ASP.Net and SQL Server I have 2 listboxes. I have a table with 50 states. In one listbox I want to show the states that are associated...
2
by: salad | last post by:
This is a tip on how to speed up listboxes DRAMATICALLY. Persons that would benefit are those that are constantly updating the rowsource of a...
1
by: Intrepid_Yellow | last post by:
Hi, I have the following code that runs my report generator. The user selects a table from a combo box, then whatever fields they want from a...
6
by: Dave | last post by:
On my form I have combo boxes. These combo boxes, after updating them, populate respective listboxes that are located below the combo boxes on the...
12
daniel aristidou
by: daniel aristidou | last post by:
hi i was wondering if it is possible to filter multiple numbers of tables at the same time. the filter would be automatically applied ie. as in a...
1
by: woodey2002 | last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just canít...
1
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.