|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Multi Line Invoices from Access to QB - same item |
| Author |
Message |
|
|
| Posted : 2007-03-14 07:20:44 |
I need to take invioce info from Access and go to QBooks that looks something like this:
Customer John Smith
purchased qty 10 units of MrKing Kong
and 20 units of Hairy Bear
The category for both of these is the same, ie, "collected bovine semen" They are not really 2 different line item descriptionis, I think it is kinda like having a lot number:
10 straws hairy bear
20 straws mr king kong
and then the same for other customers. I can get it to work with an append query but only get one ilne per customer so if the customer has 3 differnt animal, I get 3 different invoices.. And I need to do it with a batch of invoices and have the customer and order correctly matched up.
Please help!!!
bk |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-03-14 10:49:46 |
|
|
|
|
|
| Posted : 2007-03-14 11:07:41 |
Thanks Tom. . But I still am having a problem.. Here are my sqls from my access append query:
This gives me a syntax error on the From statement..... I am getting so frustrated! I have read all the forums and am getting confused..please help
Thank you SO much!
INSERT INTO InvoiceLine ( CustomerRefListID, ClassRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache )
SELECT 470001-1071525403 AS Expr3, 30000-933272658 AS Expr4, [QODBCClass2] AS Expr5, 9000-1123145194 AS Expr6, [qryInvoice_HB test].BullName, 100 AS Expr7, 100 AS Expr8, 20000-999022286 AS Expr9, 1 AS Expr10
FROM [qryInvoice_HB test]
INSERT INTO InvoiceLine ( CustomerRefListID, ClassRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache )
SELECT 470001-1071525403 AS Expr3, 30000-933272658 AS Expr4, [QODBCClass2] AS Expr5, 9000-1123145194 AS Expr6, [qryInvoice_HB test].BullName, 100 AS Expr7, 100 AS Expr8, 20000-999022286 AS Expr9, 0 AS Expr10
FROM [qryInvoice_HB test] |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-03-14 11:28:30 |
Just use a "WHERE" with the "FORM" to idetntify the record to add ike this example (as it can only be one record/row):
INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber", "Memo", "InvoiceLineItemRefListID",
"InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache")
Select "CustomerRefListID", {fn CONCAT('I', "RefNumber")} as "RefNumber",
{fn CONCAT('Sales Order ', "RefNumber")} as "Memo","SalesOrderLineItemRefListID",
"SalesOrderLineDesc", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID",
"FQSaveToCache" from SalesOrderLine where "CustomerRefFullName" ='Lamb, Brad:Room Addition' and "RefNumber"='201'
and "SalesOrderLineSeqNo"=1
or just run the INSERT statements directly like this:
DoCmd.RunSQL "INSERT INTO QB_InvoiceLine ( TxnID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, " & _
"InvoiceLineQuantity, InvoiceLineRate, InvoiceLineClassRefListID, InvoiceLineTaxCodeRefListID, " & _
"CustomFieldVFBatchNo, FQSaveToCache )" & _
"VALUES ('Me.TxnID', 'Me.RefNumber', 'Me.InvoiceLineItemRefListID', 'Me.InvoiceLineDesc', 'Me.InvoiceLineQuantity', " & _
"'Me.InvoiceLineRate', 'Me.InvoiceLineClassRefListID', 'Me.InvoiceLineTaxCodeRefListID', 'Me.CustomFieldVFBatchNo', 0)" |
|
|
|
|
|
| Posted : 2007-03-14 12:09:47 |
Thanks, I feel so dumb.. Now my query gives the error, "characters found after end of SQL Statement" I tried removing the FROM statement and got an error about no input table.
Also, If I want 3 line do I make another repeat, and 4 lines, yet another, etc???
INSERT INTO InvoiceLine ( CustomerRefListID, ClassRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache )
SELECT 470001-1071525403 AS Expr3, 30000-933272658 AS Expr4, [QODBCClass2] AS Expr5, 9000-1123145194 AS Expr6, qryInvoice_HB.BullName, 100 AS Expr7, 100 AS Expr8, 20000-999022286 AS Expr9, 1 AS Expr10
FROM qryInvoice_HB, [qryInvoice_HB test];
INSERT INTO InvoiceLine ( CustomerRefListID, ClassRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache )
SELECT 470001-1071525403 AS Expr3, 30000-933272658 AS Expr4, [QODBCClass2] AS Expr5, 9000-1123145194 AS Expr6, qryInvoice_HB.BullName, 100 AS Expr7, 100 AS Expr8, 20000-999022286 AS Expr9, 0 AS Expr10
FROM qryInvoice_HB, [qryInvoice_HB test]; |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-03-14 12:22:07 |
| You can only run ONE insert statement at a time, they can't be multiple inserts in the ONE query or append statement. You will also need to use a "WHERE" after the "FORM" to idetntify the ONE record to add as it can only be one record/row. |
|
|
|
|
|
| Posted : 2007-03-14 12:33:10 |
Are you saying that I need to run 3 append queries with insert statements that are nearly identical except for the FQSavetoCache????? I also don't understand how to use the WHERE statement in this case.
I am DUMB!!!! Sorry!
But, I know this can be done, right? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-03-14 14:14:20 |
Yes, you will need to run seperate 3 append queries.
WHERE (in this case) is used to identify the row in the other database table/query that you want to read the values from. |
|
|
|
|
|
| Posted : 2007-03-14 19:41:59 |
Do I make my queries to append each to Invoice Line or the first 3 to InvoiceLine and the Last to Invoice?
And will this work if I have on invoice with 2 lines and anotoher with 3 lines and yet another with 4 lines or more?
I made an append query to InvoiceLIne wih cache set to 1. It said it posted 4 rows. But nothing showed in QB. that is right. My second query appends to InvoceLine with cache set to o. That gave the error that the invoice is empty, but in Qbookds posted one invoice to my first customer name with all the line items to him. So, it did not match up right.
I need to post a batch of invoices from an Access query, so I can't type each customer name in line some of the examples in the articles.
And I ready in one of the forum articles that when using 2 queries there is a connection problem. They don't run on the same connection? I don't understand how VB32 takes the infor from my access query.
Thank you so much! I really need to get this done.... |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-03-14 20:01:03 |
You can create any number of invoices you wish with a range of invoice lines using a sequence of seperate SQL/Append 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
and so on as shown in Using ClassRefListID when inserting invoice lines
Most MS Access developers actually use VBA to write a procedure that loops through the insert statements required to post a batch of invoices. |
|
|
|
|
|
| Posted : 2007-03-14 22:51:36 |
ok...i will try it.....
so how many append queries do I need to make? 2 or more?
and where do I type in " invoice 1 line 1
invoice 1 line 2
invoice 2 line 1
invoice 2 line 2
invoice 2 line 3
invoice 2 line 4
invoive 3 line 1
invoice 3 line 2
invoice 4 line 1
invoice 4 line 2
invoice 4 line 3
My batch of invoices to transfer is about 25 each with a different number of lines....so how many queries do I need to maek? |
|
|
|
|