Sharing Multiple tables on different forms

[techman1]

I am running MS access 2002. I am a semi-beginner I know enough to get what
I needed done done. However I am wanting to improve the performance of what
I have.

Basically it is this. I put together a database that contains all of the
processing we have to do at night. Different reports that have to be run
back-ups and everything else. However I have multiple reports that are the
same that have to be run for different products.

I broke each product out into its own form and table and have click boxes
that access them. However this leads to quite a few tables and forms.

I tried making a reports table that contained the reports and was just going
to add them in the forms as i created them. However when 1 box got checked
they all got checked. This cannot happen.

Each product has to be checked that it was ran. Is there anyway to share 1
table of reports with multiple forms and or do I have to keep it the way that
I have it.

ex:

Game_reports_1
Game_form_1
Game_reports_2
Game_form_2

etc

i would like to have
Game_reports_1

that feeds game forms 1, 2 etc but each one is recorded in a different spot?

i don't know if this is possible with 2002 i read somewhere that 2007 or one
of those versions could do it.

thanks for you help.

 

[Klatuu]

First, you have some serious design flaws.
You should have only one table and and form for products.
When you want to work with one specific product, filter the form's record
source which should be a query on the product table, not the table itself.

As to recording you reports having run, your overnight process should have a
table it uses to record what happened during the run. I use just such a
table with a process I run weekly to compact and repair about 500 mdb back
ends.
For each mdb, the table records when the process started, What the size of
the mdb was before the compact, when it ended, what size it was when it
ended, If the process ended correctly, and if not, what happened. Then it
runs a report using the table as the report's record source.

And, if you need to run reports by product, you can use a recordset based on
your products table to control the printing of the reports. Here is a
simplistic example:

Dim rst As DAO.Recordset
Dim strWhere As String

Set rst = Currentdb.OpenRecordset("tblProduct")
If rst.Recordcount > 0 Then
With rst
.MoveLast
.MoveFirst
Do While Not .EOF
strWhere = ![ProductID]
Docmd.OpenReport "MyProductReport", , , strWhere
.MoveNext
Loop
.Close
End With
Set rst = Nothing