Invoice Number Problem

[slogie]

Hi there again. I just came across a problem that I didn't recognize before. I had an invoice table and corresponding form that had an autonumber for the invoice number. The problem now is that I would like the invoice number to begin at 256 and then increase incrementally, because I had some previous invoices from my books. I however, have no idea how to do so. So if anyone could advise on how to go about doing this. Thanks.

 

[CJ_London]

you should not use autonumber as a field which means anything (like an invoice number) since you have little control over what the system will generate other than a unique number.

You need a separate field for the invoice number which can be indexed and set not to allow duplicates, it can even be the primary key.

To create an incremental number use something like the dmax function

newInvnum=dmax("Invnum","myTable")+1

but you should not assign this until the last moment before updating (i.e. in the form before update event).

It won't matter if the system is single user but if you have concurrent users there is still a risk that two users update at exactly the same time so both invoices have the same number so you will need additional code to check the invoice has updated and if not to try again with a new 'max' number.

 

[slogie]

Thanks for replying. I already have the inv. number as a field and it is the primary key. The only thing is that I had it as autonumber. I have never used this dmax function before so I really do not know how it works. The name of the field is InvNum. So how do I use it in a function? Do I do it from the table or in the form alone?

 

[CJ_London]

this will tell you about the function

https://support.office.com/client/DMin-DMax-Functions-f5c6de87-8752-4f29-ab4b-c554053d299f

you use it in a query or in vba, not a table

 

[slogie]

Thanks for the link but I am afraid that I really do not understand it. I should state that I really know nothing about coding and vba, but I do think that I would have to use vba for this. I originally just used ID as the invoice number (this is an autonumber, as well as the primary key - no duplicates) and the table is Credit Invoice, and it works perfectly between forms and tables. However, as I had mentioned, I just want it to be able to start numbering at 257 and then continue numbering from there. How do I write that in VBA? If that is not too much to ask. Thanks

 

[CJ_London]

How do I write that in VBA? If that is not too much to ask.

The point is you can't - this link will explain more about what an autonumber is and isn't

http://www.utteraccess.com/wiki/index.php/Autonumbers

There are ways to reset it (for example, delete the contents of the table then compact) or to start from a number other than zero (called seeding), but the table has to have no records to do this. Neither of these meet your requirements