Invoice numbers

[jamjap]

I have the auto ID number set up set up on my Access database which gives me membership numbers. How do I have a supplementary auto numbering column to give me invoice numbers. You used to be able to do this automatically up until a few years ago. Now I have to enter them manually. There must be an easier way!

 

[pbaldy]

You can have an autonumber field in each table if you want. That would be the simplest solution, as long as you don't care if it occasionally skips a number. I'm assuming of course that you have separate tables...

 

[Anakardian]

you can always use dmax+1 to get the next higher number for the invoice in your invoice table.

 

[slogie]

Hi I originally had my invoice numbers as autonumber, but I would like for it to begin at 260 and then increase by 1 each time. How do I do that?

 

[pbaldy]

I think you can use an append query with a specified value for the autonumber field of 259, and it will pick it up from there. You're aware that an autonumber field can sometimes skip numbers and such? If you care about the numbers, autonumber may not be the right choice.

 

[slogie]

Thanks for replying. Well I did not know that an autonumber would skip numbers. So suppose I change the field to Number, how do I get it to generate a number seeing that it is the primary key? Or does this mean that I have to change it from being the primary key?

 

[pbaldy]

Typically the DMax() method suggested on your other thread.

 

[slogie]

So I know nothing about DMax, but from reading, I am thinking that I would use it in VBA on the form seeing that was how you helped me before with my previous problems. But the examples that I see, have the Nz function on it, and that I think would return a null value. Example(Nz(DMax("ID","Credit Invoice"),259)+1) I had tried this but it says that the key cannot contain a null value. So to be clear, I have ID as the invoice number and Credit Invoice as the table.

 

[pbaldy]

The Nz() function does not return a null, it prevents the return of a null. In this case, it's saying "give me the result of the DMax() unless it's null, then give me 259 instead".