action Query

[David]

I have code that exports records to excel and then
updates the record as "exported".
The problem is the Query has criteria that the user
inputs. User enters project number. Unfortunately, the
user has to enter the project number in twice, once for
the export and once for the update query.
What is the best approach to get the value entered by the
user to carry over to my update query?
Can I create a hidden form and use input box to throw the
value on it and then call that field in the export and
update query?
Can a query pull it's criteria from a field in a form?
I am SQL challenged, so hopefully we can do this in a
query's design mode.

Thank you for helping.

David

 

[Nikos Yannacopoulos]

David,

Yes, a query can pull criteria from a control on a form, and this is not
only very convenient for running subsequent queries with the same criteria,
it is also the most user-friendly way to collect user input; what's more,
you can add a command button on the same form to run the query(ies) or
reports or whatever, and end up with a highly intuitive and
professional-looking user interface.
Assuming the form is named frmUserInput, and control on it txtData1, this
would be "read" in your query by the expression:
Forms![frmUserInput]![txtData1]

so you could have criteria like
Forms![frmUserInput]![txtData1]

= Forms![frmUserInput]![txtData1] and <= Forms![frmUserInput]![txtData2]

Like Forms![frmUserInput]![txtData1]
etc.

Note: in the case of Crosstab queries, in particular, you would need to
explicitly declare each such reference to a form control as a parameter
(Query > Parameters) of the right type, or Access will fail to read it.

HTH,
Nikos