I have a databse which has a field called site and I made the site field into a pop up menu with a custom value list. THe value list is as follows:
Underneath I have a field called ID. What I want to do is automatically fill in the ID field when I choose a specific site. For instance if it is my first record and I choose "Baltimore" I automatically want my ID field to be filled out with "001" and then the next time I do another record from "Baltimore" I want it to fill the ID as 002 and so forth (Note: I want the format to be 00x going from 001 to 999). To make things even more complicated, I want to do the same for the other sites, so the ID for Ohio, New York, and Washington all start with 001 and move on. So at the end of the day i might have a record list of these ID's
New York 001
New York 003
.....and so forth........Also I need to make sure that the ID's don't repeat within a site. Is this all possible? This is very complicated and wasn't sure if it is possible. Can someone tell me how this can be done. Thank you.