One Form to Use on 50 Different Linked Tables

[rjstephan]

I have one form that I want to use on 50 different tables (all with identical fields) that are linked to a front end so when the form is loaded a prompt will ask "which ID?" and that ID's table will be loaded in the form. How do I dynamically assign the form's record source this way? Thank you for your expertise.

 

[smitan]

I hope your tables are called tblSomeThingxxx, where xxx is equal to your ID.

Because in that way you will be able to set the recordsource in the FormOpen to

"Select * From tblSomeThing & " & format(ID,"000&quot

If not you might make a table that holds the tablenames and the ID.

Otherwise you might populate a combobox with all the tablenames from the database via code in the FormOpen.

Have fun, it's all about VBA and the Helpfiles!

Hans

 

[MichaelRed]

A different pproach coluld be to concatenate the different tabloes (UNION query), including the Id field and use the Id Field as a filter, but this could be quite cumbersome in 50 tables, wheather linked or not.

Another thought could be to have thelogin obtain the Id and link the appropiate table at run time to some standard /universal name.

Then, this is a bit off the beaten path, so some additional information might lead to other suggestions re managing the situation a bit more easily.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places