Retroactive numbering system update query.

[JayF]

Hi all,

ADezii very kindly helped me over a hurdle with a clever piece of code to generate separate numbering systems in the same field. It can be found here.

The centre piece of the code is:

strLastSeq = DMax("[Product Ref]", "tblProducts", "[Product Ref] Like 'PRD*'")
strNextSeq = Left$(strLastSeq, 3) & Format$(Val(Mid$(strLastSeq, 5)) + 1, "000000")

I've adapted the code to use on tables that don't require two series of numbers: contacts (CON000000), products (PRD000000), etc. and it works fine. I'm now trying to build an update query that I could use to retroactively add these numbers to newly created fields in their respective tables, but can't get my head around it. These tables are currently being used in a live environment. Seeding the table with the first value will be easy enough, as would exporting it to Excel and doing it there, but that seems to invariably cause problems and time wastage and I'll be working on someone else machine and live data.

The best I am able to come up with is DCount() on the end of a "PRD00000" string, which obviously poses a problem once I get past counting records on my fingers (double digits).

If you get a minute in your busy schedule of altruistic lifesaving, would you mind pointing me in the right direction of how to use the above code within SQL?

As always, it would be very much appreciated.

[NeoPa]

First off Jay, your code is not as suggested and will lead to problems. The Mid() parameter should normally be one greater than the parameter to Left(). You'll find this to be the case in ADezii's suggested code certainly.

Moving on, an UPDATE query will not do what you need I'm afraid. You'll need to process through the records using VBA and setting the value for each record individually. You can build intelligence into that routine to avoid DMax calls for each record, but that may not be worth the effort if it's a one-off process anyway.

[JayF]

Thanks for pointing that out NeoPa. I see where I was going wrong; I was leaving an unchangeable 0 in the middle. Lesson learned.

I'm struggling to get my head around how to loop through the field. Loops are something I've not needed thus far in my VBA learning journey. I've wasted the best part of this afternoon trying to figure this out for myself, but I can't, and strangely, I can't find any examples online of people doing this (with a string at the start). Could I trouble you to point me in the right direction? I give up.

Thanks again.

[ADezii]

I am actually a little confused as to your request. As I interpret it, if you have a Products Table with 25 Records, you wish to Update an ID Field with the following Values: PRD00000 to PRD00025. Is this correct? If it is, this can easily be resolved in VBA.

[JayF]

Hi ADezii. You have understood correctly.

I would like to sequentially number a new [Ref] field in a table of existing live data. The format would simply be PRD000001, ...2, ...3, etc.

No dual formats (as per your last code) apply.

Easy for you maybe, but I am a mere semi-VBA literate mortal. :)

Thank you.

[ADezii]

I created the following simple, portable, sequential numbering Code that will do exactly as you have requested. Simply Pass to the fMakeSequential () Function 3 Arguments: your Table Name, Field Name to contain the Sequential Numbering, and a 3-Character Prefix for the Sequential Field. I based my Demo on the Orders Table (831 Records) of the Northwind Sample Database. I created a Text Field named [Seq_ID] in this Table, and I used a Prefix of 'ORD' for no special reason. When the Code completes the [Seq_ID] Field will contain Sequential Values of ORD00001 to ORD00831. The Function Code as well as a Sample Call are illustrated for your convenience. Any questions, please feel free to ask.

1. Function Definition:
   

   Public Function fMakeSequential(strTableName As String, strFieldName As String, strPrefix As String)
   Dim MyDB As DAO.Database
   Dim rstSeq As DAO.Recordset
   Dim lngCtr As Long
   
   Set MyDB = CurrentDb
   Set rstSeq = MyDB.OpenRecordset(strTableName, dbOpenDynaset)
   
   With rstSeq
     Do While Not .EOF
       lngCtr = lngCtr + 1
         .Edit
           .Fields(strFieldName) = strPrefix & Format$(lngCtr, "00000")
         .Update
       .MoveNext
     Loop
   End With
   
   rstSeq.Close
   Set rstSeq = Nothing
   End Function

2. Sample Function Call:
   

   'Sequentially Number the [Seq_ID] Field in the Orders Table, prefixed with 'ORD'
   Call fMakeSequential("Orders", "Seq_ID", "ORD")

3. Last 20 Values of the [Seq_ID] Field in Orders Table:
   

   ORD00812
   ORD00813
   ORD00814
   ORD00815
   ORD00816
   ORD00817
   ORD00818
   ORD00819
   ORD00820
   ORD00821
   ORD00822
   ORD00823
   ORD00824
   ORD00825
   ORD00826
   ORD00827
   ORD00828
   ORD00829
   ORD00830
   ORD00831

[JayF]

Great stuff, as always.

No further explanation required. It works flawlessly.

Thank you so much for, once again, saving the day.

[ADezii]

You are very welcome, JayF.