473,716 Members | 2,558 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 4626
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*******@hotm ail.com> wrote in message
news:1v******** *************** *********@4ax.c om...
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********@hotm ail.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_GotFocu s()

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 acSysCmdSetStat us, "Wait, retreiving building list"

strCurRowSource = Me.cboBldg.RowS ource '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 (strcurrowsourc e = "") create new SQL

booC = False

If strCurRowSource = "" Then

booC = True

Elseif strCurRowSource <> strSql Then

booC = True

End If

If booC = True Then Me.cboBldg.RowS ource = strCurRowSource

Exit_Proc:

SysCmd acSysCmdClearSt atus

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_GotFocu s()

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 acSysCmdSetStat us, "Wait, retreiving building list"

strCurRowSource = Me.cboBldg.RowS ource '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 (strcurrowsourc e = "") create new SQL

booC = False

If strCurRowSource = "" Then

booC = True

Elseif strCurRowSource <> strSql Then

booC = True

End If

If booC = True Then
Me.cboBldg.RowS ource = strCurRowSource

me.cboBldg.Requ ery

End if
Exit_Proc:

SysCmd acSysCmdClearSt atus

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*******@hotm ail.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
459
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 I need is in another data table. Can and how to you reference the value of a column in a data table/view from the row filter string of a different data view. I'm using a strongly typed dataset. Below is more info.
3
18228
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 select all the records. ALSO to use two fields (or more) fields to filter a query based on selections in two or more fields: All the records with NAME = all the names selected in a list box BETWEEN StartDate and EndDate
0
1099
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 a particular table. The user can edit certain fields in the grid. There are 10 columns in the grid, 5 static fields (labels), 4 listboxes, and 1 edit box. 2 of the listboxes hold about 12 items each and the other 2 listboxes only contain 2 items. The problem is that on some lower end...
1
1066
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 with a record. Let's say 4 states. I can do this. AL AK AR CA In the other listbox I want to show whatever states are not in the first listbox.
2
4349
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 listbox/combobox in order to filter and sort the data and the refreshes are slow. (OT. I've often wondered why there is no .Sort or .Filter property for Combos and Listboxes.) My listboxes , and their rowsources, on my form were constantly being refreshed to filter and sort data. Ex:
1
2499
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 list box. (This part all works and the report runs fine). There is then a combo box they can select a field from (eg CompanyID etc) and then the list box below that contains the values (eg Microsoft, Novell etc). These are all multi-select list boxes. Now I can get the code to work if the user...
6
9423
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 same form. I am trying to use a "generate report" button located on my form to print all of the list box values (that have been updated via selection from combo boxes) from the form to the report. I've tried using a macro with the code: Macro Name: cmdGenerateReport : On Click Action:...
12
4134
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 query. However i want only one list to appear when it is run.therefore not a exactly a multiple filter but a filter on one table where all the data is temporarily put when the filter is run. this filter would auto matically run whenever the databased is open presumably by using and autoexecute...
1
6797
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 get any where on. My databse mostly includes bits of code for different examples. I have one last thing to finish. I am trying to create a search form that will allow users to select criteria from multiple sources eg ,multi select list boxes , combo boxes. I have a subform showing all the...
0
8718
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9344
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9200
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9105
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9047
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5969
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3177
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2543
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2119
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.