Receipt Number

[Guest]

Hello,

I am using a database to create and record invoices for my customers.
In the Invoice Form I have used:
=Nz(DMax("[invoicenumber]","invoice table"),0)+1 for invoice numbers and its
working fine.
Now at the end of every week, I have to issue at least 25 invoices for my
credit customers.
I tried to create a query to append the data automatically to my invoice
table and all was ok execpt the invoice number. What do I do to keep the
invoice number incremeting when I use this append query??

thanks in advance

 

[Nick 'The database Guy']

Hi Sam,

Why don't you use an autonumber field?

Nick

 

[John Spencer]

Could you post the SQL of the query that you are using?

It could be as simple as needing to force DMax to run for every record you
are appending. Perhaps, something like the following would work.

DMax("InvoiceNumber","Invoice Table","InvoiceNumber>" &
CLng([InvoiceNumber] Is Not Null))

Or you could build a simpleVBA function to get the Invoice number and call
that in the append query.

Public Function fGetNextInvoice( Optional X) as Long
fGetNextInvoice = Nz(DMax("[invoicenumber]","invoice table"),0)+1
End Function

In your append query you could just call that with any field name to get it
to increment
fGetInvoiceNumber([SomeField])