Hi - I have a query that prompts the user for a parameter. For example:
SELECT *
FROM People
WHERE People.Name = [Enter the person's name]
I also have a form with a button that runs this query, and a combo box that
looks up Name from the People table. I would like to pass the Name selected
in the combo box into the query. Any suggestions on how to program this?
Thank you,
1) A direct answer to your question:
WHERE People.Name = forms!FormName!ComboName
The form MUST remain open when the query is run.
2) However, it's not unlikely that you may have more than one person
with the same name, i.e. Joe Smith. How will you determine which Joe
Smith is the correct one?
Instead of searching the name field, use the person's ID to return the
correct record.
In this case, show the NameID, Name, and some other identifying field
(SSN?, Phone, etc.) in the combo box. Hide the NameID field. Set the
Combo Box bound Column to 1.
This way, when you select the correct "Name" you will be assured of
getting the correct record returned. The Query criteria is exactly the
same, except place it in the NameID row.
WHERE People.NameID = forms!FormName!ComboName
3) Do you really have a field named "Name" in your table?
Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
See the Microsoft KnowledgeBase article for your version of Access:
109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
