Ultimate Goal: Import Custom Access invoices to Quickbooks
I have been able to successfully utilize the QODBC driver and link some Quickbooks tables to my Access application. The Quickbooks tables I have linked are 'Invoice' and 'InvoiceLine'.
I have been able to manually import invoices into Quickbooks by using specific data (and not variables) -- example shown below. I need help now reading in the query and setting it to variables. I know I am almost there but I am running out of gas....
Below are elements made up of a macro created (it calls each of them in the below sequence). After step 4, I need to call another function/procedure to do the automatic insert into Quickbooks (that's where I need the help):
1. qryInvoicing_01 --- gets records who need invoice assigned in range required
SELECT Physicians.ClinicID, Sum([PhysicianPricePerLine]*[ChargeLines]) AS TotalCharges, Transactions.InvoiceNo, CDate([Enter invoice date:]) AS InvoiceDate, CDate([Enter begin date range (m/d/yy):]) AS BeginDateRange, CDate([Enter end date range (m/d/yy):]) AS EndDateRange INTO Temp
FROM Transactions INNER JOIN Physicians ON Transactions.PhysicianID = Physicians.PhysicianID
WHERE (((Transactions.TransactionDate) Between [Enter begin date range (m/d/yy):] And [Enter end date range (m/d/yy):]))
GROUP BY Physicians.ClinicID, Transactions.InvoiceNo, CDate([Enter invoice date:]), CDate([Enter begin date range (m/d/yy):]), CDate([Enter end date range (m/d/yy):])
HAVING (((Sum([PhysicianPricePerLine]*[ChargeLines]))>0) AND ((Transactions.InvoiceNo) Is Null Or (Transactions.InvoiceNo)=""));
2. AssignInvoiceNo() --- assigns invoice#
Public Function AssignInvoiceNo()
On Error GoTo Error_AssignInvoiceNo
Dim cn As New Connection
Dim rst As New ADODB.Recordset, rsc As New ADODB.Recordset
Dim invno As Long, LastClinic As Long, invdate As Date, begdate As Date, enddate As Date
rsc.Open "Select * from Control", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rsc.BOF Then
MsgBox "No Control record exists. Enter Control record and retry."
rsc.Close
GoTo Exit_AssignInvoiceNo
End If
rsc.MoveFirst
invno = rsc("NextInvoiceNo")
rst.Open "Select * from Temp", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rst.BOF Then
MsgBox "No uninvoiced transactions for this date range."
rsc.Close
rst.Close
GoTo Exit_AssignInvoiceNo
End If
rst.MoveFirst
invdate = rst("InvoiceDate")
begdate = rst("BeginDateRange")
enddate = rst("EndDateRange")
Do Until rst.EOF
rst("InvoiceNo") = "INV" & Format(invno, "0000000")
rst.Update
rst.MoveNext
invno = invno + 1
Loop
rsc("NextInvoiceNo") = invno
rsc("InvoiceDate") = invdate
rsc("BeginDateRange") = begdate
rsc("EndDateRange") = enddate
rsc.Update
rst.Close
rsc.Close
Exit_AssignInvoiceNo:
Exit Function
Error_AssignInvoiceNo:
MsgBox Err.Description
Resume Exit_AssignInvoiceNo
End Function
3. qryInvoicing_02 --updates transactions table with assigned invoice#
UPDATE (Transactions INNER JOIN Physicians ON Transactions.PhysicianID = Physicians.PhysicianID) INNER JOIN Temp ON Physicians.ClinicID = Temp.ClinicID SET Transactions.InvoiceNo = [Temp].[InvoiceNo], Transactions.InvoiceDate = [Temp].[InvoiceDate]
WHERE (((Transactions.InvoiceNo) Is Null Or (Transactions.InvoiceNo)="") AND ((Transactions.TransactionDate) Between [BeginDateRange] And [EndDateRange]));
4. Opens Report and Print Previews Invoice
5. ???? ACTUAL IMPORT INTO QUICKBOOKS - W/O VARIABLES:
DoCmd.RunSQL "INSERT INTO InvoiceLine(InvoiceLineItemRefListID," & _
"InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,InvoiceLineServiceDate,CustomFieldInvoiceLineOther1," & _
"CustomFieldInvoiceLineOther2,FQSaveToCache)" & _
"VALUES('80000005-1198111700',200,.13,26.00,'2007-11-01','42','Dr.Cox',1)"
DoCmd.RunSQL "INSERT INTO InvoiceLine(InvoiceLineItemRefListID," & _
"InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,InvoiceLineServiceDate,CustomFieldInvoiceLineOther1," & _
"CustomFieldInvoiceLineOther2,FQSaveToCache)" & _
"VALUES('80000005-1198111700',50,.13,6.50,'2007-11-01','60','Dr.Hill',1)"
DoCmd.RunSQL "INSERT INTO Invoice(CustomerRefListID,ARAccountRefListID,TxnDate,RefNumber,IsPending) " & _
"VALUES ('80000001-1198097828', '8000002A-1198097825', '2007-12-17', 'INV0001306',0)"
So in my new module....I need to read in the temp table that holds my new records and then set up variables in the above DoCmd statements. Anybody have any gas for me?
I have been able to successfully utilize the QODBC driver and link some Quickbooks tables to my Access application. The Quickbooks tables I have linked are 'Invoice' and 'InvoiceLine'.
I have been able to manually import invoices into Quickbooks by using specific data (and not variables) -- example shown below. I need help now reading in the query and setting it to variables. I know I am almost there but I am running out of gas....
Below are elements made up of a macro created (it calls each of them in the below sequence). After step 4, I need to call another function/procedure to do the automatic insert into Quickbooks (that's where I need the help):
1. qryInvoicing_01 --- gets records who need invoice assigned in range required
SELECT Physicians.ClinicID, Sum([PhysicianPricePerLine]*[ChargeLines]) AS TotalCharges, Transactions.InvoiceNo, CDate([Enter invoice date:]) AS InvoiceDate, CDate([Enter begin date range (m/d/yy):]) AS BeginDateRange, CDate([Enter end date range (m/d/yy):]) AS EndDateRange INTO Temp
FROM Transactions INNER JOIN Physicians ON Transactions.PhysicianID = Physicians.PhysicianID
WHERE (((Transactions.TransactionDate) Between [Enter begin date range (m/d/yy):] And [Enter end date range (m/d/yy):]))
GROUP BY Physicians.ClinicID, Transactions.InvoiceNo, CDate([Enter invoice date:]), CDate([Enter begin date range (m/d/yy):]), CDate([Enter end date range (m/d/yy):])
HAVING (((Sum([PhysicianPricePerLine]*[ChargeLines]))>0) AND ((Transactions.InvoiceNo) Is Null Or (Transactions.InvoiceNo)=""));
2. AssignInvoiceNo() --- assigns invoice#
Public Function AssignInvoiceNo()
On Error GoTo Error_AssignInvoiceNo
Dim cn As New Connection
Dim rst As New ADODB.Recordset, rsc As New ADODB.Recordset
Dim invno As Long, LastClinic As Long, invdate As Date, begdate As Date, enddate As Date
rsc.Open "Select * from Control", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rsc.BOF Then
MsgBox "No Control record exists. Enter Control record and retry."
rsc.Close
GoTo Exit_AssignInvoiceNo
End If
rsc.MoveFirst
invno = rsc("NextInvoiceNo")
rst.Open "Select * from Temp", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rst.BOF Then
MsgBox "No uninvoiced transactions for this date range."
rsc.Close
rst.Close
GoTo Exit_AssignInvoiceNo
End If
rst.MoveFirst
invdate = rst("InvoiceDate")
begdate = rst("BeginDateRange")
enddate = rst("EndDateRange")
Do Until rst.EOF
rst("InvoiceNo") = "INV" & Format(invno, "0000000")
rst.Update
rst.MoveNext
invno = invno + 1
Loop
rsc("NextInvoiceNo") = invno
rsc("InvoiceDate") = invdate
rsc("BeginDateRange") = begdate
rsc("EndDateRange") = enddate
rsc.Update
rst.Close
rsc.Close
Exit_AssignInvoiceNo:
Exit Function
Error_AssignInvoiceNo:
MsgBox Err.Description
Resume Exit_AssignInvoiceNo
End Function
3. qryInvoicing_02 --updates transactions table with assigned invoice#
UPDATE (Transactions INNER JOIN Physicians ON Transactions.PhysicianID = Physicians.PhysicianID) INNER JOIN Temp ON Physicians.ClinicID = Temp.ClinicID SET Transactions.InvoiceNo = [Temp].[InvoiceNo], Transactions.InvoiceDate = [Temp].[InvoiceDate]
WHERE (((Transactions.InvoiceNo) Is Null Or (Transactions.InvoiceNo)="") AND ((Transactions.TransactionDate) Between [BeginDateRange] And [EndDateRange]));
4. Opens Report and Print Previews Invoice
5. ???? ACTUAL IMPORT INTO QUICKBOOKS - W/O VARIABLES:
DoCmd.RunSQL "INSERT INTO InvoiceLine(InvoiceLineItemRefListID," & _
"InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,InvoiceLineServiceDate,CustomFieldInvoiceLineOther1," & _
"CustomFieldInvoiceLineOther2,FQSaveToCache)" & _
"VALUES('80000005-1198111700',200,.13,26.00,'2007-11-01','42','Dr.Cox',1)"
DoCmd.RunSQL "INSERT INTO InvoiceLine(InvoiceLineItemRefListID," & _
"InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,InvoiceLineServiceDate,CustomFieldInvoiceLineOther1," & _
"CustomFieldInvoiceLineOther2,FQSaveToCache)" & _
"VALUES('80000005-1198111700',50,.13,6.50,'2007-11-01','60','Dr.Hill',1)"
DoCmd.RunSQL "INSERT INTO Invoice(CustomerRefListID,ARAccountRefListID,TxnDate,RefNumber,IsPending) " & _
"VALUES ('80000001-1198097828', '8000002A-1198097825', '2007-12-17', 'INV0001306',0)"
So in my new module....I need to read in the temp table that holds my new records and then set up variables in the above DoCmd statements. Anybody have any gas for me?