Assigning reference numbers to records
Can someone help walk me through how to do the following?
I'm setting up a database that will be filled out via a form completed by users. They'll have to select a category from a dropdown in a certain field (let's call it 'category') - I want to be able to automatically assign a reference number to each record using the following format:
C-AAA-XXX
The "C" will appear at the start of every reference number.
The "AAA" will be a three-letter abbreviation related to the category selected in the Category field. I've already made a field ("Abbreviation") that auto-populates with the abbreviation based on the selection in the Category field.
The "XXX" should be a three-digit number, starting with 001 for the first record created per Category. So for each category, there will be a -001, -002, etc.
I've tried everything I can think of, and am failing miserably. It's the 'unique number' part that's tripping me up, obviously. Finally occurred to me that someone here can probably tell me how to do it in 30 seconds. So... help?
------------------------------
Michelle L
------------------------------
