VBA Repeatable AutoNumbering system *NEED TO HAVE DUPLICATES AT TIMES*

[NickWren]

So I have an autonumbering field that just keeps on growing, this field makes an id for our samples once I put it into a calculated field:

[Facility Abbreviation] & "-" & [ID] & "-" & [Test Abbreviation]
PL-0001-NT
PL-0002-NT
ect...

Now if there is a problem with this sample later in our process we still want to store all the data for the first results, however the new sample ID needs to be similar for a reference point (add an "R" after the "NT" ex. "PL-0001-NTR") for the new data entry so we can keep both but know that the two samples are related however the sample was repeated.

PL-0001-NT
PL-0001-NTR
PL-0002-NT
ect...

I dont know if it would be easier if I just have a spot in the form that it generates the above but if we need to we can go into it and change a number, however I dont really know how to approach it either way.

[rpeare]

Don't store this value at all.
Instead add another field that stores a 1 for the original test, 2 for the retest (assuming there are only ever 2 items) or O for original R for retest or whatever you want

Then you can concantenate the sample number any time you need it as a series of four fields rather than storing it.

[NTC]

You really should have an actual autonumber field type generating a unique ID for each record and serving as the actual key field in your design. All this gobbledygook is just display for the human. What you are encountering now is typical - changes....... and so that is why you don't really want this field to be the actual key field even though the humans think it is......

[rpeare]

Oh I hadn't thought he was using this as a primary key, just that he needed a sequential numbering system that didn't leave gaps. If you intend to use this field as your primary key you definitely do not want to do that, as NTC said you want to use an autonumber field as your actual unique key/primary key field.

[NickWren]

I do have just plan ID's as the primary key, but since the ID is the primary key and I need the ID to duplicate for the samples that we repeat; this system doesnt work really. I just want to be able to type in the ID (Ex. 0001) and both the PL-0001-NT and PL-0001-NTR (repeated trial) come up.

[rpeare]

no, you can't do it that way then if ID is your primary key you can't duplicate it period. There are a bazillion ways to get the result you want though.

Here's one example

NickWren.zip

I left a lot of stuff in there so you could see how it works (like using queries instead of programmatically building the append/update queries etc.

[NickWren]

rpeare, I don't even know how to thank you enough! I dont know if it is possible if I can get some contact information to ask you how you did a couple of things, because this is beyond perfect and more than my expectation for what I was even going to do.

[rpeare]

If it relates to this database you can ask me here, if it's for something else you can pm me.

Edit: I just noticed that when you click on the list box to bring up a particular sample it blanks out the NTR field which it shouldn't do, you can fix that pretty easily though by putting something in the cleardata function to repopulate it.