Combiining multiple forms into one

[Karen]

I currently have 3 forms (Proj. history by proj number, Proj history by award
date, proj. history - all pending projects). Each pulls data from 3
different queries and 3 different reports. However, all are in the same
database and based on the same tables.

I'd like to put all the command buttons (search, requery, print preview,
print, etc.) and data from the 3 forms on 1 form but separate each group
(i.e., Project history by proj number, proj history by award date & proj
history - all pending projects).

It seems like I can only generate a form based on 1 query or 1 table at a
time. Is this so? In other words, can I pull up the field lists from any
query/table anytime while trying to complete the form? My field list is
always the one that I selected when first trying to write the form. How do I
bring up other field lists?

 

[Klatuu]

A form can have only one record source; however, that does not have to be
static. It can be changed at run time. That is not a problem as long as the
fields all the record sources are the same so they will still match up with
the control sources for the controls.

It looks to me like what you need rather than different queries, is just
filtering and ordering. If you want history on a specific project, you can
filter your form based on the Project Number. If you want it by Award Date,
filter by Award Date, and by all, remove the filtering.

The reporting would use the same logic. You can impose that by using the
Where argument of the OpenReport method.

 

[Karen]

Really, the reports and queries are not that simple. For example, the
project history by date allows the user to enter a start and end dates and
the information is being pulled from linked tables that provide a one-to many
relationships. I'd rather not have one query for all of the searches.

 

[Klatuu]

Well, you may be stuck with 3 forms, then. As I said in my last post, you can
change the recordsource of a form:

Me.RecordSource = "qselProjects"
or
Me.RecordSource = Me.optSourceSelect

But, since all your controls are bound to fields in the record source, it
may cause problems. If control is bound to a field in query1 that doesn't
exist in query2, then you will get an error at some point.

You can make it work, but it will take some careful coding. If a control is
valid for query1, but not for query2 or query3, then when you select one of
the others, you could make the control source "" and invisible, or change the
control source to a field in the other queries. The issue here may be in
validating data in the different versions.

If the 3 versions are different, it may make sense to just leave it at 3
forms. Now, be aware that I am the ultimate Access minimalist. I will
always look for a way to use the fewest number of objects and the fewest
lines of code, but in some situations, 3 straight forward forms may be better
than 1 very complicated form.