Custom autogenerated number not using autonumber

LibGuy

New member
Local time
Today, 18:37
Joined
Mar 12, 2009
Messages
3
Hi,

I'm creating a database for entering and retrieving information on in-house publications. I'm looking to create a custom number for each record entered into the database using a form. The number would be based on the selection of a section ID (TM, CR, SL, ...) which would be choosen from a combobox on the form. The number needs to follow this pattern, TM-2009-001, CR-2009-002, TM-2009-003, and so on with the number increasing by 1 for everytime a SectionID choosen. I also need the numbers to revert back to 1 when the year changes. This can be done either automatically or through a "reset" button. My knowledge of expressions and coding is very, very limited and i've been looking online for weeks trying to figure how to do this.Can anyone please help me?
 
And you are not planning on using this as your Primary Key, I hope, but just as another field, correct?


welcometoawf.png
 
That is correct. I need the TM-2009-001 format because it is a standard for the publication.
 
Use an autonumber for your primary key. Use three fields to hold this visible ID since it is comprised of three parts. you can concatenate it for display purposes. you will also need to define a unique index for the three fields to ensure that they are not duplicated.

You don't ever need to reset the year field. Just use Year(Date()) to get the current year when you are building the unique id. you can use the DMax() function to return the highest sequence value for the first two fields. Don't forget to add error handling to ensure that if your code generates a duplicate (possible in a multi-user environment) that you gracefully loop until you get a unique sequence number. Of course if you end up with more than 999 items in a given year, you're in deep do-do so make sure that three digits is sufficient for the sequence number.
 

Users who are viewing this thread

Back
Top Bottom