Andreas said:
I have a form with a subform inside which both manipulate 2 tables:
Quotations and QuotationDetails (One-to-many rs).
I have a button which when pressed will transfer the quotation
details to invoice.
To achieve this I tried to create a query. I selected Append type but
then it asks me which table I want to append. Here I have 2 related
tables. How can I do this for both tables?
First of all, is this possible or due to the (many part) of the 2nd
table that would be impossible?
Then, Append is the correct selection?
You need to run two append queries, or otherwise add records to the two
tables, in sequence. I'm assuming you have an Invoices table and a
related InvoiceDetails table. First you need to create a record in the
Invoices table, and then you need to create the necessary child records
in the InvoiceDetails table. The only catch is that, depending on how
you have defined the keys for these tables, you may not know the primary
key of the Invoices record until after you've added it -- but you need
to know that so that you can insert it as a foreign key in the
InvoiceDetails record. This would be the case if you use an autonumber
as the primary key of table Invoices, but not if you are using some
code-generated invoice number that you can calculate in advance.
If you're using an autonumber primary key, there are a couple of ways
around the problem. One is to use recordset operations, rather than an
append query, to add the record. That way, you open a recordset on the
Invoices table, call its AddNew and Update methods to add a new record,
and get the ID from the record before you close the recordset. Along
these lines:
Dim rsInvoices As DAO.Recordset
Dim lngInvoiceID As Long
Set rsInvoices = CurrentDb.OpenRecordset( _
"SELECT * FROM Invoices WHERE False")
With rsInvoices
.AddNew
!SomeField = SomeValue
!AnotherField = AnotherValue
' etc.
.Update
.Bookmark = .LastModified
lngInvoiceID = !InvoiceID
.Close
End With
Set rsInvoices = Nothing
Now you can embed lngInvoiceID into your append query to pull records
from QuotationDetails into InvoiceDetails.
