Results 1 to 7 of 7
  1. #1
    theosgood is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2012
    Location
    Denver, CO
    Posts
    30

    Saving records in a Tabbed form to multiple tables

    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?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,582
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Jester0001 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2012
    Location
    Indiana
    Posts
    69
    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.

  4. #4
    theosgood is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2012
    Location
    Denver, CO
    Posts
    30
    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?

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,013
    Quote Originally Posted by theosgood View Post
    ...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.

    Quote Originally Posted by theosgood View Post
    ...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.

    Quote Originally Posted by theosgood View Post
    ...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!

    Quote Originally Posted by theosgood View Post
    ...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!

    Quote Originally Posted by theosgood View Post
    ..."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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    theosgood is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jun 2012
    Location
    Denver, CO
    Posts
    30
    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.

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,013
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-05-2012, 12:04 PM
  2. Saving Multiple Records at once
    By EvanRosenlieb in forum Access
    Replies: 5
    Last Post: 10-18-2011, 12:39 PM
  3. Tabbed forms and multiple tables
    By jonesy29847 in forum Forms
    Replies: 3
    Last Post: 03-22-2011, 10:03 AM
  4. Replies: 10
    Last Post: 12-13-2010, 11:49 PM
  5. Saving records in multiple sub forms
    By niak32 in forum Forms
    Replies: 0
    Last Post: 10-13-2008, 04:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums