Help Reading in Query and Setting to Variables

lawsonta

Registered User.
Local time
Today, 12:05
Joined
Dec 22, 2007
Messages
15
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?
 
Something like:

"VALUES('" & Variable1 & "', '" & Variable2 & "', '" & Variable3 & "')"
 
Thanks!!!! Okay, this is what my Quickbooks insert looks like using variables from my various queries.... it works for the most part, I still need to add some type of loop or something. It is only importing the first invoice into Quickbooks at the moment, when there are multiple ones that need to.... I will need to research the loop thing in Access VBA, so if someone wants to help with that I'm all ears...

Option Explicit

Public Function RUN_INVInsert()

Dim invoiceitemid As String, custid As String, arid As String
Dim invno As String, invdate As Date, transdate As Date, crglines As Long, phypriceline As Currency, subtotal As Currency
Dim transid As Long, phyname As String
'Dim totalcrg As Currency,clinicid As Long

Dim cn As Connection
Dim rs As New ADODB.Recordset
rs.Open "Select * from qryInvoicing_04", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rs.BOF Then
MsgBox "No transactions to import into Quickbooks."
rs.Close
GoTo Exit_INVInsert
End If

'clinicid = rs("ClinicID")
'totalcrg = rs("TotalCharges")

invno = rs("InvoiceNo")
invdate = rs("InvoiceDate")
crglines = rs("ChargeLines")
phypriceline = rs("PhysicianPricePerLine")
transdate = rs("TransactionDate")
transid = rs("TransactionID")
phyname = rs("PhysicianName")

subtotal = crglines * phypriceline
invoiceitemid = "80000005-1198111700"
custid = "80000001-1198097828"
arid = "8000002A-1198097825"

DoCmd.RunSQL "INSERT INTO InvoiceLine(InvoiceLineItemRefListID," & _
"InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,InvoiceLineServiceDate,CustomFieldInvoiceLineOther1," & _
"CustomFieldInvoiceLineOther2,FQSaveToCache)" & _
"VALUES('" & invoiceitemid & "','" & crglines & "','" & phypriceline & "','" & subtotal & "'," & _
"'" & transdate & "', '" & transid & "','" & phyname & "',1)"

DoCmd.RunSQL "INSERT INTO Invoice(CustomerRefListID,ARAccountRefListID,TxnDate,RefNumber,IsPending) " & _
"VALUES ('" & custid & "','" & arid & "','" & invdate & "', '" & invno & "',0)"

Exit_INVInsert:
Exit Function

End Function
 
Well, ideally you would insert with a SELECT statement rather than a loop:

INSERT INTO...
SELECT...
FROM...

If a loop is necessary:

Code:
Do While Not rs.EOF
  'insert here
  rs.MoveNext
Loop
 
Okay, this is the function looks like so far. When the function runs I need to insert multiple invoices that have multiple line items each. It is not working completely right now. Unfortunatley, it is creating a separate invoice for each line item (hope that makes sense). It should look like:

Invoice #1
line item 1
line item 2

Invoice #2
line item 3
line item 4

vs.

Invoice #1
line item 1

Invoice #2
line item 2

Invoice #3
line item 3

Invoice #4
line item 4

...
Do Until rs.EOF
invno = rs("InvoiceNo")
invdate = rs("InvoiceDate")
crglines = rs("ChargeLines")
phypriceline = rs("PhysicianPricePerLine")
transdate = rs("TransactionDate")
transid = rs("TransactionID")
phyname = rs("PhysicianName")
subtotal = crglines * phypriceline

DoCmd.RunSQL "INSERT INTO InvoiceLine(InvoiceLineItemRefListID," & _
"InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,InvoiceLineServiceDate,CustomFieldInvoiceLineOther1," & _
"CustomFieldInvoiceLineOther2,FQSaveToCache)" & _
"VALUES('" & invoiceitemid & "','" & crglines & "','" & phypriceline & "','" & subtotal & "'," & _
"'" & transdate & "', '" & transid & "','" & phyname & "',1)"

DoCmd.RunSQL "INSERT INTO Invoice(CustomerRefListID,ARAccountRefListID,TxnDate,RefNumber,IsPending) " & _
"VALUES ('" & custid & "','" & arid & "','" & invdate & "', '" & invno & "',0)"

rs.MoveNext
Loop
....

I need to find a way to insert the like InvoiceLine items and then the Invoice table insert immediately after. The InvoiceNo (or invno) is the field that groups them together. Any help, sample code, will be much appreciated. I am guessing a check for a new invoice number is needed before the Invoice insert?
 
It might help to see some sample data, to know the table structure. Can I assume that the recordset you have now only gets the header info? If it's the structure I think it is, you'd need a second recordset loop inside the first that got the line items for the current invoice. Then inside a loop of the main recordset you'd append the main record, and inside a loop of the line items you'd append the line items for that main record.
 
The recordset pulls all the needed information - for both the invoiceline table and the invoice table (the header info). It is the same query that I use to print the actual invoices via a report in Access.

Attached is some sample data from running qryInvoicing_04, which is what my recordset is set to. I updated a few things since my last post, so I am reposting my complete function code:

Public Function RUN_INVInsert()

Dim invoiceitemid As String, custid As String, arid As String
Dim invno As String, invdate As Date, transdate As Date, crglines As Long, phypriceline As Currency, totcharge As Currency
Dim transid As Long, phyname As String

Dim rs As New ADODB.Recordset
rs.Open "Select * from qryInvoicing_04", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rs.BOF Then
MsgBox "No transactions to import into Quickbooks."
rs.Close
GoTo Exit_INVInsert
End If

invoiceitemid = "80000005-1198111700"
custid = "80000001-1198097828"
arid = "8000002A-1198097825"

Do Until rs.EOF
invno = rs("InvoiceNo")
invdate = rs("InvoiceDate")
crglines = rs("ChargeLines")
phypriceline = rs("PhysicianPricePerLine")
transdate = rs("TransactionDate")
transid = rs("TransactionID")
phyname = rs("PhysicianName")
totcharge = rs("TotalCharge")

DoCmd.RunSQL "INSERT INTO InvoiceLine(InvoiceLineItemRefListID," & _
"InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,InvoiceLineServiceDate,CustomFieldInvoiceLineOther1," & _
"CustomFieldInvoiceLineOther2,FQSaveToCache)" & _
"VALUES('" & invoiceitemid & "','" & crglines & "','" & phypriceline & "','" & totcharge & "'," & _
"'" & transdate & "', '" & transid & "','" & phyname & "',1)"

DoCmd.RunSQL "INSERT INTO Invoice(CustomerRefListID,ARAccountRefListID,TxnDate,RefNumber,IsPending) " & _
"VALUES ('" & custid & "','" & arid & "','" & invdate & "', '" & invno & "',0)"

rs.MoveNext
Loop

rs.Close
Set rs.ActiveConnection = Nothing

Exit_INVInsert:
Exit Function

End Function
 

Attachments

All right, then I'd use a variable for the invoice number. Set it before you start to the first invoice number. As you move through the recordset, test that variable and only append to the main table the first time you hit each invoice number (and reset the variable). Every record, append a detail record. That should get you one header record for every invoice, plus all the related detail records. Does that sound like it will fit the situation?
 
Thanks for your help!! I tried setting it to a variable but it didn't work 100%. I have some invoices that could have 1 line item and some that have multiple so I could never get the compare to work with 100% certainty. However, I did follow your advice with the 2nd recordset loop inside the 1st recordset and it appears to work (fingers crossed). I am adding some more fields (address, etc) and will post that code when I get finished.

After that my next step is to be able to distinguish between customers and post invoices for that particular customer in Quickbooks, as my query will pull invoices for multiple customers. My thoughts are:
1. Create a link to the Quickbooks customer table (have to get unique customer id from there to use in my function above)
2. Somehow match against customers in Access (I may have to change the way Access stores the customer info). Names would have to be identical if I match against the name.
3. Find a way to tie all that in and reference it in my function
4. Pray it works
 
Well, the 2 recordset solution shouldn't be necessary if your 1 recordset contains all the data. It was probably a logic flow problem that made the variable method fail. In any case, if it's working for you, I guess run with it.

Praying is sometimes the only solution with Access. :p

Feel free to post back if you get stuck.
 
After seeing the two recordsets below you may understand why I decided to go this way. Very possible my logic was off on the variable solution! Very possible my logic is off below too, but its all I got, lol....

Public Function RUN_INVInsert()
Dim invoiceitemid As String, custid As String, arid As String, tempid As String
Dim dicdate As Date, crglines As Long, phypriceline As Currency, totcharge As Currency
Dim transid As Long, phyname As String
Dim invno As String, invdate As Date
Dim clname As String, cladd1 As String, cladd2 As String, clctzp As String

Dim rs As New ADODB.Recordset
rs.Open "Select distinct InvoiceNo,InvoiceDate,ClinicName,ClinicAddress1,ClinicAddress2," & _
"ClinicCityStZip from qryInvoicing_04
", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rs.BOF Then
MsgBox "No transactions to import into Quickbooks."
rs.Close
GoTo Exit_INVInsert
End If

invoiceitemid = "80000005-1198111700"
custid = "80000001-1198097828"
arid = "8000002A-1198097825"
tempid = "80000009-1198110414"
rs.MoveFirst

Do Until rs.EOF
invno = rs("InvoiceNo")
invdate = rs("InvoiceDate")
clname = rs("ClinicName")
cladd1 = rs("ClinicAddress1")
cladd2 = rs("ClinicAddress2")
clctzp = rs("ClinicCityStZip")

Dim rst As New ADODB.Recordset
rst.Open "Select * from qryInvoicing_04 Where InvoiceNo = '" & invno & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rst.MoveFirst

Do Until rst.EOF
crglines = rst("ChargeLines")
phypriceline = rst("PhysicianPricePerLine")
totcharge = rst("TotalCharge")
dicdate = rst("DictateDate")
transid = rst("TransactionID")
phyname = rst("PhysicianName")

DoCmd.RunSQL "INSERT INTO InvoiceLine(InvoiceLineItemRefListID," & _
"InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount,InvoiceLineServiceDate,CustomFieldInvoiceLineOther1," & _
"CustomFieldInvoiceLineOther2,FQSaveToCache)" & _
"VALUES('" & invoiceitemid & "','" & crglines & "','" & phypriceline & "','" & totcharge & "'," & _
"'" & dicdate & "', '" & transid & "','" & phyname & "',1)"
rst.MoveNext
Loop

rst.Close
Set rst.ActiveConnection = Nothing

DoCmd.RunSQL "INSERT INTO Invoice(CustomerRefListID,ARAccountRefListID,TemplateRefListID,TxnDate,RefNumber," & _
"BillAddressAddr1,BillAddressAddr2,BillAddressAddr3,BillAddressAddr4,IsPending)" & _
"VALUES ('" & custid & "','" & arid & "','" & tempid & "','" & invdate & "', '" & invno & "', '" & clname & "'," & _
"'" & cladd1 & "','" & cladd2 & "','" & clctzp & "',0)"

rs.MoveNext
Loop

rs.Close
Set rs.ActiveConnection = Nothing


Exit_INVInsert:
Exit Function

End Function
 
Well, I'm a believer in "if it ain't broke, don't fix it", so I guess I'd leave it alone. The variable method would have looked something like this pseudo-code, if my logic hasn't failed me:

Code:
check for empty recordset, bail out if empty
set variable to ZLS or something that will never be a real invoice number
do while not rs.eof
  if variable <> rs invoice number
    add main record
    set variable = new invoice number
  end if

  add detail record
  move next
loop
 
With Quickbooks, you have to insert all the child records first and then you can insert the parent record (the main record). I guess we could have flip flopped the logic a bit and got it to work. Mine looked kinda like that but obviously I did something wrong. If I get this customer thing working, I may go back and play with the logic above. Thanks for all your help!!!!

I'm going to post a new thread for my customer problem. Needing to check to see if customer already exists, If so just set a variable or something, If not insert customer record. Use dlookup or yet another recordset?

This thing is never ending.... (not too bad for a DBA though :p)
 
It sounds like you could simply move the add detail record bit above the If/Then that adds the main record. I think that logic is right, but of course you never know until you try. That seems like unusual logic, as normally you'd want to add the parent record first, or you'd violate referential integrity (child with no parent). But what Quickbooks wants, Quickbooks gets!

I've never worked with Quickbooks. My wife asked me about it for where she works, as I had already written them a nice little app that emails each customer an Excel file of their info every month. Took them 2 solid days to do it manually, now runs by itself in a few minutes. They're verrrry happy. Anyway, I said I could probably figure it out, but they stumbled on someone who already had experience with Quickbooks. They now wish I had done it, as the guy has turned out to be a flake. If they get too frustrated with him, I may be asking you for Quickbooks tips.

I'd probably use a recordset for the customer thing, but a DLookup or DCount would work too.
 
My experience in Quickbooks is now going on 1 to 2 weeks. But by the time I get done, I may deem myself an expert for sure. Without finding that QODBC driver, I would have been screwed.

I think I'm going to research the Dlookup or Dcount option, or at best take a break. I am confusing myself now......:confused:
 

Users who are viewing this thread

Back
Top Bottom