How to number records correctly?

[Ugurovic]

Dear all,

I want to have a clear numbering in my access db.
Problem is like this.
Now I have field with an input mask of "LABEL"0000;;_ , I have to put the value of the label myself but I want that the db checks de last value en adds a "+1" to make a new one. 'Autonumber' is not Ok because if you cancel the record, it automatically cancels the increased number and by a new record it puts the increased value+1 in the new record...

So to make it clear, if I make a new record and I press save=> LABEL0001; if I cancel and after a while I want to make a new record it must start bij LABEL0001 or 0001 and not 0002!!!

Thanks in advance

Ugurovic

 

[wazz]

please search the forums for many many many threads/posts about this issue. (creating primary key, etc.)

i never do this, but i think that you might consider actually *using autonumber*, so Access will take care of that business (which is what it is good for), then adding a *separate field* for your LABEL.

when you create a record, look up the last LABEL and update your separate field. if a record/update is cancelled, the autonumber will be "out of order" but it won't matter for your LABEL. you can look up the last LABEL the next time and all will be well. hth.

 

[Ugurovic]

please search the forums for many many many threads/posts about this issue. (creating primary key, etc.)

i never do this, but i think that you might consider actually *using autonumber*, so Access will take care of that business (which is what it is good for), then adding a *separate field* for your LABEL.

when you create a record, look up the last LABEL and update your separate field. if a record/update is cancelled, the autonumber will be "out of order" but it won't matter for your LABEL. you can look up the last LABEL the next time and all will be well. hth.

Hey thanks for you reply.
It is not obligated that the word 'Label' is put in front of the number, the number by himself is also OK.
But with what code do I have to number it? Maybe I have to count te records in the table and adding 1 to it to make a new one. Do you have VBA code for that?

regards

Ugurovic

 

[wazz]

you could count the records (DCount) or get the highest (maximum) value by:

SELECT Max(tblYourTable.YourField) AS MaxOfYourField
FROM tblYourTable;

- set this to a variable and add one.

 

[Rabbie]

Rather than using Autonumber you could use the DMAx function(see Access help for more info) to find the highest value currently stored in your table and then add 1 to it for the next one