Greetings!

I am trying to create a Sales Receipt wiht 2 items. I am using Access Queries to test everything before I automate it (still in access).

I can insert the first line just fine but the 2nd line gives an error. I have tried the back up plan method of using the txnid instead of teh FQsavetocahse method. Neither have worked for me.

Creating the sales receipt I have

INSERT INTO SalesReceiptLine ( CustomerRefListID, RefNumber, SalesReceiptLineItemRefListID, SalesReceiptLineDesc, SalesReceiptLineRate, SalesReceiptLineAmount, SalesReceiptLineTaxCodeRefListID, FQSaveToCache, TxnDate, DepositToAccountRefListID )
SELECT [PaypalUSD-ImportPrepTableSorted].CustomerID, [PaypalUSD-ImportPrepTableSorted].ID, [PaypalUSD-ImportPrepTableSorted].ItemID, 'Paypal USD Sales' AS Expr4, 1 AS Expr5, IIf([Value of Payments Received]>0,[Value of Payments Received],[fees]) AS Expr2, [PaypalUSD-ImportPrepTableSorted].TaxCode, 0 AS Expr8, [PaypalUSD-ImportPrepTableSorted].Date, '3A0000-1181081224' AS Expr1
FROM [PaypalUSD-ImportPrepTableSorted];

works great - it pulls data from a table I have set up with my paypal transactions.

Then I try a second line - for the paypal fee.

I've tried using the above code a 2nd time with thew values changed for FQSaveToCache and the ItemSequenceNumber

this didn't work

I then tried the back up plan with the following code

INSERT INTO SalesReceiptLine ( TxnID, SalesReceiptLineItemRefListID, SalesReceiptLineDesc, SalesReceiptLineRate, SalesReceiptLineAmount, SalesReceiptLineTaxCodeRefListID )
SELECT 580-1185316153 AS Expr3, [PaypalUSD-ImportPrepTableSorted].ItemID, 'Paypal USD Sales' AS Expr4, 1 AS Expr5, IIf([Value of Payments Received]>0,[Value of Payments Received],[fees]) AS Expr2, [PaypalUSD-ImportPrepTableSorted].TaxCode
FROM [PaypalUSD-ImportPrepTableSorted];

I tried it by manully entering the TxnID in case this was the problme. - No go.

I get [QODBC]Field not allowed on insert.

Ok that confirms why the first try didn't work.

I did however read the forum notes about the Plan B method and I tried that as well.

Details are in my original post

My example:

INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID", "SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineSalesTaxCodeRefListID")
VALUES ('5C93-1197764640', '250000-933272656', 'Building permit 3', 3.00000, 3.00, '20000-999022286')

worked without error. The TxnID is a string, perhaps SELECT 580-1185316153 AS Expr3 should be SELECT '580-1185316153' AS Expr3 ???

I've tried both 'txnid' and without ' ' . The error is obvious without the ' '. With the ' ' I get

[QODBC] Field not allowed on insert

I pasted your insert code directly into my query. I had to change the format a pinch because access doesn't the the double " around everything

it looks like

INSERT INTO SalesReceiptLine ( TxnID, SalesReceiptLineItemRefListID, SalesReceiptLineDesc, SalesReceiptLineRate, SalesReceiptLineAmount, SalesReceiptLineTaxCodeRefListID )
SELECT '580-1185316153' AS Expr1, 'B0000-1182207491' AS Expr2, ' Buildingpermit3' AS Expr3, 3 AS Expr4, 3 AS Expr5, '60000-1180906326' AS Expr6;

I'm using the Canadian version of QB  if that makes a difference.

I decided to try the VB demo

and used the following code

INSERT INTO "SalesReceiptLine" ("TxnID", "SalesReceiptLineItemRefListID","SalesReceiptLineDesc", "SalesReceiptLineRate", "SalesReceiptLineAmount", "SalesReceiptLineTaxCodeRefListID")
VALUES ('580-1185316153', 'B0000-1182207491','Building permit 2', 2.00000, 2.00, '60000-1180906326')

I get the same error [QODBC]Field not allowed on insert

ok is there ANY way I can enter  2 line sales receipt using Access?

Am I going to run into this problem with all types of transactions where I want more than one line?

Any other things I should know that aren't going to work ? Before I bang my head against a wall for hours again. :)

Thanks in advance

You will need to execute the inserts within the one sub procedure. Here's an example:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
     Dim insertStr As String

     insertStr = "INSERT INTO InvoiceLine (" & _
                 "CustomerRefListID, CustomerRefFullName, " & _
                 "RefNumber, InvoiceLineItemRefListID, " & _
                 "InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, " & _
                 "InvoiceLineQuantity, InvoiceLineTaxCodeRefListID, FQSaveToCache" & _
                 ") VALUES (" & _
                 FormatStr(CustomerID) & "," & FormatStr(CustomerName) & "," & _
                 FormatStr(RefNumber) & "," & FormatStr(ItemListID) & "," & _
                 FormatStr("Desc") & "," & "1.0000," & "120.00," & "1.00," & _
                 FormatStr(GSTString) & ",1" & _
                 ")"

     Try
         ODBCExecute(insertStr)
     Catch Ex As Exception
         MessageBox.Show(Ex.Message)
     End Try

     insertStr = "INSERT INTO Invoice (" & _
                 "CustomerRefListID, CustomerRefFullName, RefNumber, BillAddressAddr1, " & _
                 "BillAddressAddr2, Memo" & _
                 ") VALUES (" & _
                 FormatStr(CustomerID) & "," & FormatStr(CustomerName) & "," & FormatStr(RefNumber) & "," & _
                 FormatStr("Bill1") & "," & FormatStr("Bill2") & "," & FormatStr("Memo") & _
                 ")"
     Try
         ODBCExecute(insertStr)
     Catch Ex As Exception
         MessageBox.Show(ex.Message)
     End Try

End Sub

Private Function FormatStr(ByVal aStr As String) As String
   Return "'" & aStr.Replace("'", "''") & "'"
End Function