Queries using parameter queries, assigning values to for params in called query, is it possible?

[Guest]

In a monthly update of a database with new data, I have quite a number of operations to be done, amongst these there are a lot of queries, and many of them are the same query to be run several times, but with different parameters

I have made a checklist in a table to be used by the one updating the database, which may be a user not that familiar with Access. Each step is done and ticked off. There is a minimum of dataentry needed

In order to make maintenance and the use of the checklist easier, I want to retain just one query with one or more parameters. But to minimize the risk of giving wrong values for the parameters when the query is run, I want to make a number of queries that use this one query but run it with different values for the parameters. Can that be done, or can I not avoid the runtime prompt for the values for the parameters

If it is not possible, can I make a form using the checklist table and through VBA call the query with values for the parameters. Again, so that the user is not prompted for anything, eliminating the risk of bad dataentry

Regards

Frank M.

 

[Guest]

I have been looking a bit more into queries and the object model of Access. It seems that my best option would be to make a VBA Sub for the whole monthly procedure, using the DoCmd, OpenQuery and RunSQL methods to get my queries run automatically.
I already have a table with the code for my queries in the database, and as I mentioned a table with the actions of the monthly procedure (including the names queries to be run on a step if applicable).

It seems that using the RunSQL, having a code base for the query that is updated by replacing text in it, is the way to handle executing the same query with different parameters, if I don't want the user to be asked about parameter values. If I just use OpenQuery method on a ready query with parameters, there is no way to pass parameters to that query, and the user will be asked. If anyone knows of a way, please let me know.


Frank M.

----- Frank M. wrote: -----

In a monthly update of a database with new data, I have quite a number of operations to be done, amongst these there are a lot of queries, and many of them are the same query to be run several times, but with different parameters.

I have made a checklist in a table to be used by the one updating the database, which may be a user not that familiar with Access. Each step is done and ticked off. There is a minimum of dataentry needed.

In order to make maintenance and the use of the checklist easier, I want to retain just one query with one or more parameters. But to minimize the risk of giving wrong values for the parameters when the query is run, I want to make a number of queries that use this one query but run it with different values for the parameters. Can that be done, or can I not avoid the runtime prompt for the values for the parameters?

If it is not possible, can I make a form using the checklist table and through VBA call the query with values for the parameters. Again, so that the user is not prompted for anything, eliminating the risk of bad dataentry?


Regards,

Frank M.