Using a user-selected table to populate fields in a form

[NateH]

I have "inherited" an Access database. It has hundreds of tables, all with the exact same fields. And hundreds of forms, one for each table. Eventually, I plan to get it down to one table and one form, but for now, I'd like to create a form that allows the users of this database to access any one of the tables, but this has thus far proved to be a bit trickier than I thought it'd be.



First, I thought I'd set up a Combo Box within the form that feeds information to the Record Source. But that didn't work.

So then I created a form that simply asks which table the user wants to use, then opens the other form, feeding it the selected table. But that didn't work either.

Anyone have any suggestion of how I go about doing this? I should note that I am fairly new to Access.

[pbaldy]

Getting rid of all the tables is certainly the goal. In the meantime, your second idea should work, presuming the combo has a list of form names:

DoCmd.OpenForm Me.ComboName

[rpeare]

do all the identical tables have a similar naming convention and are they all identifiable by name from anything else in the database?

ie.e

tblSupportTable1
tblSupportTable2
tblIdenticalTable1
tblIdenticalTable2
tblIdenticalTable3
..
tblIdenticalTable100

If you have a reliable naming convention listing all the tables that meet a criteria in a list box or combo box is pretty easy, then it's just a matter of changing the record source for the form based on the selected table.

[NateH]

About two-thirds of the tables seem to adhere to one naming convention pretty well. About half of the rest adhere to what appears to be a separate naming convention. Which leaves about a sixth of the tables that appear to be named on a whim.

Nate

[NateH]

OK, so I entered "DoCMD.OpenForm Me.Combo0" (without the quotes) as the Record Source on the form's property sheet, but I'm getting an error message that says "The record source 'DoCMD.OpenForm Me.Combo0' specified on this report or form does not exist.

What am I doing wrong?

Thanks,
Nate

[pbaldy]

That was VBA code to be placed behind a button. The user would make their selection in the combo, then click the button.

[rpeare]

OK take it a step further, with VBA, with vba you could.

1. Scan all tables in your database
2. Look at the structure of each table, if it meets a certain criteria (the tables you're interested in) append that list to a list box or other control so you could view the list of tables.
IF *ALL* the tables in the database are identically structured this is much easier and you don't need to examine the structure to see if they can be a 'valid' data source.
3. After selecting the table click a butotn (or other event) to change the record source of the form
4. Requery the form with the new record source

and it should work

this is a very short description but likely the steps you'll want to follow.

[rpeare]

Simple example because I was bored and thought this looked fun!

NateH.zip