re-using forms

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have several identical forms in my database which have all the same fields
and controls but have different record sources. The differing record sources
are simply queries with different selection criterea.The selection criterea are
based on values in other forms which are not always open. This is a real pain
when ever I modify the forms appearance as I have to repeat the changes on
several forms.

Is there a neat way to use only a single form, but with code (or something) to
select the relevant query depending on where the form was called from?

Many thanks,
Paul
 
Paul said:
I have several identical forms in my database which have all the same fields
and controls but have different record sources. The differing record sources
are simply queries with different selection criterea.The selection criterea are
based on values in other forms which are not always open. This is a real pain
when ever I modify the forms appearance as I have to repeat the changes on
several forms.

Is there a neat way to use only a single form, but with code (or something) to
select the relevant query depending on where the form was called from?
Click to expand...

You can use code at any time the form is open to change the RecordSource.

Forms!NameOfForm.RecordSource = "QueryName"
or
Me.RecordSource = "QueryName"
 
In place of using different queries..why not just get the conditions...and
pass that to the form?

When you open a form, you can pass it a "where" clause. that "where clause
is simply a correctly formed sql "where" without the word where.

This means you can build nice prompt form with all the conditions. This
approach will allow you to simply eliminate all the queries..and simply have
one query for the form.

For example, lets assume we have a un-bound form with the salesman, and also
a combo box with the City. The user would select what sales man, and also
what city for the forms records to show. The code to do this would be:

dim strWhere as string


if isnull(me.txtSalesRep) = false then

strWhere = "SalesRep = '" & me.txtSalesRep & "'"

end if

if isnull(me.txtCity) = false then

if strWhere <> "" then
strWhere = strWhere & " and "
endif

strWhere = strWhere & " City = '" & me.txtCity & "'"
endif

docmd.openform "yourform",,,strWhere

As you can see, you can make a way cool form with as many field prompts as
you want. Take a look at the following prompt screens. While these screens
are for reports, the EXACT same idea is used, and I use the "where" clause.
that way, I don't have to write a whole bunch of reports (or in your case
forms). Here is the screen shots to give you some ideas:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

and

http://www.attcanada.net/~kallal.msn/Search/index.html
 
Back
Top