I use MS Access 2003 to generate calculated totals for invoices that I need to post into QB. 

Using an Append Query I have been able to successully upload Invoices however all the line items show up on one invoice and the first customer appears in the invoice header info even though there are 4 customers total.

I have one Append Query that uploads the Line Items to the InvoiceLine Table and a second Append Query that uploads the Header information to the Invoice table (which I receive an error when I run it but the invoice turns up in QB anyway)  I'm using an Autonumber field to create the Invoice Number (RefNumber) - could the error be because they are different data types? 

What do I have to do to create multiple invoices with multiple line items?

Thank you!!!

The first article looks like the invoice is already assigned a TxnID in Quickbooks and the second option has the steps I followed to create the invoice but I still end up with only one invoice with all the line items in it.  My ListID fields are being taken directly from QuickBooks.

This is the SQL View of the 1st Access Query I created (to append line items)

INSERT INTO InvoiceLine ( TxnDate, CustomerRefListID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineQuantity, FQSaveToCache, RefNumber )
SELECT [1-PostedInvoiceTotals].InvoiceDate, [1-PostedInvoiceTotals].CustomerRefListId, [1-PostedInvoiceTotals].InvoiceLineItemRefListID, [1-PostedInvoiceTotals].Name, [1-PostedInvoiceTotals].GrandTotal, 1 AS Qty, [1-PostedInvoiceTotals].FQSaveToCache, [1-PostedInvoiceHeaderTotals].InvoiceNumber
FROM [1-PostedInvoiceHeaderTotals] INNER JOIN (LocalCustomer INNER JOIN [1-PostedInvoiceTotals] ON LocalCustomer.ListID = [1-PostedInvoiceTotals].CustomerRefListId) ON [1-PostedInvoiceHeaderTotals].CustomerRefListId = [1-PostedInvoiceTotals].CustomerRefListId;

NOTE: CustomerRefListID should not be used here

This is the second Query (to post the Header Info:

INSERT INTO Invoice ( RefNumber, TxnDate, CustomerRefListID, CustomerRefFullName, ARAccountRefListID, BillAddressAddr1, BillAddressAddr2, BillAddressAddr3, BillAddressAddr4, BillAddressCity, BillAddressState, BillAddressPostalCode, ShipAddressAddr1, ShipAddressAddr2, ShipAddressAddr3, ShipAddressAddr4, ShipAddressCity, ShipAddressState, ShipAddressPostalCode )
SELECT [1-PostedInvoiceHeaderTotals].InvoiceNumber, [1-PostedInvoiceHeaderTotals].InvoiceDate, [1-PostedInvoiceHeaderTotals].CustomerRefListId, [1-PostedInvoiceHeaderTotals].CustomerRefFullName, [1-PostedInvoiceHeaderTotals].ARAccountRefListID, [1-PostedInvoiceHeaderTotals].BillAddressAddr1, [1-PostedInvoiceHeaderTotals].BillAddressAddr2, [1-PostedInvoiceHeaderTotals].BillAddressAddr3, [1-PostedInvoiceHeaderTotals].BillAddressAddr4, [1-PostedInvoiceHeaderTotals].BillAddressCity, [1-PostedInvoiceHeaderTotals].BillAddressState, [1-PostedInvoiceHeaderTotals].BillAddressPostalCode, [1-PostedInvoiceHeaderTotals].ShipAddressAddr1, [1-PostedInvoiceHeaderTotals].ShipAddressAddr2, [1-PostedInvoiceHeaderTotals].ShipAddressAddr3, [1-PostedInvoiceHeaderTotals].ShipAddressAddr4, [1-PostedInvoiceHeaderTotals].ShipAddressCity, [1-PostedInvoiceHeaderTotals].ShipAddressState, [1-PostedInvoiceHeaderTotals].ShipAddressPostalCode
FROM [1-PostedInvoiceHeaderTotals];

Exactly!!  I WANT 4 invoices but in Quickbooks only 1 is created.  I'm trying to create Multiple Invoices with Multiple Line Items in each invoice.

Thank you for your time!

When I look at the InvoiceLine Table, QuickBooks has applied the first customer number to all of the LineItems, even though there were 4 different customer numbers (that match customers in QB) in the data.  (I guess that's why I get only one invoice).  How do I tell it to create one invoice per customer? 

Thank you!

Simply do single line invoices as a single SQL INSERT with all the CUSTOMER detail and FQSaveToCache FALSE (0):-

INSERT INTO "InvoiceLine" ("CustomerRefListID", "ClassRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('470001-1071525403', '30000-933272658', 'QODBCClass2', '250000-933272656',
'Building permit Additional Room', 100.00000, 100.00, '20000-999022286', 0)

Will this give me one line per invoice (I'd end up with 29 invoices as opposed to 4?)  I'm not sure how to do single line inserts through an access query.

Thank you again for responding so quick - you know this is driving me crazy to figure out!!

You're nearly there. When FQSaveToCache is set false, or the header insert is executed (after lines have been cached), the cached lines of the invoice will get writtened.

The last example will give you 29 invoices, while the other time you got 1 invoice with four lines? So you need to check the FQSaveToCache logic in your append queries. For example for two invoices with two lines in each I do:

insert invoiceline FQSaveToCache 1  <<< Invoice 1 line 1
insert invoiceline FQSaveToCache 1 <<< Invoice 1 line 2
insert invoice  <<< header sets FQSaveToCache 0

Invoice 1 gets written to QuickBooks

insert invoiceline FQSaveToCache 1 <<< Invoice 2 line 1
insert invoiceline FQSaveToCache 1 <<< Invoice 2 line 2
insert invoice  <<< header sets FQSaveToCache 0

Invoice 2 gets written to QuickBooks

See: Using ClassRefListID when inserting invoice lines for a better example and how to add a line to an existing invoice.

So do I have to create these invoices one at a time?  Again, I'm not familiar with SQL but I don't think Access will allow me to append to more than one table at a time.   Thank you so much for your time. 

You can create any number of invoices you wish with a range of invoice lines using a sequence of SQL statements. You don't need to use the Invoice header and you can do your inserts just using the InvoiceLine table like this:

insert invoiceline FQSaveToCache 1 <<< Invoice 1 line 1
insert invoiceline FQSaveToCache 0 <<< Invoice 1 line 2

Invoice 1 gets written to QuickBooks

insert invoiceline FQSaveToCache 1 <<< Invoice 2 line 1
insert invoiceline FQSaveToCache 1 <<< Invoice 2 line 2
insert invoiceline FQSaveToCache 1 <<< Invoice 2 line 3
insert invoiceline FQSaveToCache 0 <<< Invoice 2 line 4

Invoice 2 gets written to QuickBooks

instead

You are wonderful!!  Thank you so much!!