Autonumber help (1 Viewer)

[maverick235]

Hi,

I'd like to make updates to our existing database.
Currently:

• Multiple users have their front end that they use to add or edit records. Each record has a unique ID that is labelled as: ABC-MMDDYY-XX.
  • ABC is constant (does not change).
  • MMDDYY - is the date the record is created
  • XX - is essentially the counter; ie. 1st record for a particular day would be -01, and subsequently -02 etc.
  • This entire record number is also a Unique ID
• when creating new records, users type out the Unique ID (which is obtained from a separate spreadsheet)

Updates/Help Needed:

• Instead of using a separate spreadsheet to document/create new record numbers, we'd like the ability to have Access autonumber records when creating a "new" record.
• I just have ZERO clue as to how about get this going.
• Essentially, when the User hits "New" button on the form and selects a particular Project, the ABC# should generate and populate according to the rules stated above.
  • Note that if a different Project is selected, there is no ABC# and another Unique ID that the user has to input (there's no automation on this one). Therefore, lets say there's 2 Projects (Project A and Project B). If the user hits "new" and selects Project A, then generate an Autonumber based on previous mentioned rules. If the user selects Project B, then have the ability to type in the unique ID and not autogenerate the number.

Hope that makes sense and look forward to any help/suggestions.

Thanks

 

[theDBguy]

Hi. When creating your own "autonumber," it's advisable to separate the "number" part from everything else that may define the record as unique. For example, since "ABC" is a constant, you don't need to have it in the table because you can always just add it back in. Recommend having a separate date field for the MMDDYY part. So, the XX part would be a separate field (number), which would make it easier to increment, if by itself. Is the XX part the same as the "Unique ID" you mentioned coming from Excel?

 

[CJ_London]

Agree with DBG

the problem with what you want to do is you are limiting your app to 99 records per day, That may be sufficient for now - but down the line?

Essentially, when the User hits "New" button on the form and selects a particular Project, the ABC# should generate and populate according to the rules stated above.

• Note that if a different Project is selected, there is no ABC# and another Unique ID that the user has to input (there's no automation on this one). Therefore, lets say there's 2 Projects (Project A and Project B). If the user hits "new" and selects Project A, then generate an Autonumber based on previous mentioned rules. If the user selects Project B, then have the ability to type in the unique ID and not autogenerate the number.

• this does not make sense to me - can you provide some examples of what you mean