Look up combos in Query Criteria?

I am trying to build a database  that narrows the record choices as different 
fields are selected.  I believe that this would be a query, but whenever I 
build a query and select a field choice, the record in the main table changes.
For example:
Car Dealer enters car ID, make, model, and color into main database.  I as a 
consumer, select make model and color, and the query lists only records that 
match.  I would prefer a lookup box in the query, as we don’t want to rely on 
stuff being named correctly, or maybe a burgundy from the list, would suffice 
for a red preference.  Keep in mind, changes made to the query should not 
effect the main table.
0
Utf
3/2/2008 4:56:00 PM
access 16762 articles. 3 followers. Follow

3 Replies
471 Views

Similar Articles

[PageSpeed] 49

You need to create a form with unbound combo boxes that contain the 
appropriate values.

Have your query refer to the combo boxes on the form as criteria:

Forms![NameOfForm]![NameOfControl]

Note that the query will only work properly when the form is already opened. 
Running the query will not cause the form to be opened.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Moose" <Moose@discussions.microsoft.com> wrote in message 
news:0F22E3E2-8DC8-47DB-8EC0-35A249A59E9C@microsoft.com...
>I am trying to build a database  that narrows the record choices as 
>different
> fields are selected.  I believe that this would be a query, but whenever I
> build a query and select a field choice, the record in the main table 
> changes.
> For example:
> Car Dealer enters car ID, make, model, and color into main database.  I as 
> a
> consumer, select make model and color, and the query lists only records 
> that
> match.  I would prefer a lookup box in the query, as we don't want to rely 
> on
> stuff being named correctly, or maybe a burgundy from the list, would 
> suffice
> for a red preference.  Keep in mind, changes made to the query should not
> effect the main table. 


0
Douglas
3/2/2008 5:09:10 PM
One way of doing this would be to have an unbound main form with a continuous 
form view subform bound to a query listing all available cars.  The main form 
would have unbound combo boxes cbMake, cboModel and cboColor say.  The 
subform's query would reference these controls as parameters like so:

SELECT *
FROM Cars
WHERE NOT Ordered
AND (Make = Forms!YourMainForm!cboMake
OR Forms!YourMainForm!cboMake IS NULL)
AND (Model = Forms!YourMainForm!cboModel
OR Forms!YourMainForm!cboModel IS NULL)
AND (Color = Forms!YourMainForm!cboColor
OR Forms!YourMainForm!cboColor IS NULL);

I've assumed for this example that the Cars table contains an Ordered column 
of Boolean (Yes/No) data type, so 'NOT Ordered' in the query restricts it to 
cars not yet ordered by a customer. By testing for NULL a selection from each 
combo box in effect becomes optional, so you can narrow down the search, 
starting with Make.

In the AfterUpdate event procedure of each of the three unbound combo boxes 
requery the subform control with:

Me.YourSubformControl.Requery

Note that YourSubformControl is the name of the control in the main form 
housing the subform, not the name of its underlying form object.

As a user selects a make the subform will update to show cars of just that 
make; when they select a model it will update to show just those of that make 
and model; and when they select a colour it will update to show only those of 
that make, model and colour.

To make this work properly you'll really need to correlate the combo boxes 
so that when a make is selected the second combo box shows only models of 
that make, and similarly only the colours available for the selected model.  
To do this make the RowSource of the cboModel combo box on a query which 
references the cboMake control, e.g.

SELECT DISTINCT Model
FROM Cars 
WHERE Make = Forms!YourMainForm!cboMake
ORDER BY Model;

and for the cboColors combo box's RowSource:

SELECT DISTINCT Color
FROM Cars 
WHERE Model = Forms!YourMainForm!cboModel
ORDER BY Color;

In the AfterUpdate event procedure of cboMake, as well as requerying the 
subform control, you'll need to set to Null and requery the cboModel  and 
cboColor controls:

Me.cboModel = Null
Me.cboColor = Null
Me.cboModel.Requery
Me.cboColor.Requery
Me.YourSubformControl.Requery

And similarly in cboModel's AfterUpdate event procedure

Me.cboColor = Null
Me.cboColor.Requery
Me.YourSubformControl.Requery

In the subform, to prevent any changes being made to the data set the Locked 
property of each bound control to True and its Enabled property to False.

Ken Sheridan
Stafford, England 

"Moose" wrote:

> I am trying to build a database  that narrows the record choices as different 
> fields are selected.  I believe that this would be a query, but whenever I 
> build a query and select a field choice, the record in the main table changes.
> For example:
> Car Dealer enters car ID, make, model, and color into main database.  I as a 
> consumer, select make model and color, and the query lists only records that 
> match.  I would prefer a lookup box in the query, as we don’t want to rely on 
> stuff being named correctly, or maybe a burgundy from the list, would suffice 
> for a red preference.  Keep in mind, changes made to the query should not 
> effect the main table.

0
Utf
3/2/2008 7:13:00 PM

"Moose" <Moose@discussions.microsoft.com> ha scritto nel messaggio 
news:0F22E3E2-8DC8-47DB-8EC0-35A249A59E9C@microsoft.com...
> I am trying to build a database  that narrows the record choices as 
> different
> fields are selected.  I believe that this would be a query, but whenever I
> build a query and select a field choice, the record in the main table 
> changes.
> For example:
> Car Dealer enters car ID, make, model, and color into main database.  I as 
> a
> consumer, select make model and color, and the query lists only records 
> that
> match.  I would prefer a lookup box in the query, as we don’t want to rely 
> on
> stuff being named correctly, or maybe a burgundy from the list, would 
> suffice
> for a red preference.  Keep in mind, changes made to the query should not
> effect the main table. 

0
padre
3/3/2008 1:59:55 PM
Reply:

Similar Artilces:

DLookup on a query??
Hello, Just wondering if it's possible to use the DLookup function on a query instead of a table? Alan Don't worry about it. I got it to work. Thanks anyway. Alan "Alan" wrote: > Hello, > > Just wondering if it's possible to use the DLookup function on a query > instead of a table? > > Alan ...

Key Combo Closes App
I am using a custom keypad which which generates standard keycodes and extended keycodes. I am capturing the key presses in PreTranslateMessage. How can I stop "Alt Shift F4" closing my application? Tim Try processing WM_CLOSE notification message. I think it's the one being sent by Alt+F4. "tim@DELETEMErobotcrazy.com" <tim@robotcrazy.com> wrote in message news:1174078109.910817.11510@d57g2000hsg.googlegroups.com... >I am using a custom keypad which which generates standard keycodes and > extended keycodes. I am capturing the key presses in > Pre...

HELP with queries!!!
Hi all, I am a new user to Access and need some help with setting up a query. I am building the framework to an inventory database. Currently I have one table that has the following information: serial # Status (checked in or checked out) equipment type (pc, network printer, standalone printer, scanner, and monitor) EE# (equipment number) model manufacturer work order# department (where the equipment is going) equipment location (the room number) relocation date comments I understand how to run the query, but I am trying to include drop down menu's in the query ins...

Import from web query problem
When importing from a web query the cell value <10E 6/L or <10^ 6/L does not import ie target cell is blank. Same happens whether the middle space is left in or out. Cells such as <100 import OK. Any suggestions as to what is happening here would be appreciated. Thanks. ...

Web Query Not Working
Up until yesterday, my web query in my Excel worksheet has been working perfectly. I have been using the saved query named "Microsoft Investor Stock Quotes.iqy". It looks like the server accessed by the URL has been down. When I ping the domain, I get the message that the ping timed out. The link used by the query is http://investor.msn.com/external/excel/quotes.asp?SYMBOL=AA,fdx... Is anyone else experiencing the same problem? Does anyone know how to contact MSN to jog them to get things up and running? Or, have they stopped supporting the query? Gary Mine went d...

Open A Sub-Form Using A Combo Box
I have a main form, frmEmployee, to record employee detail, one of which is a combo box, Position. I also have a sub-form for each position. Is there any way of changing the sub-form in the main form to match the position selected? Regards Nick To change the sub form try Me.[SubFormControlName].SourceObject = "SubFormName" Using the AfterUpdate event of the ComboBox -- Good Luck BS"D "Nick hfrupn" wrote: > I have a main form, frmEmployee, to record employee detail, one of which is a > combo box, Position. I also have a sub-form for each position. &g...

Counting with multiple criteria
Would be grateful if someone can help me with this A B C D 8-9AM 9-10AM 1 CA03 ON CA03 OF 2 CA03 OF CA03 OF 3 CA03 ON CA03 TR 4 CA03 TR CA03 TR 5 CA05 OF CA04 OF 6 CA05 TR CA05 ON 7 CA04 OF CA05 TR Note: Column A-B has the same header row, and so does C-D I want to count in the range (A1:D7) - How many CA03 and TR: [the result should be 3] - How many CA03 and ON and OF: [the result should be 5] Thanks in advance for your help. Best regards, Thuy Assuming your table is in A1:D8, data in rows 2 to 8 (in A2:D8) > ...

Convert datatype using query
I have create a query which combines two tables; ASSETS and UNITINFO. The UNITINFO table is linked from another database and the ASSETS table has a field that looks up a field called UNIT # from the UNITINFO table. Both fields are called UNIT # but the datatype on the UNITINFO table is set to Text and the UNIT # field on the ASSETS table is set to Number since it is a lookup field. I cannot change the datatype on the UNITINFO table to Number as the field contains both numbers and text. So when I ran my query that I created to pull data from both tables it gave me an error bec...

Query Based DL [WILDPACKET]
I want to configure a QBDL so it sends message to all the Active Accounts in our Domain, is this possible? I guess when a message is sent to QBDL it also sends messages to the Disabled Accounts too, right? I aonly want the QBDL to send messages to Enabled Users. Please advise. Thank you On Mon, 5 Dec 2005 08:44:04 -0800, "WILDPACKET" <WILDPACKET@discussions.microsoft.com> wrote: >I want to configure a QBDL so it sends message to all the Active Accounts in >our Domain, is this possible? > >I guess when a message is sent to QBDL it also sends messages to the ...

Queries
I have the following query (for example): countofstates color 3 red 8 blue 7 orange 2 purple 9 yellow I would like to display this table on a report, but when I do it repeats 6 times. How do I only get this information to appear once? Thanks in advance. Try a Totals query... Color States GroupBy Count -- hth Al Campagna . Candia Computer Consulting . Candia, NH USA Microsoft Access MVP http://home.comcast.ne...

Combo Box
Greetings, I had an issue of how to populate my combo box with unique values only, after some research I discovered collections and how they dont allow duplicate values and I could simply skip the error to achieve the results I wanted. However how can I modify the code to sort the values in the collection (Unique) in alphabetical order? I've seen some complex looking loops based on the old bubble sort routines that I wrote many many moons ago, but wondering what other peoples solutions would be? Any assistance would be appreciated. Cheers Rob 'Populate combo box with unique...

assigning a unique query for each textbox on a form
I've got a table (tblMain) with the following (relevant) fields: tblMain .FirstName .LastName .Seat .RefNum I've got a form that has a graphical display of the seats to which the people listed in tblMain are assigned. I'd like to put a text box on each seat corresponding to first and last name of the person assigned to that seat (stored in tblMain.Seat). The same database is used for various seatings so I'd like only those associated with the RefNum at hand to be called. I can write a simple enough query which I can call from VBA and which relies on a...

MFC based client server application query?
Hi experts I have developed an client server application using MFC sockets support. The server is a dialog based application & the client is an SDI application. I am having problem with sending data from client to server. After starting the server application, i start the client and try to connect & send data to server using the following code. void CMainFrame::OnConnect() { CSimpleSDIsockclientApp *pApp = (CSimpleSDIsockclientApp*)AfxGetApp(); int res = pApp->ClientSocket.Create(40000); if(res) { MessageBox("Client Socket at port no. 40000 Successf...

how do I enter a list in a list box or a combo box
hi i am trying to enter a list of names into either a list box or a combo box but i can not find out how to do it... can anybody help please... On 31/05/2010 11:42 AM, word challenged wrote: > hi i am trying to enter a list of names into either a list box or a combo box > but i can not find out how to do it... can anybody help please... See the following page of Greg Maxey's website: http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm If it's an ActiveX combobox that you haved inserted directly into the document, right click on it and select View Code and use c...

Where are the Forms and Queries in 2007
In Access 2003 if you wanted to open your existing Forms and Queries you clicked on the Forms button. In Access 2007 this option seems to be missing. My question is how do I access the Forms,Tables and queries. The only forms button I have found creates new forms, I want to access my existing forms. Many thanks for any advice. This is one of those times when a picture paints a thousand words, so here's a picture ... http://brenreyn.brinkster.net/navpane.jpg -- Brendan Reynolds "Ernmander" <Ernmander@discussions.microsoft.com> wrote in message news:4CDA479B-BFA6...

Using a cell to determine a value in a database query.
Hi folks, I've run into a problem recently - maybe there's no good answer, then again perhaps you can help me come to one. I have a spreadsheet on which There are three entry fields: start date end date store number I've managed to map start date to [startdate] in a query and end date to [enddate] - that seems to work fine. The problem I'm running into now has to do with the store number field. In the database, store number is a 4 character field, as of now the numbers of the store are like 01,02,03,04..HQ,RO. When I try to map store number to [storenum] in the query, I ...

Query Builder (SQL View)
I am trying to join 2 tables. I want to join if 'Field B' is in 'Field A'. ie. Field A = 'ABCDE' Field B= 'BC' ....the join would be successful cause 'BC' is in 'ABCDE'. Is this possible? I tried... SELECT FROM table1 INNER JOIN table2 ON table1.name like (% table2. partial_name %); Keep getting errors. Any help greatly appreciated! Warren Depending on the "flavor" of SQL you are using SELECT * FROM table1 INNER JOIN table2 ON table1.name like '%' & table2.partial_name & '%' Or SELECT * FROM table1 IN...

Combo Box Issues
I am relatively new to Access: I am trying to do two things: Use 'other' in a combo box list and have it open a text box where someone can enter free text and have the entry be stored under the same heading Have a combo box selection that will continue to open additional combo boxes (up to 4) until somebody selects the 'none' option, then it will move to the next data entry. Any and all help would be appreciated -- I know these are simple questions but I am trying to teach myself!!! "Clubber Land" <Clubber Land@discussions.microsoft.com> wrote in messa...

Querry Criteria
Hellooo Monsters! I have a report that is based on a querry. The querry has two fields, StartMeter and EndMeter. I have a form with two textfields, txtSartMeter and txtEndMeter where I enter a value for start meter and end meter and a button to open the report. I would like to set the criteria in the querry under StartMeter and EndMeter to reffer to these two text boxes respetively and filter the report for records starting from the StartMeter and ending with EndMeter. In other words records between txtStartMeter and EndMeter. I know how to do this under one field, but don't know how if...

Access Deleting a Query SQL
Hi all, I am working with a multiple parameter search query. The form I am using to conduct the search displays the results in a subform. I have gone through and really combed through the SQL to make sure there are no blatant spelling errors or incorrect references within the code. Anyways when I open the form that it is attached to, I keep getting the boxes described in this website http://allenbrowne.com/bug-13.html .. The form is open, and I even changed names of fields in the table in case SQL was choosing to be picky about what I named my fields. Is there anything else that would cau...

Combo boxes and Protection
Hi all! I have a workbook with a number of worksheets with combo boxes. I made sure to set the locked property to false for the combo boxes, but whenever I try to select an item from my combo box I get the following message: The cell or chart you are trying to change is protected or read-only. In the message I am informed to remove protection ... which of course is not what I want to do. Is there a solution to this problem. Thanks. ...

Data Validation With Multiple Criteria
I currently have a form that accepts sample measurements. Is there a way to place multiple validation rules into a text box so that there is either a measurement entered between 4" and 4.5" or the text box is left empty as the other choice. (This last option would be to allow a person who accidentally entered a measurement into the wrong text box to go back and delete it without breaking the validation rule ) I'd greatly appreciate any advice on the best way to do this, Thanks, Adam Private Sub YourTextBox_BeforeUpdate(Cancel As Integer) If Not IsNull(Me.Yo...

Look up cell range?
Is there a way to use a search field to find a range of cells? With the range named from column D. If that makes sense. You could apply a data filter to show all the cells in column D that match your criteria. Hard to say more than that, given your question's brevity.... HTH, Bernie MS Excel MVP "doss04" <doss04@discussions.microsoft.com> wrote in message news:39BC9DA1-67CE-448D-82CE-B3A696A8C9E8@microsoft.com... > Is there a way to use a search field to find a range of cells? With the range > named from column D. If that makes sense. > > I ha...

Help required in Update query
Hi, pls help me here table t1 has 3 fields, and data is as below name , grade , marks n1 a 10 n2 b 20 n3 c 30 n4 a 30 n5 c 10 n6 n7 40 now i want an update query where 2 names should be swapped. like n1 is updated as n2, and n2 updated as n1. i tried to put update query, but problem is if n1= n2 then we have 2 n2's in the table then again n2=n1 tried then we have 2 n1's , i want both names should be interchanged, any help STEP 1: BACKUP you...

If statement to export query if record count is not null
Greetings, thank you very much to all who are reading this. Basically I have an automated email that goes out every Monday. Very simply it uses sendobject to email a query. I would like to add some logic that basically looks at the record count and if it isn't null...sends teh email as is...if the record count is empty I would like to have it send an email that basically says "The record set this week is empty." Below is the module converted from the original macro. I was having trouble modifying the module so any help would be greatly appreciated. Thanks in ...