Run a parameter query from an Access form (VBA Access2003/WindowsXP)

Topic

New Reply

How do I pass a parameter from a form that requests needed data (like a name) to a query that needs that data? Writing a parameter query is straight forward, but its method of requesting its parameter is by the pre-defined use of its little parameter request window. Running a query from a form is also straight forward, but I can see no way, after the form has requested and received the parameter data, of passing it to the query. (The DoCmd OpenQuery Method, as an example, has no capability to pass along the parameter information.)

Thank you for your time and help,

John Littell

Reply | Quote

Replies

To have a query refer to the value referred to on the form, in the criteria use a statement like this:

IIf(IsNull([forms]![YOURFORM]![INPUT SOURCE CONTROL HERE]),[FILTERED FIELD NAME HERE],[forms]![YOURFORM]![INPUT SOURCE CONTROL HERE])

Reply | Quote

Jeremy’s reply lets the query return all records if the parameter is left blank. If you simply want to replace the parameter prompt with a reference to the form, change the criteria from something like

[Enter name]

to

[Forms]![frmMyForm]![txtName]

where frmMYForm is the name of the form, and txtName is the name of the text box on the form in which the user enters the name information.

Reply | Quote

Jeremy and Hans,
Thank you for your replys. Once I realized that the form would still be open and accessable, it all fell into place. Works fine.

John

Reply | Quote

John,
If you run into the case where you must (or want to) close the form, you could do one of two things…create a hidden form that will store the value of the box, and refer to that, or store the value as a global variable.

Reply | Quote