question on using query to bind data to form

[djc]

I need to use a query to bind data to a form. My particular uncertianty is
how to handle the fact that the query will require a parameter. I know I
could just open the record set based on an sql statement but I would like to
actually have the query created ahead of time to take advantage of the jet's
optimization if thats possible.

heres what I have to do:
1) a small form opens on the load event of the main apps form. The main apps
form loads unbound. The small form is a login form with just username and
password boxes. I want the user to login first and I need the username to be
part of the query that is then used to bind data to the main form. I want to
only show records for a particular user. The user info is stored in a user
table.

Can I somehow create the query ahead of time rather than open a recordset
based on an sql statement? or is opeing the recordset based on an sql
statement ok?

any help is appreciated.

 

[Sandra Daigle]

If you have the parameter available on another form, you can just hide the
other form (rather than closing it) then change your query to refer to the
other form. Hide the form by setting it's visible property to false.

If you are using A2000 or above, you could use VBA to open a recordset based
on a parameter query and then set the form's recordset to the recordset you
created in VBA.

Finally, you could just assign the RecordSource property using inline SQL.

Which is best? Unless the query is complex you probably won't see a
difference between any of the methods. If it is complex, I would say that
either the first or second query will improve your performance since they
will be able to use jet's optimization.

 

[djc]

Thanks!

If you have the parameter available on another form, you can just hide the
other form (rather than closing it) then change your query to refer to the
other form. Hide the form by setting it's visible property to false.

If you are using A2000 or above, you could use VBA to open a recordset based
on a parameter query and then set the form's recordset to the recordset you
created in VBA.

Finally, you could just assign the RecordSource property using inline SQL.

Which is best? Unless the query is complex you probably won't see a
difference between any of the methods. If it is complex, I would say that
either the first or second query will improve your performance since they
will be able to use jet's optimization.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I need to use a query to bind data to a form. My particular
uncertianty is how to handle the fact that the query will require a
parameter. I know I could just open the record set based on an sql
statement but I would like to actually have the query created ahead
of time to take advantage of the jet's optimization if thats possible.

heres what I have to do:
1) a small form opens on the load event of the main apps form. The
main apps form loads unbound. The small form is a login form with
just username and password boxes. I want the user to login first and
I need the username to be part of the query that is then used to bind
data to the main form. I want to only show records for a particular
user. The user info is stored in a user table.

Can I somehow create the query ahead of time rather than open a
recordset based on an sql statement? or is opeing the recordset based
on an sql statement ok?

any help is appreciated.