How to Put QuickBooks Invoice Terms into a Microsoft Access Table

Places QuickBooks® Invoice Terms for Selected Invoices 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% 10 Net 30 days: if the customer pays with 10 days, they receive a 2% discount but you expect full payment in 30 days.



Where do I find the QuickBooks Terms when creating invoices?


You may or may not use terms on your customized invoices. QuickBooks invoices like Intuit Product Invoice or Intuit Professional Invoice come with a Terms drop-down box. This is where you select the Terms for each invoice and where you may add new Terms.

You may also select Terms for customers from the Edit Customer window.



Purpose of Code


This code uses QODBC to extract the Terms applied to Specific Invoices from QuickBooks and places those records into a Microsoft Access 2 (included in Microsoft XP) table.



Uses

Used in functions or on forms or reports to place Terms applied to Specific Invoices into a table for viewing or creating a report or form or for use in a query or code that requires those records.

You must limit the invoices you wish to query using one or more parameters.

The code here selects invoices by date but you may also use other parameters such as invoice numbers, customer names, job names, or any other parameters you wish. If you do not use parameters to limit the query, QODBC will query all invoices and could hang for a while.



Code


Sub TermsFromInvoices()
'puts QuickBooks terms from specific invoices into a 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 = "strQ"
'use function to delete named queries if they exist or you will get an error message that the query already exists - find this function here
fncDeleteQuery (strQ)
'set db to the current database
Set db = CurrentDb
'create a querydef
Set qd=db.createquerydef(strQ)
'set new query to return records
qDef.ReturnsRecords = True
'uses this function to retrieve QODBC connection string - find this function here
qDef.Connect = QODBCConnect 
'select terms and whatever other data you want to extract from the invoice records'be sure to qualify by date, reference number or some factor to limit results or query make take all day'note the QODBC date format
qDef.SQL = "select customerreffullname,termsreflistid,termsreffullname from invoice where txndate>{d '2011-06-01'} and txndate<{d '2011-06-30'}"
'now the query has been created and exists in your database'you may now set the querydef and database to nothing because you are finished with them
Set qDef = Nothing
Set db = Nothing
'declare string variable to hold table name where you will put the query results
Dim strTable As String
'name the table
strTable = "tblTerms"
'turn off warnings to create table DoCmd.SetWarnings False 'run SQL to extract query data and put into the table you just named
DoCmd.RunSQL ("select * into " & strTable & " from " & strQ)
'turn warnings back on
DoCmd.SetWarnings True
'open the table you just made
DoCmd.OpenTable ("tblterms")
 'delete the new query
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




Do you like this post? Please link back to this article by copying one of the codes below.

URL:

HTML link code:

BB (forum) link code:

2 comments:

  1. 'create a querydef
    'set new query to return records

    is something missing between these two lines? I got an error message "Object variable or With block variable not set" and Debug goes to qDef.ReturnsRecords = True line.

    ReplyDelete
  2. Set qDef = db.CreateQueryDef(strQ)

    ReplyDelete