Reusing a Form

[glenn354]

I have a form I would like to use in front of several queries. For
example, I want to use frmA but only looking at the records retrieved
by qryX. Then I want to use frmA again, but only looking at the records
retrieved by qryY. Then be able to reuse frmA to view records from
qryZ. My reasoning is that if the form changes, I don't have to
manipulate multiple forms. There will be approximately 20 such queries.
Has anyone done something similar? If so, how?

Don Glenn

[Larry Linson]

<gl******@cox.net> wrote

I have a form I would like to use
in front of several queries. For
example, I want to use frmA but
only looking at the records retrieved
by qryX. Then I want to use frmA
again, but only looking at the records
retrieved by qryY. Then be able to
reuse frmA to view records from
qryZ. My reasoning is that if the form
changes, I don't have to manipulate
multiple forms. There will be approxi-
mately 20 such queries.

Three ways come immediately to mind... on the guess that the Form you
mention is bound to the Query, and that you are opening it from VBA with a
DoCmd.OpenForm statement.

It seems that the only difference between the Queries is which Records are
returned, not a different set of Fields (which could, perhaps, be handled
with a single Form, but wouldn't be as easy).

The DoCmd.OpenForm has two arguments in which you can supply criteria to
limit the Records from the Form's RecordSource: FilterName and
WhereCondition. In at least some versions of Access, you can pass the name
of a string containing a complete SQL statement in the Filter or Where
Condition arguments, if you'd rather. You could use one of those, or,
perhaps more efficiently, use the OpenArgs argument to pass the exact SQL
which you use to replace the Form's RecordSource in the Form's Open event.

Larry Linson
Microsoft Access MVP

[Bas Cost Budde]

By using the Recordsource property of the form. If you set that to
another recordset, voila.

Data will show only if the fields have the same names.

gl******@cox.net wrote:

I have a form I would like to use in front of several queries. For
example, I want to use frmA but only looking at the records retrieved
by qryX. Then I want to use frmA again, but only looking at the records
retrieved by qryY. Then be able to reuse frmA to view records from
qryZ. My reasoning is that if the form changes, I don't have to
manipulate multiple forms. There will be approximately 20 such queries.
Has anyone done something similar? If so, how?

Don Glenn

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[David W. Fenton]

"Larry Linson" <bo*****@localhost.not> wrote in
news:nUFue.2579$HU.1674@trnddc03:

<gl******@cox.net> wrote

I have a form I would like to use
in front of several queries. For
example, I want to use frmA but
only looking at the records retrieved
by qryX. Then I want to use frmA
again, but only looking at the records
retrieved by qryY. Then be able to
reuse frmA to view records from
qryZ. My reasoning is that if the form
changes, I don't have to manipulate
multiple forms. There will be approxi-
mately 20 such queries.

Three ways come immediately to mind... on the guess that the Form
you mention is bound to the Query, and that you are opening it
from VBA with a DoCmd.OpenForm statement.

It seems that the only difference between the Queries is which
Records are returned, not a different set of Fields (which could,
perhaps, be handled with a single Form, but wouldn't be as easy).

The DoCmd.OpenForm has two arguments in which you can supply
criteria to limit the Records from the Form's RecordSource:
FilterName and WhereCondition. In at least some versions of
Access, you can pass the name of a string containing a complete
SQL statement in the Filter or Where Condition arguments, if you'd
rather. You could use one of those, or, perhaps more efficiently,
use the OpenArgs argument to pass the exact SQL which you use to
replace the Form's RecordSource in the Form's Open event.

Or, have the form by default open with an empty recordsource, then
choose which data set you want to load from, say, a dropdown list,
the AfterUpdate event of which would set the form's recordsource.

The way I create empty recordsets is to pick the smallest table in
the application, then do a TOP 1 query on it. I then return no
fields from that table, but instead:

SELECT Null as YourField1, Null As YourField2

and so forth.

The result is a non-editable single-record record source that loads
very fast, but has all the fields the controls on the form are bound
to (as opposed to having a blank recordsource, which will cause the
#NAME error to show in all the controls).

I don't see any merit to use OpenArgs for passing SQL or filters
when you already have a mechanism for passing a WHERE clause as a
dedicated argument to the OpenForm method.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[clarale]

Or you can create several queues list in the form, when someone opens
the form, they choose their own queue to output different info for each
different queue. this will pull from a queue table having an SQL .

[glenn354]

Larry: Thanks for the quick reply. You are correct, I want to use the
same exact fields, but apply 20 different filters on the data based on
what the person wants to work on. I just don't want to maintain 20
queries and 20 forms.

I will try your answer as I think it is the easiest and therefore the
most elegant.

Don Glenn

[Steve Jorgensen]

On 24 Jun 2005 07:48:46 -0700, "gl******@cox.net" <gl******@cox.net> wrote:

Larry: Thanks for the quick reply. You are correct, I want to use the
same exact fields, but apply 20 different filters on the data based on
what the person wants to work on. I just don't want to maintain 20
queries and 20 forms.

I will try your answer as I think it is the easiest and therefore the
most elegant.

Don Glenn

Just to add my 2 cents...

I've found that it's not good for the code that launches the form to be too
tightly coupled to -how- the form displays its records. Thus, rather than
pass the criteria using the FilterName or WhereCondition clauses, I prefer to
pass the criteria using OpenArgs, and let the form build the SQL or apply a
filter to itself during its Open event handler.

One example of where this technique pays off...

You open the same form from 10 different calling procedures, and now you find
you need to present the form data in a subform of the main form. You can't
simply apply the criteria to the main form because it's not the one bound to
the data, and there are now 10 places the code would have to be changed to
accomodate the new master/sub form design. Careful - don't miss one.

[David W. Fenton]

cl*****@attglobal.net wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

Or you can create several queues list in the form, when someone
opens the form, they choose their own queue to output different
info for each different queue. this will pull from a queue table
having an SQL .

"Queue?"

I have no idea what you're talking about with that term.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"gl******@cox.net" <gl******@cox.net> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

Larry: Thanks for the quick reply. You are correct, I want to
use the same exact fields, but apply 20 different filters on the
data based on what the person wants to work on. I just don't want
to maintain 20 queries and 20 forms.

I will try your answer as I think it is the easiest and therefore
the most elegant.

I wouldn't maintain any queries at all -- just filter the basic
form.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:43********************************@4ax.com:

On 24 Jun 2005 07:48:46 -0700, "gl******@cox.net"
<gl******@cox.net> wrote:

Larry: Thanks for the quick reply. You are correct, I want to
use the same exact fields, but apply 20 different filters on the
data based on what the person wants to work on. I just don't want
to maintain 20 queries and 20 forms.

I will try your answer as I think it is the easiest and therefore
the most elegant.

Just to add my 2 cents...

I've found that it's not good for the code that launches the form
to be too tightly coupled to -how- the form displays its records.
Thus, rather than pass the criteria using the FilterName or
WhereCondition clauses, I prefer to pass the criteria using
OpenArgs, and let the form build the SQL or apply a filter to
itself during its Open event handler.

One example of where this technique pays off...

You open the same form from 10 different calling procedures, and
now you find you need to present the form data in a subform of the
main form. You can't simply apply the criteria to the main form
because it's not the one bound to the data, and there are now 10
places the code would have to be changed to accomodate the new
master/sub form design. Careful - don't miss one.

Well, that sounds like an ideal situation to have the form's default
recordsource be an empty recordset (or the uneditable single record
I described in another post), and then use public properties of the
form to set the recordsource.

DoCmd.OpenForm "MyForm", , , , , acHidden
Forms!MyForm.WhereClause = "[your criteria here]"

The .WhereClause property of the form would look something like
this:

Property Let WhereClause(strSQL As String)
Dim strRecordsource As String

' assumes module-level constant with the base SELECT/FROM
strRecordsource = c_strRecordsource
strRecordsource = strRecordsource & " WHERE " & strSQL
Me.Recordsource = strRecordsource
Me.Visible = True
End Property

That way you're treating the form as an object, and everything about
the form that needs to know what to do with itself is encapsulated
in the form itself. This could include issues like even passing the
form an argument to display a child record, then having that
property look up the parent record, navigate to it in the parent
form, then display the appropriate child record.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Fri, 24 Jun 2005 21:49:34 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:43********************************@4ax.com :

On 24 Jun 2005 07:48:46 -0700, "gl******@cox.net"
<gl******@cox.net> wrote:

Larry: Thanks for the quick reply. You are correct, I want to
use the same exact fields, but apply 20 different filters on the
data based on what the person wants to work on. I just don't want
to maintain 20 queries and 20 forms.

I will try your answer as I think it is the easiest and therefore
the most elegant.

Just to add my 2 cents...

I've found that it's not good for the code that launches the form
to be too tightly coupled to -how- the form displays its records.
Thus, rather than pass the criteria using the FilterName or
WhereCondition clauses, I prefer to pass the criteria using
OpenArgs, and let the form build the SQL or apply a filter to
itself during its Open event handler.

One example of where this technique pays off...

You open the same form from 10 different calling procedures, and
now you find you need to present the form data in a subform of the
main form. You can't simply apply the criteria to the main form
because it's not the one bound to the data, and there are now 10
places the code would have to be changed to accomodate the new
master/sub form design. Careful - don't miss one.

Well, that sounds like an ideal situation to have the form's default
recordsource be an empty recordset (or the uneditable single record
I described in another post), and then use public properties of the
form to set the recordsource.

DoCmd.OpenForm "MyForm", , , , , acHidden
Forms!MyForm.WhereClause = "[your criteria here]"

The .WhereClause property of the form would look something like
this:

Property Let WhereClause(strSQL As String)
Dim strRecordsource As String

' assumes module-level constant with the base SELECT/FROM
strRecordsource = c_strRecordsource
strRecordsource = strRecordsource & " WHERE " & strSQL
Me.Recordsource = strRecordsource
Me.Visible = True
End Property

That way you're treating the form as an object, and everything about
the form that needs to know what to do with itself is encapsulated
in the form itself. This could include issues like even passing the
form an argument to display a child record, then having that
property look up the parent record, navigate to it in the parent
form, then display the appropriate child record.

Yeah, that's a decent approach. I tend not to couple the an object's
initialization to the setting of the property though. I'd either make a Setup
procedure that takes a where condition (and any other required arguments), or
have a Startup procedure that takes no arguments and is called after setting
the prerequisite form properties.