Auto Increment Code

[winpooh5]

Happy 2005 to everyone. I am trying to get my alphanumeric ID field to increment automatically whenever I click on a duplicate record button. For eg, duplicated records should have IDs of "New1", "New2", "New3", etc. to differentiate them from the original IDs (which can be any alphanumeric characters).

Previous posts have usually suggested the DMax function but combining it with "New" has been a problem for me. My duplicate button works fine, but I hope someone can help me with the code for the autoincrement ID.

 

[winpooh5]

Hi again. Is there anyone who can help? Surely this can't be that tough... I just don't know how to make use of the DMax function in this situation... any pointers will be much appreciated.

Or if there is a better alternative, please share it with me.

 

[Mile-O]

Can you detail it more?

It's not clear on whether you have some text and for each duplicate you are incrementing a number (starting from one) or the duplicates are only numbered leaving the original unnumbered. And why, anyway, would you want to do this?

 

[winpooh5]

This ID field is actually a reference ID for project quotations. Each user may have a different reference system, eg KJP001-JH-0105, and since the company wants to retain this system, I have to give them what they want.

Since any user can duplicate any project, I have no way of telling which reference system will be used, hence I want to have a general reference ID such as New 1, New 2, New3, etc. which they can change later (This field is also the PK in the Projects table, that's why I can't retain the existing reference ID in the duplicate project).

So, in summary, all reference IDs (exisitng and duplicates) will have both text and numbers. The ref ID for the first duplicate will be New 1, second duplicate New 2, third duplicate New 3 (which is totally different from the existing ID). The best analogy I can think of is when you add new folders on your desktop, they will be named New Folder 1, New Folder 2, New Folder 3, etc. (which may be totally different names to the existing folders). Pls let me know if you need further clarifications.

 

[sonny]

Ok, so they dup a record....
How does the user know which value to plug into that field(the Id field)?
Does that field have an input mask?
Why couldnt you delete the contents of that field after the dup takes place and force the user to enter the new record ID before saving?

 

[winpooh5]

Actually, I never thought of prompting the user to enter a new ID for each duplicated record.... can you please help me with that? This is still only my first database...

(Regarding the other questions, each user knows the values to type into the ID field as they have been doing that on paper for years. The input mask suggestion sounds like a good idea - thanks - but I'll see if they have any objections first).