How to save master and detail at same time? (1 Viewer)

[mcalex]

Hi

I have a form that collects (contact logging) data that will be saved to two different tables.

Up to now, I have been saving the master info, and then querying the table (using the data I just saved) to retrieve the primary key in order to correctly save the detail, but it strikes me that this is so common there must be a function for it somewhere and I'm being clueless.

Is there a way to automatically tell the detail save to use the primary key from the 'just saved' master info?

Specifically to this problem, after I have saved the details of a contact (phone call, meeting, email etc), I want to save info about who the contact was referred to info in another table using the ID of the contact as a foreign key. Is there an easy way to get this info?

many tia

mcalex

 

[vbaInet]

I imagine you mean the primary key of the record just created or inserted?

 

[MStef]

I suggest, make a MainForm (for master info), and a Subform (for a detail info). Link them via ID (Primari key in the first table, and foreign key in the second table), in the Relationships, also link them via "Link Child Fields" and
"Link Master Fields" properties. In that case you don't neeed a query.

Look at "DemoSubA2000.mdb" (attachment, zip).
Open "frmMain" and see.

 

[mcalex]

@vbalnet: yep, which is the problem, coz I don't have that ID until savetime.

 

[vbaInet]

@vbalnet: yep, which is the problem, coz I don't have that ID until savetime.

I think MStef might have something for you. If you use a subform you don't need to worry about how to get the ID because that will be handled by the subform control. Have you considered that approach?

 

[mcalex]

Ok, might give it a look. My form is a bit complex (drags in data from a bunch of tables to create the new record), so I may end up sticking with the Insert, then Select approach

cheers MStef and vbaInet

mcalex