Update multiple tables with 1 form

[Guest]

My boss had me create tables to list the EZpass tags for the cars in
different centers. He wanted me to have 1 table for each center, each table
listing all the cars for the center and the tag that goes with it.

I'm not sure why he wanted them separate, but that is what I was told to do.
I then created a form for each of these so if something needed to be added,
it could be done easily. However, I was wondering if there was a way for me
to create one form that could update whichever of the tables I want it to.

Example:

centers 1-10 each have a table all with identical field names

Form has the field names, I fill in center 2, car 50, tag number 45678
and the form updates only center 2s table

 

[Guest]

Well, first, you are correct in questioning why anyone would want you to
create many seperate tables with the same fields to store this type of data.
It is evident that that person (your boss) has no real understanding of
databases and how they are to be used.

That said, what you are wanting to do can be done. You can set the record
source for any form at the time is is opened.

One way to do this would be to use the OpenArgs option of the form. For more
info on this take a look in Help for the OpenForm Action.

For example, when opening the form, if you wanted to have the table for
Center #8 to be the table you are going to view and write to with your form,
you can use code like the following to open your form:

DoCmd.OpenForm "", , , , , , "tblCenter8"
Me.R
You can use any value as the value that will refer to a specific table.

Then in the OnOpen event of your form use a Case statement to set the Record
Source of the form based on the value passed by the "OpenArgs" property of
the OpenForm Method.

Something like (air code, untested):

Select case me.OpenArgs
Case "tblCenter1" '(here check the value you passed in the OpenArgs)
me.recordsource = "tblCenter1" '(here use the actual name of Center 1
table)

Case "NextOpenArgsValue"
me.recordsource = "tblCenter?" '(here use the actual name of another
table)

'here repeat the Case statements for each table

End Select