Creating invoices and exporting to accounting system

[Guest]

We have an order processing mdb that I developed and which is handling
everything fine, but we have to do our billing separately within the
accounting system.
I want this to be done within my mdb (all the data is there and I can easily
create the necessary mergefields etc).
The resulting invoices would need to be exported as a csv to the Sage sales
ledger (there is a well-estalished import routine within that package so no
problem there).
Question is, once the invoices are generated and residing within an Invoices
table, how do I select those that I need to export? Obviously they should
only be exported once, so there needs to be some sort of flag on them once
they have been exported to prevent them being selected within any subsequent
batch.
I'm sure I'm hardly the first to be doing this, so can somebody please help
with this or point me to a sample mdb that includes a similar process?
Thanks a lot
CW

 

[Al Campagna]

CW,
I would add a Date/Time field to tblInvoices, say... ToSageDate, that captures the date
and time the record was exported.

The Export to .csv would involve two steps...
Export a Select query to .csv that only selects tblInvoices records where ToSageDate =
Null
Then immediately run an Update query against tblInvoices, setting all ToSageDates that
are Null to Now()

SageDate serves two purposes, it indicates that that record has been exported, and when
it was exported... in case anything goes wrong.
Let's say on 1/1/07 @ 17:00, 20 records were exported, and some problem occurred.
You could now easily filter for that ToSageDate in a query, reset those 20 dates to
Null again, and rerun the export again.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Al -
Thanks for your suggestions - especially the bit about how to handle things
when it goes wrong. Not if, but when, cos it surely will at some point!
I'll try to start developing some of that stuff next week. Stand by for
further queries!!
Thanks again
CW