Making a drop-down query criteria 3

[ehsguy77]

Is it possible to make a drop-down of values available as query criteria? I don't want my users typing in info, because if they make a typo, they won't get any query results.

 

[Tranman]

cbpwc01,
"I keep getting errors" is a pretty open-ended statement...what kind of errors are you getting?

There is no "From" clause in your sql statement.

Is your form open when the query runs?

Let us know.

Tranman

 

[cbpwc01]

Tranman,

Sorry, I will specify. I keep getting "The expression you entered contains invalid syntax", but each time I change or alter the entry, I get the same response.

The form is not open when the query runs, do I need to add a command to open the form before the subquery runs?

Thanks!

 

[PHV]

If the Funds form isn't open how will the user choose the funds type ? ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cbpwc01]

Thanks to both of you for your help.

I'm creating the switchboard now and will set it up to open the form before you can run this query. I will keep you poseted on how this progresses.

 

[Tranman]

Here's a query that uses the value of a combo as criteria:

SELECT CUST.*
FROM CUST
WHERE CUST.L_NAME = [FORMS]![CUST]![TXTCOMBO4];

The form must be open when the query runs.

Tranman

 

[cbpwc01]

Thank you both! Its up and running--I had put in the wrong name as the combo box, which is what took so long.

 

[Tranman]

Congratulations.

Of course, neither I, nor PHV has ever made a mistake like that...

Right, PHV?

 

[XMalvolio]

Ok, for some reason I'm still in the dark after reading all of that!

I have a query that I want to ask the user for an entry from a combo box. I've created a form called IndCombo, and on it is a combo box, IndComboBox, with all the data linked to a table. I know the format for referencing this box is [forms]![IndCombo]![IndComboBox] , but I don't know where to put this or in what syntax.

I am a relatively new user, and this query already has a complicated "Is Null" entry, which makes the SQL view very confusing to look at. Can anyone explain how to make this happen within Access' design view? Or perhaps explain if I need to add it into the SQL view the syntax for adding to an existing Query?

 

[PHV]

What is already in the criteria cell of the relevant field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[XMalvolio]

There is nothing. The field is "Industry," and the only criteria is what they choose in the combo box that I want to appear.

 

[PHV]

Have you tried this in the criterial cell ?
=[Forms]![IndCombo]![IndComboBox]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[XMalvolio]

Yes, and it comes up with my first criteria question, then the combo box should appear in the second, but instead I get the normal Enter Parameter Value dialog, with "Forms!IndCombo!IndComboBox" in text and an emtpy white box beneath. No combo box appears.

 

[cbpwc01]

You can also try this:

Click the criteria cell under the field "industry". leaving the cursor in that cell, go to the top toolbar, click on query, and then click on SQL specific. Copy and paste the SQL codes from earlier in this post (adjusting names, etc), and hit enter. This is how I managed to get it entered for me.


It's probably the long way, but I get more success entering the SQL rather than using commands.

 

[XMalvolio]

I don't know enough about SQL and what the commands above mean to even edit them to my own use. I don't even know which kind of SQL-Based Query to choose on that menu.

 

[PHV]

The IndCombo form must be open when the query is launched ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[XMalvolio]

Ok so now with =[Forms]![IndCombo]![IndComboBox] in the criteria and with the form open when I run the query, it no longer gives me that second dialog box. However, it doesn't bring up a combo box, nor does it use the selection I've made in the open form. Should I have to choose the industry on the form first? Regardless, that didn't work. Any other ideas?

 

[PHV]

The Bound column of the combo must be the Industry field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[XMalvolio]

Ok, still nothing. I have a form with a combo box on it. The combo box correctly displays the contents of the Source: Industry table. I have that form, IndCombo, open. In my query I have a column with a field name "Prim Industry" from the table "Data - Job Data". In the Criteria field I have =[Forms]![IndCombo]![IndComboBox]. The settings on the combo box are as follows:

Control Source: blank
Input Mask: blank
Row Source Type: Table/Query
Row Source : SELECT [Source: Industry].Industry FROM [Source: Industry] ORDER BY [Industry];
Bound Column: 0 (I have also tried 1, but neither seem to make a difference. All records show every time. The table listing the Industries only has 1 column in it)
Limit To List: Yes
Auto Expand: Yes
Validation Rule/Text: Blank
Enabled: Yes
Locked: No

Does this shed any light on anything?

 

[PHV]

Has [Data - Job Data].[Prim Industry] values from [Source: Industry].Industry ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[XMalvolio]

Yes, [Source: Industry].Industry is the one column in that table, and [Data - Job Data].[Prim Industry] pulls its data from that column on input.