Link one form to different tables containing the same fields.

[Moss100]

Hi there.

I have a database which has hundreds of tables (each one an accounts table).

The table fields are therefore exactly the same as each other.

I would like to use a single form to acess the data from each table.

How do I make set a table to become the control source for the form. At presnet I seem only to be able to be able to tie one table to the form.

For example. If i take tables 1, 2 and 3 as exapmles.

In the form view the control source can be set as Table 1. This obviously means I would then need another form for table 2, etc....

It must be possible to set the form control source on open?

(PS the tables are in an external table).

 

[randy700]

If you have hundreds of identical tables, your database is not normalized. You should move all of the data into one table. You would probably need to add one field to identify the "table" your data originated from. For example, if each table represents a different customer, simply add a customer number field to your new (normalized) table. You could then create a form based on this table or, better yet, a query that gets it's data from this table.


Randy

 

[Moss100]

Yes it may seem that way, but the database is a commercially written database and so I am stuck with that. There are upwards for 2000 tables!!! - so combining these is not an option.

Thanks anyway - Regards Mark

 

[PWise]

while i agree with randy that your db is not normalized you have a few options
1)use code on form open

me.recordsource = tablexxxx

2) use a union Sql statment as a recordsource

Select *
from table1
union all 
select *
from table2
....

union all 
select *
from tablexxxx

create a query from the above sql statment

and st the recordsouces to that query

 

[TheAceMan1]

How are ya Moss100 . . .

Since the table fields are exactly the same, all you need is a single form with a combobox, the combobox having the list of table names ... wheheby a selection from the combo changes the [blue]tablename[/blue] of the forms [blue]recordsource[/blue].

[blue]Your Thoughts?[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Moss100]

Hi theaceman,

Your suggestion sounds along the right lines.

I have a continous form which contains a field with the table name in it called [table number]

I would like to click on a record from this continuous form and then set the opening forms recordsource with the [table number]. This is the part I don't know how to do.

Thanks for any pointers, Mark

 

[TheAceMan1]

Moss100 . . .

Try an unbound combobox for this. Its possible the table your looking for doesn't show in the recordset, let alone hunting thru records to find the one to click.

Use the combobox wizard!

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]