How to prompt for criteria

[kavarin]

I have read through most of the posts, and I have not seen a clear answer on this one:

I want to design a query (a select query I beleive) that will group records that contain a particular field. This field is a linked field to another table that provides about 15 diffeent possible entries. Can my query prompt the user to select the approriate choice and then pull all records that contain THAT data? I apologize if that is confusing. I will try to explain further if needed

 

[David R]

It sounds like you're asking if you can use a value from a Lookup table to lookup records in a main table, when all that's stored in the main table is the lookup number, not the value (i.e. 3 for "Apples"). You want the user to be able to type "Apples", not 3.

Include both tables in your query. Put the [Enter value here:] in the criteria for the Lookup table's Value field (the text one). You don't need to include the lookup number field in the query, just have both tables present and the relationship properly joined.

Post back if I missed the idea,
David R


[This message has been edited by David R (edited 04-16-2002).]

 

[kavarin]

Yes and No- (sorry)

My lookup table is simply a single field containing a name of a community. I have fiddled with the query and figured out how to have it prompt for an entry, but the users must KNOW what the value is.
Is it possible to have the prompt provide the same drop-down list that is available via the form? That way there is no error in search criteria and the users are all smiles.

 

[kavarin]

By the way...thanks for your reply

 

[David R]

Ahhh, gotcha. What you want to do then is have an unbound form with a combo box on it and a 'search' button. The combo box should have a row source equal to your listing (basically it will look like the combo from your other form, but not bind the value anywhere).

Then with your search button start the query. In the criteria for your query, instead of a parameter prompt, refer to this form:
CommunityField Criteria: Forms!UnboundFormName.thisComboBox

As long as this form is open at the time you run the query, all will be smiles. You can put a line in the button after opening the query:
DoCmd.Close acForm, "UnboundFormName"

HTH,
David R

 

[kavarin]

Thank you David,

I will "attempt" to follow your instructions and get this thing to work. I will post again if I have problems.

 

[kavarin]

I can't thank you enough on this one. After working out my typo's...it worked perfectly a you suggested.
I shall sacrafice an old FoxPro database in your honor. HAIL TO THE ACCESS GOD!!

 

[David R]

Actually, I think Pat is a goddess last I checked

Glad you got it working,
David R

 

[kavarin]

Ahhaaa. I will keep that in mind my friend. Thanks again.

P.S. I posted another mind boggler for ya (if your up to it)