Saving records in a Tabbed form to multiple tables

[theosgood]

I am in Access 2007. I’m building a 6 tabbed form to replace 6 single forms.

My primary table is MOL. It has a one to many relationship with the AOL table. I’ve read that because I have a relationship, I can update multiple tables using one form. When I add fields I see my primary table and fields. I also see the related table and fields.
In the 6 form set-up, the MOL record is created first with event as the PK. Then the AOL records are created using the event field as its PK with an additional field.

On a single page form when you save the record the source table is updated for all fields on the form. I assume the same is true for tabbed forms. However, because my AOL tabs need to read the event value from the MOL, I need to save that portion of the form first. I was thinking of setting the AOL tabs to Visible = No. Then enter MOL data and create a function to save the record, set the MOL tabs Visible = No and AOL tabs Visible = Yes. It would be a lot simpler than writing a proc to save only the MOL fields, then another proc to save AOL records.



Before I decide which code to write does anyone know the if you save a tabbed form, does it save only the visible tab values or all values?

[June7]

You are using a tab control?

You have two tables so why are there 6 forms?

1 to many relationship means form/subform arrangement.

Place subforms on tab control pages.

Data on all tab pages will be saved.

[Jester0001]

With a tab control, all of the information on all of the tabs are loaded at the same time. Therefore, I believe that once you save, it will save the data on all of the tabs.

[theosgood]

Thanks for the reply.

Knowing that all tabs, visible = on or off is saved allows me to code around the acSaveRecord.

The original database was in 2003 and has poor table design. The MOL has 183 fields in 4 workable areas. So they built 4 forms to populate each area. The other 2 forms are to populate the AOL table for the same reason. Since I came on board I've heard that loading 6 screens and saving after each one takes too long and they wanted to create/edit on one form. The tabbed form allows me to have all 6 areas in one form with the appropriate fields in each tab.

"Place subforms on tab control pages" - Are you saying that I should use a subform in the tab to group my fields instead of individual fields in the tab? Would that require a save of the subform data as you go through tabs?

[Missinglinq]

...I’ve read that because I have a relationship, I can update multiple tables using one form...

This is true if each Form or Subform [B] is based on Data from a different Table.

...the MOL record is created first with event as the PK. Then the AOL records are created using the event field as its PK...

Actually, 'event' should be the PK in the MOL Table; in the AOL Table it would be the Foreign Key. The AOL Table should have its own, distinct, PK.

...On a single page form when you save the record the source table is updated for all fields on the form. I assume the same is true for tabbed forms. However, because my AOL tabs need to read the event value from the MOL, I need to save that portion of the form first.

In a Main Form/Subform scenario, Data is Saved automatically when moving from one component to another, i.e. when you move from the Main Form to a Subform, the Main Form data is Saved. When you move from a Subform to the Main Form or another Subform the first Subform data is Saved. And as I said, this is done automatically! No Command is needed. It's how Access works, by default!

...The original database...MOL has 183 fields in 4 workable areas.

Most experienced developers are going to tell you that any Table that contains more than 35-40 Fields is almost assuredly non-normalized. Since you're trying to correct sins of the past, I think you really need look at the way your data is set up!

..."Place subforms on tab control pages" - Are you saying that I should use a subform in the tab to group my fields instead of individual fields in the tab...

If you have Fields from your Main Table/Main Form on a Tabbed Control Page, they would go directly on the Page, and would be referenced, if necessary, as if they were simply on the Main Form.

The Fields from the related, AOL Table, would need to be on Subforms placed on Tabbed Pages, and would require a different syntax if they are to be referenced.

And 'yes,' you can 'mix 'n' match' on the Tabbed Control Pages! Some Pages can have Fields from the MOL Table and other Pages can hold Fields in Subforms, from the related Table.

Linq ;0)>

[theosgood]

Thanks Linq. Unfortuneately, I cannot do anything about the table structure, PK or FK. Those 2 tables are in 53 dept databases which feeds 9 grouping databases which feed 62 reporting databases. However, all your other information and suggestions were right on and worked perfectly.

[Missinglinq]

I'm glad you've got it working! But you misunderstood about the Primary Key/Foreign Key!

Unlike with a PK, the Foreign Key does not require you to do anything, it simply is a Foreign Key when it matches up with the Primary Key of a Related Table, which your AOL 'events' Field does! So this Field is the Foreign Key, without you needing to do anything to the Table! It's just a matter of terminology!

Good luck with your project!

Linq ;0)>