Knowledgebase
[QODBC-Desktop] How to create Invoices through MS Access using QODBC
Posted by Jack - QODBC Support on 05 August 2014 12:40 PM

Examples of Creating Invoice using QODBC

First, please create a new MS Access database & link ALL tables.
Please refer to the below-mentioned article for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking the table, we need to execute the query in the MS Access query editor:
MS Access>> create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the File menu.

Note: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have separate smaller commands for the Invoice Lines and a final  INSERT for the Invoice Header.

Create an Invoice with 3 Lines

     Here we create an invoice with three lines by using 3 InvoiceLine INSERT commands (with the FQSaveToCache caching flag set true) followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately.

First Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 1', 1.00000, 1.00, '20000-999022286', 1)

 http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/step1.png

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step1.png

 

Second Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 2', 2.00000, 2.00, '20000-999022286', 1)

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step3.png

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step2.png

 

Third Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 3', 3.00000, 3.00, '20000-999022286', 1)

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step4.png

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step2.png

 

Header Information

INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES ('620000-1071526513', '40000-933270541', #9/23/2005#, '1', 'Brad Lamb,' '1921 Appleseed Lane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '10000-933272658', #10/31/2005#, #10/01/2005#, '360000-1071521681', 'Memo Test,' 0, '10000-999022286') 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step5.png

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step2.png

Results in QuickBooks 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step6.png

 

Related Data Location

InvoiceLineItemRefListID

InvoiceLineItemRefListID is '320000-1071525597' and found like this:

     Select ListId, FullName from Item where FullName like 'C%'

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step7.png

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step8.png

 

InvoiceLineSalesTaxCodeRefListID

InvoiceLineSalesTaxCodeRefListID is '20000-999022286' and found like this:

     Select ListId, Name from SalesTaxCode where Name like 'N%'

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step9.png

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step10.png

CustomerRefListID

CustomerRefListID is '620000-1071526513' and found like this:

    Select ListId, FullName from Customer where FullName like 'Smi%'

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step11.png

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step12.png

 

ARAccountRefListID

ARAccountRefListID is '40000-933270541' and found like this:

     Select ListId, Name from Account where Name like 'Account%'

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step13.png

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step14.png

ItemSalesTaxRefListID

ItemSalesTaxRefListID field is related to ItemSalesTax, ItemSalesTaxGroup, and ItemSalesTaxGroup,Line depending on InvoiceLineType. If InvoiceLineType is GroupItem, then it is found in the ItemSalesTaxGroupLine table.

ItemSalesTaxRefListID is '360000-1071521681' and found like this:

     Select ListId, Name from ItemSalesTaxGroup where Name like 'E%'

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step15.png

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step16.png

CustomerSalesTaxCodeRefListID

CustomerSalesTaxCodeRefListID is '10000-999022286' and found like this:

     Select ListId, Name from SalesTaxCode where Name like 'T%'

 

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step17.png

http://support.flexquarters.com/esupport/newimages/InvoicesMSAccess/Step18.png

(1 vote(s))
Helpful
Not helpful

Comments (2)
carlos urquilla
03 December 2015 05:45 PM
Is the sample above to add 3 lines to an existing client? if yes why the header CustomerRefListID, is not the same id as the adding lines?
How did you get the ARAccountRefListID?
How did you get the ItemSalesTaxRefListID?
the first 3 lines work perfectly fine for me but when i tried to add the header i get errors.
Jack
04 December 2015 09:09 AM
Hi Carlos,

Yes, above sample 3 lines for existing customer. In above example CustomerRefListID is inserted in header only not in line items. Please refer “Related Data Location” section in above page for related data information.

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).