Clever Geek Handbook

Can I set the parameters of the linked query in vba before loading the form?

I have a linked form. The data source for the form is a request with parameters. Is it possible to set parameters in vba before loading the form? The popup for parameters appears even before the form_open event: /

I know of alternatives .. building my own query strings with parameters or manipulating sql queries. but I don't like them.

I am currently using unbound forms at design time and bind them to parameters at runtime. but having unbound forms at design time is not very convenient.

Any idea?

Hello

Aegi

+3


source to share


2 answers


You said:

I am currently using unbound forms at design time and bind them to parameters at runtime. but having unbound forms at design time is not very convenient.

One alternative is to use the "dummy" RecordSource so that you have an associated form at design time, but still update the RecordSource using a parameter query at runtime.

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



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

It filled the "Field List" fields EmployeeID

, FirstName

, LastName

, DoB

. Obviously, you would like your dummy query field names to reflect what the field names will be at runtime.

This will also avoid pop-ups of options at boot time.

+4


source


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

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



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

And with parameters removed from the query, you can use the query in forms, reports, export procedures, and all kinds of uses.

0


source






More articles:


All Articles