I am working in MS Access 2000 and trying to have the computer continue an ID pattern I have. Right now I have a table with a field "Customer Program ID". There are currently 5 of them...CP001, CP002, CP003, CP004 and CP005. When the user selects new record I would like it to automatically fill in the next available ID...however, I am new to code and I am not sure how to do this. Any help you can provide would be appreciated!
*Michelle*
MSUMichelle 0 Newbie Poster
mnemtsas 3 Posting Whiz in Training
I am working in MS Access 2000 and trying to have the computer continue an ID pattern I have. Right now I have a table with a field "Customer Program ID". There are currently 5 of them...CP001, CP002, CP003, CP004 and CP005. When the user selects new record I would like it to automatically fill in the next available ID...however, I am new to code and I am not sure how to do this. Any help you can provide would be appreciated!
*Michelle*
There's a couple of ways I can think to do this.
1.
I'd have a time stamp field containing the datetime a record was added in the table to enable you to select the most recently added record. You can then use some VBA to parse out the most recent ID and create a new one. Trouble with this method is that if you delete the most recent record you will repeat a number.
2. The other way is to have a separate table containing a single INTEGER and simply increment the integer when you add a record to the main table. You can use the value in the table to cat together your unique ID. The strength of this method is that you wont repeat a number if the most recent record happens to be deleted. I'm sure this could be done with straight SQL but I'd do it in VBA, it's pretty trivial.
Cheers,
MSUMichelle 0 Newbie Poster
I apologize, but I am not very good with VBA code. The second option sounds best - would you mind walking me through it a little more? I appreciate it!
*Michelle* ;)
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.