Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
Access 2003 and QODBC - Finding invoice numbers in InvoiceLine table after using an append query |
Author |
Message |
|
Posted : 2006-11-01 01:06:51 |
I'm using QODBC version 199, Quickbooks Pro 2005 and Access 2003. I have successfully inserted data into InvoiceLine using an Append query. I just need to retrieve those invoice numbers created by QB (to be placed back into my Access databse). I am allowing Quickbooks to provide the invoice number (Refnumber) as it has for thousands of previous invoices within QB. Is there any way to find these numbers after the append query ? "Splastinsertid" only gives you the very last invoice number. Or do I have to insert each invoice 1 at a time and then retrieve the number using "Splastinsertid"? I have tried using a DLookup (searching for the InvoiceLineAmt and InvoiceLineDesc for each record in need of an invoice #), but it has provided incorrect or incomplete results each time i've tried to use it. I have even created a separate query from which to search on that includes only the invoices from InvoiceLine that were created on that same day (which should make the search much simpler and quicker).
I have created a work-around, but this involves using the Memo field temporarily to insert the Primary id# from my original Access table along with each Draw # (since we invoice the customer separately for each job and draw # that we have). I can then obtain the invoice number using DLookup because criteria is much simpler.
Is this the proper way to do this? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-11-01 09:06:12 |
Actually you simply query the Invoice table. For example if I just created 10 new invoices, I can do:
SELECT TOP 10 TxnID, RefNumber, Memo FROM Invoice order by TIMECREATED desc
to get the Transaction ID, Invoice Number and memo to match it to your Primary id#
|
|
|
|
TomW |
|
Group | : Members |
Posts | : 24 |
Joined | : 2007-04-03 |
|
Profile |
|
Posted : 2007-05-07 04:54:30 |
I was struggling with this today.
In Access, I can't get SELECT TOP to work with anything other than a 1.
I get a message saying 'expected number or parameters 1' ,
The following works and returns all the individual invoicelinetxnlindid that were just created for the the customer's most recent invoice.
SELECT TOP 1 InvoiceLineTxnLineID, TxnID, timecreated, CustomerRefFullName, InvoiceLineItemRefFullName, InvoiceLineQuantity FROM InvoiceLine AS UNOPTIMIZED WHERE (CustomerRefFullName='Aunt Mid Produce Co.') ORDER BY timecreated DESC;
If for example, I put a 3 in instead of the 1 ... it depends on where I do it...
If I do it in the Access design grid it works fine ( in my case 3 was the # of lines)
But if I do it from VB code module I would get an error about parameters expected being 1
Anyone want to enlighten me on this? Am I missing something?
Thanks |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2007-05-07 08:23:13 |
Your syntax has an error, it should be:
SELECT TOP 1 InvoiceLineTxnLineID, TxnID, timecreated, CustomerRefFullName, InvoiceLineItemRefFullName, InvoiceLineQuantity FROM InvoiceLine UNOPTIMIZED WHERE (CustomerRefFullName='Aunt Mid Produce Co.') ORDER BY timecreated DESC;
but if you reconfigure the QODBC optimizer, by checking "Optimize data after an Insert or Update", you don't need to use the UNOPTIMIZED tag:
SELECT TOP 10 InvoiceLineTxnLineID, TxnID, timecreated, CustomerRefFullName, InvoiceLineItemRefFullName, InvoiceLineQuantity FROM InvoiceLine WHERE (CustomerRefFullName='Aunt Mid Produce Co.') ORDER BY timecreated DESC;
|
|
|
|
|