rubbernilly
Programmer
Hello all,
I have been working with a particular issue from several different POVs over the last several weeks, and am still having difficulty with it. I have searched this board and not found an adequate answer.
My basic question is: can I pass parameters to a query that I am opening as the sourceobject of a sub-form control?
Here is the scoop...
There are 3 ways that a user can open a query in this DB:
1) Directly from the DB window
2) As the source for a subform control
3) In an automated Monthly Run
For (1), I have parameters in the query, such as, "[TIS Upper Bound]". This way, the user gets prompted to enter the parameter. To make that work with (3), I keep a record of default values I want to pass the query when the monthly run occurs. I dimension a querydef object, and then pass the default values into the proper parameters.
Number (2) is where I am having trouble. The user can select the query to use as the source from a combobox on the main form. Because crosstab queries do not have a Filter property, I remask the query using DAO and a querydef's SQL property.
set qd = currentdb.QueryDefs("DynamicQuery")
qd.SQL = "SELECT * FROM " & Me.cboQuery
The problem comes in that when working with Filters on a dynamically changing query like this, I have to remove the filter and then reapply a bland one:
qd.Filter = qd.Fields(0) & " Like *"
And then set the sourceobject of the subform control to "", and then to the DynamicQuery again (to apply the proper filter).
The end result is that the user would get prompted 4 or 5 times for each parameter.
What I want to do is have a listbox control somewhere on the main form that loads up with the default monthly run values (that way, all of the parameters will be there). I can get the values there, and I can give the users the ability/interface to change them. So, again, my question is, how can I pass them to the query and then use the query as the source of the sourceobject in the subform control?
I have been working with a particular issue from several different POVs over the last several weeks, and am still having difficulty with it. I have searched this board and not found an adequate answer.
My basic question is: can I pass parameters to a query that I am opening as the sourceobject of a sub-form control?
Here is the scoop...
There are 3 ways that a user can open a query in this DB:
1) Directly from the DB window
2) As the source for a subform control
3) In an automated Monthly Run
For (1), I have parameters in the query, such as, "[TIS Upper Bound]". This way, the user gets prompted to enter the parameter. To make that work with (3), I keep a record of default values I want to pass the query when the monthly run occurs. I dimension a querydef object, and then pass the default values into the proper parameters.
Number (2) is where I am having trouble. The user can select the query to use as the source from a combobox on the main form. Because crosstab queries do not have a Filter property, I remask the query using DAO and a querydef's SQL property.
set qd = currentdb.QueryDefs("DynamicQuery")
qd.SQL = "SELECT * FROM " & Me.cboQuery
The problem comes in that when working with Filters on a dynamically changing query like this, I have to remove the filter and then reapply a bland one:
qd.Filter = qd.Fields(0) & " Like *"
And then set the sourceobject of the subform control to "", and then to the DynamicQuery again (to apply the proper filter).
The end result is that the user would get prompted 4 or 5 times for each parameter.
What I want to do is have a listbox control somewhere on the main form that loads up with the default monthly run values (that way, all of the parameters will be there). I can get the values there, and I can give the users the ability/interface to change them. So, again, my question is, how can I pass them to the query and then use the query as the source of the sourceobject in the subform control?