CODEWITHSUNDEEP
databaseformsms-accesstabs
J. Taylor
J. Taylor

Reputation: 4865

MS Access: Accessing data from multiple tables in a tabbed form

I have an inventory system in MS Access 2010, and am trying to make a form that accesses multiple tables. Here is the relevant part of the data relationships printout: enter image description here

I have a tabbed form that I am using that, for each food item, allows our orderers to edit/view the food purchasing data for that food item, or edit/view the inventory data for that food item. This is what that looks like:

a picture of a tabbed MS access form that needs to access multiple tables

So, currently, the parent form is using [Food items] as the control source. I want the tabs to basically display (and update when edited) the appropriate data from the [Inventory] and [Food purchase data] tables for that food item. So for instance, in the example above, the Food purchase data tab should be searching for the record for Agave in the [Food purchase data] table, and using that to populate the fields for that tab (and likewise for the inventory tab).

What is the best way to go about this?

Upvotes: 0

Views: 3633

Answers (1)

Parfait
Parfait

Reputation: 107747

Simply, use subforms inside your tabs, maintaining Food Items as the main form's recordsource. Remember, forms are essentially visual representations of tables. So on the main form, build separate forms for Inventory and Food Purchases and then link them just as you do in relationships.

How to specifically integrate subforms on main form?

  1. Build subforms as separate regular forms, either as single, multiple-item, or datasheet. Don't place anything in page header/footer as they will not show on main form. You can use form header/footer.

  2. Then in design view of Food Items main form, go to the tab control and select tab area, making sure yellow outlines appears, then locate the subform/subreport control in menu in Design tab on ribbon (looks like a newspaper icon if you scroll towards the end). Select the form specific to tab.

  3. Walk through wizard, setting up the parent/child linked ids which you can also do on each subform control's Data tab in Property Sheet. Now, form will appear in its own design view where you can stretch out, re-position, and manage its own controls accordingly.

Upvotes: 1

Related Questions