Can I use multiple querys with one Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form built that I would like to feed multiple queries, depending on which "button" the user presses
Can I do this, or does each query need its own form

If I can, how do I do it? I am new to Acces

Thank
Mike
 
You can change the recordsource query of a form programatically.

me.recordsource = "Queryname" (perhaps this is a drop down box)
me.requery



Mike Pallos said:
I have a form built that I would like to feed multiple queries, depending
Click to expand...
on which "button" the user presses.
 
Hi Mike,

It's hard to really give detailed advice since the post
was pretty general (no details on the queries, whether
they have the same field names, same underlying tables,
etc) but I will offer the following:

A form has a record source, which can be a table or
query. If it is a query, it can be based on one or more
tables, and, it can be specified as the name of a saved
query or as a SQL statement.

Generally, when designing a form, I will use a query to
combine all of the data with a 1:1 relationship that I
want the user to be able to edit and I set that query as
the form's record source.

Then, for any information that is 1:many (such as a list
of contacts for a project, list of part #'s on an order,
etc), I will design a subform and place it on the main
form and set the Master/Child fields.

Can you change the form's record source at run time, yes,
but the new record source would have to contain the same
fields as those on your form (unless you want to write
code to also modify the form objects) or you would get an
error. Also, the query would have to be updateable if
you want the user to be able to make changes.

But, if you want to do it, the syntax is the following:

Me.Recordsource = "TableOrQueryName"

Or

Me.Recordsource = "SQL Statement"

You could put this code behind buttons, or a combo or
list box, depending on your needs.

-Ted Allen
-----Original Message-----
I have a form built that I would like to feed multiple
Click to expand...
queries, depending on which "button" the user presses.
 
Thx Ted

The form is based on a single table, or a tmp table with the same fields. I'd like to use multiple querys, or recordsets build from my code, then set the recordset = in some logic. All recordsets and tables have the same field names. I was just unclear of how to select the data differently, then present it using one form and not have to use a different form for each query or recordset in a program. I hope that makes more sence. - Mike
 
Yeah, in that case you should be able to use
Me.Recordsource to change the source for the form.

You could build saved queries for each of your
situations, or you could build the sql statement in code
to generate the recordset that you want.

I have read that using saved queries for forms can be
faster because Access optimizes the saved queries. I
don't know whether it would make a measurable difference
in your case.

If you decide to generate the sql string for the
recordset at run time, it is usually easier to follow to
assign it to a string variable and build it using
whatever necessary logic. Then just set the recordsource
equal to the variable containing the sql string.

A snippet of some code building a very simple sql string
follows (watch wrapping)

strSQL = "SELECT tFADetail.* FROM tFADetail "
strSQL = strSQL & "WHERE tFADetail.WO_No = '" & strWO
& "'"

You can go to sql view of your existing queries to look
at their sql statements. The idea would be to either
build them in code based on some input, or just select
one based on which button was pressed, or something
similar.

So, you can use If statements and other conditional
evaluation tools to generate the sql that you want, then
set that as your form's record source.

To answer your other earlier question, you can set
anything valid as your form's record source in the design
view, that will just define the form's initial view and
your code will directly change it at run time per your
established event triggers.

HTH

-Ted Allen
-----Original Message-----
Thx Ted.

The form is based on a single table, or a tmp table with
Click to expand...
the same fields. I'd like to use multiple querys, or
recordsets build from my code, then set the recordset =
in some logic. All recordsets and tables have the same
field names. I was just unclear of how to select the
data differently, then present it using one form and not
have to use a different form for each query or recordset
in a program. I hope that makes more sence. - Mike
 
Back
Top