ms-accessms-access-2007

One form to get record data from multiple unrelated tables?


I have several tables in MS Access which describe card collections. I want to keep the listings separate. I want one form that will access them all, so that I don't need to fiddle about clicking on different forms every time I want to update a card database. The more tables I include, the less practical this would get, anyway. Sites seem to suggest SQL-like statements as solutions.

I want one form that has a combo box menu to select the table to edit, then another box to select the card to edit the details of. I can do this if I make one form for one table.

I would like to check for new tables should I add them. E.g. when a new card series comes out I will create a new table for it, add the cards, and so on.

How do I make one form for many tables?


Solution

  • You can use subforms. Create a few subforms and your combobox and hide/show the forms as you desire.

    Creating separate tables for each card type is a bad practice. If the cards share features like MonsterName, MonsterPowerType or whatever, create another table card_types and add a card_type field to the cards table. Then if you need to get only Pokemon cards create a query with card_type_id = xx or card_type_name = "Pokemon" criteria. This way you only need one form and you can change the card_type criteria with use of your combobox.