Common parameters for multiple queries

[zhbuyi]

I have several queries which use the same parameter. The parameter is updated once in a while. To avoid having to change the parameter for all the queries, I have thought of two solutions, but have not executed.

1. Define a "global constant" to store the value, and every time I want to change the parameter, I just change the global constant. But I don't know whether you can do that in Access or if you can, how to do it.

2. Store the value of constant in a table, and pull the parameter from the table to the queries. I really don't like it because it is so heavy-headed. I'd very much prefer the first solution.

Please let me know if there are better solutions.

Thanks

 

[vbaInet]

This is how you define constants.
Below "Option Compare Database"

Option Explicit
Const nameOfConstant = "yourParamter"

You could consider creating a table with the list of all possible parameters, set a combo box's recordsource to that table and use your combo box as the paramter selector.

 

[boblarson]

Just use a form for the parameter.

 

[zhbuyi]

This is how you define constants.
Below "Option Compare Database"

Option Explicit
Const nameOfConstant = "yourParamter"

You could consider creating a table with the list of all possible parameters, set a combo box's recordsource to that table and use your combo box as the paramter selector.

I am assuming using VBA to store the constant? But how do I do that without VBA so that the queries will recognize the constant?

What I meant was that is there a "Global Constant" for Access by itself

Also, if I write that into VBA, at what point should I invoke the program?

 

[boblarson]

Why go through the hassle? Just use a form and then you can refer to that form (as long as it is open, and even if it is hidden, at any time) in the query criteria like

[Forms]![YourFormNameHere]![YourControlHere]

 

[zhbuyi]

Why go through the hassle? Just use a form and then you can refer to that form (as long as it is open, and even if it is hidden, at any time) in the query criteria like

[Forms]![YourFormNameHere]![YourControlHere]

Thanks, I will try.

Just for discussion purpose, I don't like this either for the same reason I don't like the table solution, because forms and tables are not intended to store such global values. It feels like getting a cell phone just for the calendar in it.

 

[boblarson]

Thanks, I will try.

Just for discussion purpose, I don't like this either for the same reason I don't like the table solution, because forms and tables are not intended to store such global values. It feels like getting a cell phone just for the calendar in it.

Actually, global variables are somewhat volatile and if you have certain errors that happen, you can totally lose the value. So, Pat Hartman (the former MVP and the current top poster on the site) had suggested using forms for storing information instead of using global variables. That way, if you have anything happen where it would normally blow your global variable away then the form is still untouched.

As for tables, I use those all of the time for storing preferences and certain information which I need to retrieve. I use ONE table called tblPreferences and I can store a name and a value and retreive it at any time. So, why would it seem that using a table for a system would be wrong? In fact, Access uses system tables in all of the databases. So, why can't we?

 

[datAdrenaline]

To make a constant visible to a query ...

Option Explicit
Const MyTextConst = "SomeTextValue"
 
Public Function fMyTextConst() As String
    fMyTextConst = MyTextConst
End Function

Then, in your query ...

SELECT *
FROM SomeTable
WHERE SomeTextField = fMyTextConst()

....

But ... calling a VBA function vs. a Table is not all that much different, it all depends on the scope of the solution you are wanting to provide.

When I have a need like you describe, which is virtually in every app I develop! I will often mix the technique of a table to hold parameters along with custom database properties. I typically use the table technique if the parameters are user specific and I use the database properties technique if the parameter is applicaiton specific.

I think you would be better off either useing a table to hold the parameter (or as many as you like for that matter), or the db property technique as I think it is a poor practice to have the need to modify your code on a regular basis, or worse yet, have your users modify your code.

To find out more about using the database properties method, check out the post found by clicking here:
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=84&Number=1513840&fpart=all#Post1513840

Also, I have attaced the code from that post in the event you can not down load it from the link.

Hope that helps!