How do I use a same form, report, query for multiple tables?

[Guest]

I have multiple tables that have the same fields with different data
depending on the table. Is there somewhere in the form or query to set the
criteria for the user to decide which table to be entering data or retrieving
data?

 

[Rick B]

Sounds like your database structure is flawed. Generally, You should not
have multiple tables with the same fields. What is in your table? This can
almost always be accomplished by having one tale with one additional field
to define the "type" or record.

If your tables each refer to a different branch office, for example, you
could combine them into one and add an "office" field. Your queries,
reports, and forms could then use this field to limit the records returned.
IF you did this same scenario with a different table for each office, then
you would have to add a brand new table everytime your company grew. You'd
also have to create union queries and jump through other hoops if you ever
wanted to see combined reports. To then sum these by office would be even
harder.

It is likely that your structure could easily be changed to correct this and
save you many many headaches down the road.

 

[Rick Brandt]

I have multiple tables that have the same fields with different data
depending on the table. Is there somewhere in the form or query to
set the criteria for the user to decide which table to be entering
data or retrieving data?

You can use code to switch the RecordSource of the Form...

Me.RecordSource = "TableName"

What would be correct thing to do though would be to combine all the tables
into a single table with an additional field that distinguishes the sets of
records. Then you just filter on that value to view any particualr set.

 

[Brendan Reynolds]

Multiple tables with identical structure is almost certainly a serious
database design flaw.

That said, you would need to change the RecordSource property of the form.
For example, if you used a combo box to allow the user to choose a table,
you would add code such as the following to the AfterUpdate event procedure
of the combo box ...

Me.RecordSource = Me!NameOfComboBoxHere

 

[Guest]

Question: What makes the tables different from each other if they share the
same fields? Different years? Different projects?

You should not be using multiple tables to store the same kind of data.
Instead you should have something like a date field or project field so that
you can differentiate between the records in the criteria of a query. Thay
way you do not need to jump backwards through hoops trying to make queries,
forms, and reports work with numerous tables.