How to Put the QuickBooks Terms List into a Microsoft Access Table Using QODBC

Places QuickBooks Invoice Terms List into Microsoft Access table using QODBC .



What are Invoice Terms?


The number of days you give a customer to pay an invoice including any service charges and/or discounts. Invoice Terms appear somewhere on the invoice, usually at the bottom.

Example:

2% 15 Net 30 days: if the customer pays within 15 days, they receive a 2% discount but you expect full payment in 30 days.



Where do I find the QuickBooks Terms List?


Select:

Lists from the QuickBooks main menu
Customer & Vendor Profile Lists from the drop-down menu
Terms List from the fly-out menu



Purpose of Code:


This code uses QODBC to extract the Terms List from QuickBooks and puts that list into a Microsoft Access table (included in Microsoft Office XP).



Uses of code:


Used in functions or on forms or reports to place the Terms List into a table for viewing or creating a report or form or for use in a query or code that requires the Terms List.



Code:


Sub TermsList()
'places QuickBooks Terms List into Microsoft Access table

Dim db As DAO.Database, qDef As DAO.QueryDef, strQ As String
'create a new query to extract QuickBooks data via QODBC
'name new query
strQ = "qryTemp"
'function to delete existing queries - find this function here
fncDeleteQuery (strQ)
'assumes you're working in the current database
Set db = CurrentDb
'create the new query with the string variable that equals "qryTemp"
Set qDef = db.CreateQueryDef(strQ)
'make sure to return records
qDef.ReturnsRecords = True
'function to retrieve QODBC connection string - find this function here
qDef.Connect = QODBCConnect
'select all fields from terms table or whatever data you want to extract from it
qDef.SQL = "select * from terms"
'now the query is created and waiting so you are finished with the variables, set them to nothing
Set qDef = Nothing
Set db = Nothing
'declare a variable to hold the table name
Dim strTable As String
'give the table a name
strTable = "tblTermsList"
'turn off warning to replace tblTerms
DoCmd.SetWarnings False
'run SQL to extract the records from the query you made and put them into a table
DoCmd.RunSQL ("select * into " & strTable & " from " & strQ)
'turn warnings back on
DoCmd.SetWarnings True
'open the new table
DoCmd.OpenTable ("tblTermsList")
'delete the new query because the data is now in a table
DoCmd.DeleteObject acQuery, strQ 
End Sub




Requirements:

QODBC Read Version
Microsoft Access 2002
QuickBooks by Intuit


Required Microsoft Access References

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft Office Runtime Library
Microsoft DAO 3.6 Object Library

For more about Microsoft Access References see:

             www.vbquick.com/2011/09/microsoft-access-references_13.html

Comments

Popular Posts