Create new number for each record

[dlab85]

Hi,
I have an autonumber (without using Autonumber field) and redundancy question. I have a table with the following fields, and no PK:
tblSamples:
-Accession (FK from another table) (example of a record is M12-12345)
-RowID


-SampleID
-Sample (example of a record in here is M11-12345-1, with the last digit increasing incrementally dependant on # of samples, could be -100)
-Additional
-Test Type
....etc.

The Issue: The field SampleID is an autonumber field that I use to sort my records in queries, etc, because of leading zeros in the Sample throwing off the order (i.e. M12-12345-10 followed by M12-12345-1). I have tried to alleviate this problem, but nothing seems to place the zero where I need it, i.e. M12-12345-01) Now, I need the field RowID to generate a sequential number (just like Autonumber does) with every Sample that is entered into my LogIn Form. BUT, I want to be able to change that number (it is like a placeholder). Here's why: I use a query to pull a certain set of records from the tblSamples (from within a Date Range) into a Form in Datasheet view. The Form is sorted with my autonumber field SampleID, EXCEPT there are a few instances where some samples need to be moved around in the form because they are high priority cases. For example, they are samples with RowID 20-24, but I need them to go where samples with RowID 11-16 are located. I use this form as a basis for a Report that another person receives as they are viewing results for the samples, and I need this report to have the shifted samples in the appropriate order. It doesn't matter that the RowID is switched, because it is not a field that we search for records in. It is simply a placeholding field. Any help would be greatly appreciated! Thanks everyone!

David

[June7]

What have you attempted in formatting the autonumber? Something like:

"M12-12345-" & Format(SampleID, "00000")

Need criteria for the sort. Can either manually number records for the desired order or assign a PriorityCode. Can create every record with Low as default and then edit to set specific records as High. Sort By PriorityCode then SampleID.

[dlab85]

The formatting I had issues with was actually in the field Sample (not sampleID), I was just using the SampleID field as my backup sorting field; so I will re-examine my formatting on the Sample field to see if what you replied works.

However, my bigger issue comes up with the second portion of your reply. I would hate to have to manually assign a number to the records as we log them in (sometimes there are 25+ samples, it becomes tedious), when I know I could write some code that would look up the last RowID number used, and start numbering any new records sequentially from there (i.e. RowID was 101, and then I added five more samples, making those new samples RowID 102, 103, 104, 105, 106). I like your idea of creating a low priority versus high priority, my only concern is sometimes those high priority cases aren't at the very top of the sorted list, they may be a few samples down (i.e. starting in spot 8 rather than spot 1) because of partial testing carry-over. I know this is nitpicking down to a very specific detail in order for me to obtain ideal "perfection", but I feel like I am so close, I just need that extra brain power from the geniuses here! Any thoughts June7? Thanks for the suggestions so far.

[June7]

Programmatically generating a unique ID is a common topic on forum. Search forum or google.

One method is to set the DefaultValue property of the field in record BeforeUpdate event. In your case the DefaultValue will be set as an increment of the current value.

Review http://access.mvps.org/access/forms/frm0012.htm

[dlab85]

Thanks for the response! I THINK I figured it out, and it might be the same answer you just gave me June7. ANYONE, please correct me if you think that I set this up wrong and you foresee problems in the future, but, on the BeforeUpdate event for my form (which is actually a subform on top of another form, but it still does the same thing), I created a SetValue (not sure if this is similar to DefaultValue, it seems like it would be similar) to DMax("[Field]", "[table]")+1. It seems to do the trick. I wish I had more access experience and could have reached this conclusion in less than the 4 hours i spent racking my brain

[June7]

SetValue - so you are using macro? That is not setting DefaultValue property. It is actually populating the field. Which is another method to get the same outcome.

[dlab85]

I had tried to set the DefaultValue property of the field on my form, but it wouldn't let me continue adding records. I would enter the first sample for that accession and it would populate the appropriate RowID, but I would try to arrow down to the next sample, and it would simply increase the RowID, but had me locked into the first sample. Not sure what that was all about. Either way, it appears to be functioning the way I need it to, so hopefully I don't find any kinks. Thanks for the help.