Change Record Source of Form on Opening/Loading

[Aftab Ahmad]

Hi there!

I have 16 tables with different names but all the fields are same in these tables even data types too. I have also one form named "Main Page" where I have 16 buttons for those tables called btn1, btn2, btn3, ....... btn16. I want to open another form named "Form1". Can I set the record source of this form on opening/loading as RecordSource = Me.btn1 or RecordSource = Me.btn2 etc.

[PhilOfWalton]

Firstly the answer is yes, but it almost certainly is a thoroughly bad idea.

To do what you want, you need to do something like

Private Sub Btn1_Click
    Dim StrSQL as String

    StrSQL = "SELECT Table1.* FROM Table1"

    DoCmd.OpenForm Form1
    Forms!Form1.RecordSource = StrSQL

End Sub

Obviously for Btn2 you would use Table2 etc.

I think you should have a look at a single table combining the 16 existing tables and add a new field "TableNo" indicating which table the data was from initially.

Then it is a simple matter to apply a filter to your form filtering data from Table1, Table2 etc. This will save so much effort if you need to change the table design.

Phil

[zmbd]

Aftab Ahmad:
While I agree with PhilOfWalton that this isn't the thing to be doing (besides we frequently late-bind forms to subforms in tab-controls for performance and this really is no different) - I do disagree with some of the finer points:

- Intead of the "single table combining the 16 existing tables and add a new field "TableNo" as suggested by PhilOfWalton - you should re-evaluate the overall design of the database. The fact that you have multiple tables with the same internal structure points to a highly non-normalized database.

This will lead you to issues such as needing a navigation form to open the tables for say 2014's records, 2015's records, 2016's records, etc... with each new year's records (or however one is batching the data) you'll have to go in and add a new button and new code. This becomes a maintenance nightmare; however, job security is assured... it also makes trending within queries between tables a lot more complicated than it really should be - whereas a normalized database should be much easier to handle and query.

We see this type of database all too often when someone directly converts a workbook to a database, assuming that each worksheet needs to be a table within the database - or simply, that they don't understand the nature of a RDMS. (hmm, mea culpa, in my early days, some very ugly databases - live and learn :) )

Normalization is your friend!
home > topics > microsoft access / vba > insights > database normalization and table structures

- The nice thing about PhilOfWalton's maping table is that you could use a combobox and pull the table name from the combobox and set the record source that way - example attached! - IMHO, if you insist on the current table arrangement this would be a much better solution than either command buttons or frame-option buttons. In this case I would use a combobox, one could just as easily use a list-box control; however, I personally dislike the list-box for things like selecting an option... for me, in this usage a list-box just seems cluttered to the eye.

+ I've also refined your approach using command buttons by changing over to Frame-Option buttons. I've not included it in the code; however, one could use a select-case to set the table name when using the option button approach. In this example the table names all have a root name and only need the value of the option-button (via the frame control) to be appended.

The "calling form" will open with the database.
When you click on either of the command buttons the calling form will close and the selected table will open in the "table form."

The code should all be self explanatory; at least it made sense to me at the time :)

The error trapping in the on_open event of the "table form" is to guard against the event a user mangles a table name (known to happen) or if a table name were to be mis-entered into the mapping table.

[Aftab Ahmad]

Thank you so much friends. This is just what I am searching.

[NeoPa]

ZMBD:
- Intead of the "single table combining the 16 existing tables and add a new field "TableNo" as suggested by PhilOfWalton - you should re-evaluate the overall design of the database. The fact that you have multiple tables with the same internal structure points to a highly non-normalized database.

I suspect you may actually be agreeing if I understand correctly, just expressing the same problem in different ways.

I would also support the idea that changing the RecordSource is a perfectly viable way to go - except it looks like the reason in this case is wholly invalid. There are good reasons to do this, and of course it can be done, but this does very much sound like your design is the real problem and with a proper design you wouldn't even be getting into this area.

Good luck with your project :-)