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 6 4618
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
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 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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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.
|
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
|
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...
|
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.
|
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:
| |
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...
|
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:...
|
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...
|
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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,...
| |
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...
|
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...
|
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();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |