Automatically Change Number In Field

[Scofield67]

Guys/Gals



In a field called PSSheets I want the number to be update every new entry, if I use the AutoNumber feature it won't let me enter the number I want to start with, which is 1421001, then the next number would be 1421002 and so on right up to 1421200 and then the number would change to 1422001.

14/21/001 relates to Year/Week/Entry.

Can anyone assist?

Thanks in advance to any replies.

[June7]

Generating custom unique identifier is common topic. I do something like what you describe. Review: https://www.accessforums.net/forms/a...ing-23329.html

[ItsMe]

You can start the autonumber at whatever number you want by using an append query.

Create a copy of your original table, without data.

Change Autonumber to Long Integer

Type the number you want to use as a starting point in the new table you created.

Run a compact and repair

Append the record(s) in the new table to the original table.

So long as the new number you typed is not being used in the original table and it is a larger number than any existing records, you are good to go. The only thing is, sometimes the Autonumber field may not give you true sequential numbering. If you can not have a missing number in your sequence, you need to consider another approach.

[Scofield67]

Fellas unless you can guide me through it I'm going to struggle. I am a complete Novice.

Thanks though for your replies.

[June7]

What you want requires code. I referenced a link that offers an example. Adapt it to your database. In your case, you need to use date manipulation functions to pull the current year and week from the current date. The VBA procedure will pull up the latest ID, parse it to its component parts, compare to current year/week to determine if need to restart sequence or increment the sequence, then concatenate the sequence with the current year/week parts.