Possible to set the parameters of a bound query in vba before form load?

3
votes

I've a bound form. The datasource for the form is a query with parameters. Is it possible to set the parameters in vba before the form loads? The popup for the parameters pops up even before the form_open event :/

I know of the alternatives .. build my own query strings with parameters, or manipulate the sql of the querydefs. but i dont like them.

at the moment I use unbound forms during designtime and bind them to parameterqueries during runtime. but having unbound forms during designtime is not so handy.

Anyone an idea?

Greetings

Egi

2
ms-access
Can you set the parameters to the values of controls on a hidden form? - Fionnuala
i dont like hidden forms neither ;D.. i like to work with code. - Egi
So .. you do not want to build an sql string, you do not want to change the query def, and you do not want a hidden form. It does not leave a lot of room, does it? I guess you could use ADO, but that will complicate your life even more. - Fionnuala
yeah it doesn't ... as I told you, I m not looking for alternatives but for a way to fill the parameters of a query in vba that is bound to a form during form_open or something. i know that maybe that is not possible, but "myQuery.Parameters("ID").Value = 3" is just so much better then anything else in access, I am really pissed that I can't do it in form_open. - Egi
You will have to look at an alternative - there are many. For example, you could leave the parameters out altogether and apply a filter, or add a Where statement with the parameters to the OpenForm action of DoCmd (4th argument, I think) - Fionnuala

2 Answers

4
votes

You said:

at the moment I use unbound forms during designtime and bind them to parameterqueries during runtime. but having unbound forms during designtime is not so handy.

One alternative would be to use a "dummy" RecordSource so that you have a bound form at design time, but still update the RecordSource using your parameter query at runtime.

An example of a dummy query would be something like this:

SELECT 1 AS EmployeeID, 'John' AS FirstName, 'Doe' AS LastName, #1/1/1980# AS DoB

That would populate the Field List box with the fields EmployeeID, FirstName, LastName, DoB. Obviously you would want your dummy query field names to reflect what the field names will be at runtime.

This will also avoid the issue of the parameter pop-ups showing at load time.

0
votes

Actually, the basic solution here is to simply remove the parameters and NOT change the SQL used. Access forms have a where clause. They are designed to solve this very problem. And this means you don't need a query with parameters and you don't have to modify the form's or report sql.

You simply pass the where clause when you open the form.

So the alternatives of building a query string are NOT required, and the alternative of having to manipulate the sql of a querydef is also not required. And the alternative of having to modify the forms data source is also not required.

And with parameters removed from the query, then you are free to use the query in forms, reports, export routines and all kinds of uses.