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.
43
