Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Atlanta, Georgia, USA
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing records to other tables when closing form (Access 2000, SP1)

    OK- another newbie question. I have a form that is linked to a table that has an autonumber field. The table is updated properly when I write the record and close the form, but there are other fields on the form that need to be written to other tables, using the autonumber field as their primary key. Again, I'm sure this is a simple thing to do, but I can't find it. How do I tell Access to write the new records to all the other tables that have fields on that form?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Writing records to other tables when closing form (Access 2000, SP1)

    The values in all bound controls should be saved automatically if the user navigates to another record or closes the form. Bound controls are controls whose Control Source property is the name of one of the fields in the Record Source of the form.
    If you have a subform that is properly linked to the main form, the values of bound controls in the subform will also be saved to the correct location; however, if the subform is not linked correctly, you may have problems.
    If you have unbound controls whose values you want to save in a table, you must write code to do so. Before you do that, try to find out if you can't use bound controls instead.
    If you *do* want to write code, I think it should be in the BeforeUpdate event of the form. If you put the code in the OnClose event, it will only be executed when the user closes the form, not when the user moves to another record.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing records to other tables when closing form (Access 2000, SP1)

    A Simple way to do this is to have your form based on a query that includes all the tables you want filled.
    Let
    Regards,
    Peter

  4. #4
    Star Lounger
    Join Date
    Mar 2001
    Location
    Atlanta, Georgia, USA
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing records to other tables when closing form (Access 2000, SP1)

    This is, unfortunately, something that I've got to get working, but I didn't create myself. The 'master' record has an autonumber field, which is used as a key into the subservient tables. Why they weren't combined into one master record, I can't really say. But suffice it to say that I have to get it working. If there's not an easy way to gather up the fields and perform an 'Add these fields to a Record in table X', then I'll see if I can get the tables put together into bound fields on the main form. Either way, it will be an interesting exercise, and one which I don't have the luxury of boundless time.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Writing records to other tables when closing form (Access 2000, SP1)

    Do you want to spell out the table structure of what the form has to update.
    We may get a better idea if you post your DB (cutdown version w/o sensitive data).
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing records to other tables when closing form (Access 2000, SP1)

    To base your form on a query that includes all the tables shouldn
    Regards,
    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •