-1

I am using vba in MS Access to create Invoices in Quickbooks with QODBC. This process requires multi line invoice items to be inserted first and saved temp till primary invoice information is inserted. I have several invoices that are in need of being inserted as bulk.

EXAMPLE:

MultiLIne (INVOICE ITEMS) = Item #, OrderID, Item Desc, etc.

**MULTILINE matches PRIMARY invoice based on OrderID

Primary (INVOICE) = OrderID, Name, Address, Billing terms, etc.

**Primary is a single line record per orderID

"QB_AppendInvoice_LoopRef" contains the unique orderid's that need to be processed. I was trying to use this as a recordset to import the multiline items based on the current recordset orderid, however, I am unable to reference the current recordset orderid.

Dim db          As DAO.Database
Dim rs          As DAO.Recordset
Dim iCount      As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("QB_AppendInvoice_LoopRef") 'open the recordset for use (table, Query, SQL Statement)
   With rs

    If .RecordCount <> 0 Then 'Ensure that there are actually records to work with
        'The next 2 line will determine the number of returned records
        rs.MoveLast 'This is required otherwise you may not get the right count
        iCount = rs.RecordCount 'Determine the number of returned records
        Do While Not .BOF
            DoCmd.SetWarnings False
            'Append Invoice Line (determine tests ordered)
            Dim SQL1 As String
            SQL1 = "INSERT INTO InvoiceLine (CustomerRefListID, CustomerRefFullName, ARAccountRefListID, ARAccountRefFullName, InvoiceLineSerialNumber, InvoiceLineLotNumber, TemplateRefListID, IsPending, DueDate, TxnDate, InvoiceLineType, InvoiceLineItemRefListID, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache, RefNumber)" & _
            "SELECT Customer.ListID, Customer.FullName, '4C0000-1070045186', 'Accounts Receivable', Null, Null, '80000023-1495649075', '0', QB_ORDER_DETAILS.OrderDate, QB_ORDER_DETAILS.OrderDate, 'Item', QB_TestList_TestCodes.ListID, QB_TestList_TestCodes.FullName, QB_TestList_TestCodes.Description, QB_TestList_TestCodes.SalesOrPurchasePrice, QB_TestList_TestCodes.SalesOrPurchasePrice, '1', QB_ORDER_DETAILS.OrderID " & _
            "FROM ((Customer INNER JOIN contacts ON Customer.AccountNumber = contacts.Company) INNER JOIN QB_ORDER_DETAILS ON contacts.[Full Member Info] = QB_ORDER_DETAILS.Physician) LEFT JOIN QB_TestList_TestCodes ON QB_ORDER_DETAILS.ProductID = QB_TestList_TestCodes.TestCode " & _
            "WHERE QB_ORDER_DETAILS.OrderID = rs.Fields.getvalue('OrderID')"
            DoCmd.RunSQL SQL1, False

            'Append Invoice to Invoice Line (put the tests ordered on an invoice)
            Dim SQL2 As String
            SQL2 = "INSERT INTO Invoice (CustomerRefListID, CustomerRefFullName, ARAccountRefListID, ARAccountRefFullName, TemplateRefListID, [Memo], IsPending, IsToBePrinted, CustomFieldOther, ItemSalesTaxRefListID, TxnDate, DueDate, RefNumber)" & _
            "SELECT Customer.ListID, Customer.FullName, '4C0000-1070045186', 'Accounts Receivable', '80000023-1495649075', [Patient_Last] & ', ' & [Patient_First] & ' - ' & [Full_Specimen_ID], '0', '0', [Patient_Last] & ', ' & [Patient_First] & ' - ' & [Full_Specimen_ID], Null, [OrderDate], [OrderDate], Orders.OrderID" & _
            "FROM Customer INNER JOIN (Orders INNER JOIN contacts ON Orders.Physician = contacts.[Full Member Info]) ON Customer.AccountNumber = contacts.Company" & _
            "WHERE Orders.OrderID = rs.Fields.getvalue('OrderID')"
            DoCmd.RunSQL SQL2, False
        .MovePrevious
        Loop
    Else
        MsgBox "There are no records in the recordset."
    End If
        MsgBox "SENT TO QB - SUCCESS!!!"

    End With
        rs.Close 'Close the recordset
        Set rs = Nothing 'Clean up
        DoCmd.SetWarnings True

End Sub

Jena Strickland
  • 1
  • Don't use `DoCmd.SetWarnings False`, as you can't fix errors that you ignore (e.g.: missing blanks in front of FROM and WHERE, every string in SELECT has to be surrounded by quotes). Use `Debug.Print SQL` to check your SQL. Use an Error-Handler to deal with errors. Use `db.Execute SQL , dbFailOnError` instead of `DoCmd.RunSQL` for more error informations. – BitAccesser Jun 22 '17 at 18:42

1 Answers1

1

It's because you are updating a string, VBA can't use variables in string like that, here is the correct way of doiing it:

"WHERE Orders.OrderID = " & rs.Fields("OrderID").Value

And if the value is a string, you have to add quotes:

"WHERE Orders.OrderID = '" & rs.Fields("OrderID").Value & "'"
Alex Dupuis
  • 366
  • 3
  • 14