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

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote