Updating 2 tables using the same ID number

[stepone]

Hi,

I have invoice data held in 2 tables called 'tblInvoiceHeader' and 'tblInvoiceDetails'. tblInvoiceHeader will have one line per invoice, with multiple lines in tblInvoiceDetails.

I now need to transfer the data from those tables into 2 existing tables in our current application for further processing.

So, I plan to first import into tblInvoiceHeader into has an Autonumber ID field. I then want to import into tblInvoiceDetails and link these records using the same Autonumber ID as my foreign key.

What is the best way to do this ? I assume in VBA that I can rs.Insert, rs.Update, and then read the value of the autonumber field. But that feels the wrong way to do it, and anyway I try to keep VBA to a minimum in my applications. Is there a smarter way, using referential integrity, or some way that I can write one update query to do both updates and apply the same key value to both fields ?

I know that I could also create a form, with a linked child sub-form, and ask the user to enter invoice header details, and then add the lines one at a time in the sub-form manually. But is it possible to automate this ? So the user just has to click a button, and the data will be pulled across. I suppose I am thinking that the form would be based on the target tables. How I transfer all records from my tblInvoiceDetails into the subform so that the foreign key will be populated automatically?

Any advice is great appreciated,

StepOne

 

[Ranman256]

You append to the master table with a date stamp.
Now you know the name,date,and any other datum that identifies the record.
Use these to pull the key,
key =dlookup([ key],"table","[name]='bob'.....)

 

[stepone]

Hi Ranman,

So, first I append a record to the Header table, with some known details - e.g. Invoice Date, Company Name, Company ID. This will create an Autonumber reference.

I then write a query to append to the Details table, where the Foreign Key uses a DLOOKUP formula to get its value from the Header table, using fields on my form as its filter values.

Does that sound like what you mean ?

Thanks,
StepOne

 

[Ranman256]

yes, now you have the key,
run append records using it to your detail tbl.