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 of all please create new MS Access database & link ALL table.
Please refer below mentioned article for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking table, we need to execute the query in MS Access query editor:
MS Access>> create tab >> Query Design >> It should popup for choosing table, please click "close" button & click on "SQL" from left top corner below 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 3 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, ItemSalesTaxGroupLine depend on InvoiceLineType. If InvoiceLineType is GroupItem then it is found in 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 enter the text you see in the image into the textbox below (we use this to prevent automated submissions).