This is a long one,  I hope some will review it and help with a response.

I am creating an Access/Excel application that will have the sales orders entered into it.  Then Access will create in QB the BillOfLaden/Invoice with the proper price assignments.

We need to enter orders in a separate program from quickbooks in order to calculate total orders by item each day,   which then can be compared to available inventory (also outside of QB),  which then results in field instructions for harvesting vegetables. (very fast – most products harvest and ship the same day – thus making the QB interface ineffective for both inventory  and pricing).

I have been studying the various invoice creation examples in this forum earnestly.

So my design path alternatives look like this

• Path 1:  Manage Order Setup and Prices outside QB -> then Access writes orders with prices to QB
• Path 2:  After writing all price levels to QB (intermittently as needed),  manage order setup only outside QB,  manually re-enter orders into QB (so that price level macro’s function and prices are assigned by QB)

Right or wrong I have chosen Path 1

1.  Getting the Customer Info
The customer table is obtained from an Excel workbook that refreshes the data from quickbooks automatically if it is opened.  If a new customer were added to quickbooks one would have to open, save and close that workbook to have it reflect the new customer information.

The customer table is used in the access program as a lookup table to populate the various required Header Fields that will be inserted into quickbooks.  I choose not to utilize a ‘linked’ customer table in Access because it takes too long for Access to retrieve the customer data from quickbooks when a live link is used.  Question:  Is this way optimal? Or did I miss a clever way to use a live linked access table to quickbooks?

2. Getting the Items and Prices Info
I created a ‘PriceBook’  application in Excel that allows me to maintain several different price levels (NYSellingPrice,  DELPrice, FOBPrice, LessThanPalletPrice).  These prices are all functions of the NYSellingPrice so it is much easier this way than it is to try and maintain prices via the ‘PriceLevels’ features in quickbooks.  This workbook can also be updated (if new or activation/deactivation of products occurs) and it will preserve the assigned pricing values.

3. Creation of the BOL/Invoice –This is what I am currently working on.
The Access Database will store the orders in the tables OrderHeaders and OrderDetails.  Append queries will then be used to move the finalized order with prices to QB.  A finalized order occurs when shipping personnel finish loading the customers’ order on a truck and the final quantities are thus verified.

Questions:
1. Am I missing any critical issues in my approach path?

2. When I create a BOL/Invoice within QB,  choosing a customer fires a macro that populates many of the header fields automatically.  I am assuming by the forum examples that this is a function of the QB Invoice form and thus is not available to us if we are writing to the QB tables directly.  Is this correct? That I must explicitly write all the field values that I want/need in the SQL statements?  No automatic populating of various header values or invoking of QB macros that fill these fields?

3. Is the use of Excel (to get QB list data) the best way to provide the information for Access?  When I look in the optimizer folder I don’t see any files in there,  where does optimizer put the data?  Should Access be referring to this ‘optimized local store’ as opposed to QB directly?

4. It seems like Excel can refresh many QB tables in a few seconds where as Access is slow as molasses. Its almost as though optimization doesn’t work with access. Is this correct or am I going about it wrong?

Excel is a spreadsheet and not a database. Access will work fine providing you work with the data you really need. For example you just need the details of the customer you are invoicing, not all the customers in QuickBooks etc.

Creating a horizontal application that interfaces into QuickBooks is the way to go. You can look at: How do I create a new Customer, invoice them and mark the invoice as paid? to see whats involved in creating a customer and invoicing them. When invoicing the customer you just need the item name as you can invoice any pricing level you want.

Thankyou, I will revew the link you provided.

Sorry but your answer is way to short to be of much use to me. 

I want to find an existing customer,  not create a new one.  How do I find and return just one customer quickly?  I guess i don't understand your instruction.  I have to have a customer list in order to look up a customer,  don't I?  Creating a new customer and then finding the last customer created will not work.

I want to pass an order into QB from Access,  and I want to know if QB will auto-populate most of the header information for me with the customer's info. or if I must do this with the insert query when I create the header.

I want to pass the items ordered, quantities, and prices into the invoice,  either via the price levels feature in QB or just separately as I had proposed. 

I am aware that Excel is a spreadsheet,  not a database.  I will happily eliminate the front end Excel components,  but for me its least path of resistance,  and for simple list lookup requirements,  excel appeared faster.

Maybe I haven't studied the SDK functions available to me, and I should be using those in my Access???

Please excuse my ignorance on all these things,  I am trying to learn new stuff and I probably ask a lot of dumb questions.

Thankyou.

You're missing the whole point altogether. To create a invoice you just need three things:

1: The name of the customer you what to invoice (from the Customer table);
2: The name of the part or service to invoice (from the Item table);
3: The name of the sales tax code to use (from the SalesTaxCode table).

Providing all these things already exist in your QuickBooks company file, you don't need to read anything out of QuickBooks. You simply do one insert statement using the three things above. For example:

INSERT INTO "InvoiceLine" ("CustomerRefFullName", "RefNumber",
"InvoiceLineItemRefFullname", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefFullName", "FQSaveToCache")
VALUES ('Data Access Worldwide', 'QODBCINV1', 'Repairs', 'Repair NoteBook Computer',
200.00000, 200.00, 'NON', 0) 

In MS Access this can be run using DoCmd.RunSQL:

DoCmd.RunSQL "INSERT INTO InvoiceLine (CustomerRefFullName, RefNumber, " & _
    "InvoiceLineItemRefFullname, InvoiceLineDesc, InvoiceLineRate, " & _
    "InvoiceLineAmount, InvoiceLineSalesTaxCodeRefFullName, FQSaveToCache)" & _
    "VALUES ('My.CustomerRefFullName', 'My.RefNumber', " & _
    "'My.InvoiceLineItemRefFullname', 'My.InvoiceLineDesc', 'My.InvoiceLineRate', " & _
    "'My.InvoiceLineAmount', 'My.InvoiceLineSalesTaxCodeRefFullName', 0)"

and the following invoice was created in QuickBooks with all the customer details "auto-populated":

OK,  now I am understanding what you are saying.  Cool

I made a combo box to retrieve the customer list and various needed fields.  It works fine.

We don't charge tax (food items),  can I ignore the sales tax requirement?  If not could I use the SalesTaxCodeRefFullName that exists in the customer table (even if it is blank)?

I noticed that when I added fields to my customer combo box that they returned nulls,  so I recreated from scratch and then they returned values.  If I need to add a field to the combo box how do I force the QODBC to start returning the new fields?

I will be retaining these invoices in access even after they are posted to QuickBooks.  Can I let QuickBooks auto-assign the RefNumber when I post the order from Access to Quickbooks?  If so how do I return the newly created RefNumber and the TxnID to Access so I can later associate the access order with the qb invoice? 

You can ignore SalesTax. The combo problem is an MS Access issue, closing and reopenning the .mdb would also fix it. Because you are actually creating invoices in Access you should assign a Reference number that you can use to locate a invoice in both systems. Otherwise you will need to read the invoice table for the TxnID for the last new invoice for the customer using:

Select Top 1 TxnID from Invoice where CustomerRefFullName = 'Data Access Worldwide' order by TimeCreated desc

Tom, 

I have access now creating the invoice lines and I am working on the part about inserting the header.

I followed the following steps:

1. Insert first invoice line using customer name (then find it and capture the QBTxnID and QBInvoiceLineTxnID)

2. Insert additional invoice lines using QBTxnID (then find them and capture their QBInvoiceLineTxnID)

At this point I created a query to look for a QB invoice record with the QBTxnID, and to my surprise it doesn't exist! 

So my question is "Is this true?"  and if so "Why when I look in Quickbooks Create Invoices form does the invoice show up with some header values in it?"   Do I have to write all the values to the invoice table?  I thought it was pulling address blocks, etc in automatically...

Thanks

Sounds like your optimizer settings haven't been setup. Using the UNOPTIMIZED tag lets you query directly out of QuickBooks:

Select Top 1 TxnID from Invoice UNOPTIMIZED where CustomerRefFullName = 'Data Access Worldwide' order by TimeCreated desc

My recommended settings for using QODBC are shown here:

If you check the "Optimize data after an Insert or Update" option the new data will be optimized see: How do I setup the QODBC Optimizer? Where are the Optimizer options?  for an explaination of all the options available to you.

I have inserted 3 invoice lines into QB and then inserted the Header.

I now want to return the 3 InvoiceLineTxnLineID to Access. I cannot get it to return them until after I insert the header.

the following works perfectly in the Access design grid and in the VBDemo:

SELECT TOP 3 InvoiceLineTxnLineID, TxnID,  timecreated , CustomerRefFullName, InvoiceLineItemRefFullName, InvoiceLineQuantity FROM InvoiceLine UNOPTIMIZED WHERE CustomerRefFullName='Aunt Mid Produce Co.' ORDER BY timecreated DESC

The following works perfect from the Access Query design grid (red demarking subtle changes):

 SELECT TOP 3 InvoiceLineTxnLineID, TxnID,  timecreated , CustomerRefFullName, InvoiceLineItemRefFullName, InvoiceLineQuantity FROM InvoiceLine UNOPTIMIZED WHERE (CustomerRefFullName='Aunt Mid Produce Co.' ) ORDER BY timecreated DESC;                      

Lastly I put the query in Vb as follows (I left out the "_" line wraps as it wouldnt post in here right):

qFindAllInvoiceLinesByCustNameSQL= "SELECT TOP 3 InvoiceLineTxnLineID, TxnID,  timecreated , CustomerRefFullName,   InvoiceLineItemRefFullName, InvoiceLineQuantity FROM InvoiceLine UNOPTIMIZED WHERE  (CustomerRefFullName='" & aCustFullName & "') ORDER BY timecreated DESC;"

But when I execute the following VB command I get:  Run-time error 3061:  Too few Parameters. Expected 1.

Set rec = db.OpenRecordset("qFindAllInvoiceLinesByCustNameSQL")

What is it not liking???

Thanks

Ha!

Nevermind,  I didn't understand the 'TOP' command,  I was putting TOP 3 in there (3 as variable invlinecount).

When I did it as TOP 1,  I got all three records that I was looking for,  geeesh!!