Mass Invoice Number Generating Query

[AtlantianFerret]

I am tring to generate invoices for different groupings in a table that
will be posted back to my dues table. My temp table is based to two
different types of companies we invoice, a chapter (group of companies)
and individual companies that are not in a chapter area.

tempDuesSelection: Colums= CoID, CoPayee, and InvoiceNumber (there are
more but these are the significant ones. CoPayee is my seperator, C for
chapter and M for individual companies. I can get the Chapter ID's buy
linking to my tblCompanies table.

My invoice number is stored in ltblCodes and I usually access it get it
with this:
lngOriginalInvoiceNumber = DLookup("[CodeNumA]", "ltblCodes",
"[CodeAlpha] = 'Dues'")

With this my goal is to first assign a different invoice number to each
chapter that is being invoiced. Then stored with each company under
that chapter. After that assign a different invoice number to each of
individual companies (not part of chapter) and stored.

I have tried putting a function in an update query that would assign
the chapters each a seperate number. Then pull on that query with
another query that would update the tempDuesSelection table with the
approiate invoice number.

At first it had a problem because I reference a funtion I use when
processing individual invoices. Apperantly it will not allow me to
execute a sql statement in a funtion called from a query. After that
failure I used a global varible to store the invoice number and
incrimint it up by one. It only shows the same number for each one.

What can I do, with out creating several queries and tables?

Thank you,

BJ Moore
Information Systems Manager
IEC, Inc.

 

[BoxHead]

Is there only one record containing [CodeNumA] in ltblCodes where [CodeAlpha] would have a value of 'Dues'?

If there's more than one and you're trying to get the last one, try DMax instead of DLookup.

John



Life is short.
Build something.