0

I currently have this SQL query:

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('80002436-1519061496', 'Building permit 1', 1.00000, 1.00, '80000001-1478562826', 1)

Once it is successful, I would like this query to run:

INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES ('800001F6-1482536280', '8000001E-1478562986', #9/23/2020#, '1', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '80000002-1478562832', #10/31/2020#, #10/01/2020#, '8000295C-1541711590', 'Memo Test', 0, '80000001-1478562826')

What can I add in between the two queries? Maybe an "if" "then" statement?

UPDATE

Running them sequentially does work, but there seems to be another issue I'm seeing:

When just running the second query in Access everything works fine, but when running the second query in VBA SQL I receive the error:

" [QODBC] [sql syntax error] expected lexical element not found "

All values are hard coded, so I don't understand what the issue is?

Code:

Private Sub send_Click()

Const adOpenStatic = 3
Const adLockOptimistic = 3


Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL




sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"

sSQL = "INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID,IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES ('800001F6-1482536280', '8000001E-1478562986', #9/23/2005#, '1', 'Brad Lamb', '1921 Appleseed Lane', 'Bayshore', 'CA', '94326', 'USA', 0, '80000002-1478562832', #10/31/2005#, #10/01/2005#, '8000295C-1541711590', 0, '80000001-1478562826') "


Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Invoice was Sent to QuickBooks"
MsgBox sMsg


End Sub
  • Are these two statements being executed via VBA or just as stored queries? – Tate Garringer Jan 15 '19 at 20:28
  • They are being executed via VBA –  Jan 15 '19 at 20:29
  • 1
    I don't see why you couldn't just place them both in the same module and have them execute sequentially. Can you post the code you've tried and maybe I can get a better idea of what issues you're having? – Tate Garringer Jan 15 '19 at 20:35
  • So... where's the VBA code that's executing them? The queries themselves are rather irrelevant. – Mathieu Guindon Jan 15 '19 at 20:37
  • Please take a look at my update –  Jan 15 '19 at 20:52
  • As long as connection is actually established, the SQL should work. – June7 Jan 16 '19 at 00:22
  • https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/execute-method-ado-connection?view=sql-server-2017 - look into using the `RecordsAffected` parameter, which should return 1 if your insert is successful. If there's a problem with the insert in this case it should also raise an error though... – Tim Williams Jan 16 '19 at 01:23

0 Answers0