By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,655 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,655 IT Pros & Developers. It's quick & easy.

Retroactive numbering system update query.

P: 45
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:

Expand|Select|Wrap|Line Numbers
  1. strLastSeq = DMax("[Product Ref]", "tblProducts", "[Product Ref] Like 'PRD*'")
  2. 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.
Feb 17 '12 #1

✓ answered by 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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fMakeSequential(strTableName As String, strFieldName As String, strPrefix As String)
    2. Dim MyDB As DAO.Database
    3. Dim rstSeq As DAO.Recordset
    4. Dim lngCtr As Long
    5.  
    6. Set MyDB = CurrentDb
    7. Set rstSeq = MyDB.OpenRecordset(strTableName, dbOpenDynaset)
    8.  
    9. With rstSeq
    10.   Do While Not .EOF
    11.     lngCtr = lngCtr + 1
    12.       .Edit
    13.         .Fields(strFieldName) = strPrefix & Format$(lngCtr, "00000")
    14.       .Update
    15.     .MoveNext
    16.   Loop
    17. End With
    18.  
    19. rstSeq.Close
    20. Set rstSeq = Nothing
    21. End Function
    22.  
  2. Sample Function Call:
    Expand|Select|Wrap|Line Numbers
    1. 'Sequentially Number the [Seq_ID] Field in the Orders Table, prefixed with 'ORD'
    2. Call fMakeSequential("Orders", "Seq_ID", "ORD")
    3.  
  3. Last 20 Values of the [Seq_ID] Field in Orders Table:
    Expand|Select|Wrap|Line Numbers
    1. ORD00812
    2. ORD00813
    3. ORD00814
    4. ORD00815
    5. ORD00816
    6. ORD00817
    7. ORD00818
    8. ORD00819
    9. ORD00820
    10. ORD00821
    11. ORD00822
    12. ORD00823
    13. ORD00824
    14. ORD00825
    15. ORD00826
    16. ORD00827
    17. ORD00828
    18. ORD00829
    19. ORD00830
    20. ORD00831

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,487
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.
Feb 20 '12 #2

P: 45
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.
Feb 21 '12 #3

ADezii
Expert 5K+
P: 8,634
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.
Feb 21 '12 #4

P: 45
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.
Feb 21 '12 #5

ADezii
Expert 5K+
P: 8,634
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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fMakeSequential(strTableName As String, strFieldName As String, strPrefix As String)
    2. Dim MyDB As DAO.Database
    3. Dim rstSeq As DAO.Recordset
    4. Dim lngCtr As Long
    5.  
    6. Set MyDB = CurrentDb
    7. Set rstSeq = MyDB.OpenRecordset(strTableName, dbOpenDynaset)
    8.  
    9. With rstSeq
    10.   Do While Not .EOF
    11.     lngCtr = lngCtr + 1
    12.       .Edit
    13.         .Fields(strFieldName) = strPrefix & Format$(lngCtr, "00000")
    14.       .Update
    15.     .MoveNext
    16.   Loop
    17. End With
    18.  
    19. rstSeq.Close
    20. Set rstSeq = Nothing
    21. End Function
    22.  
  2. Sample Function Call:
    Expand|Select|Wrap|Line Numbers
    1. 'Sequentially Number the [Seq_ID] Field in the Orders Table, prefixed with 'ORD'
    2. Call fMakeSequential("Orders", "Seq_ID", "ORD")
    3.  
  3. Last 20 Values of the [Seq_ID] Field in Orders Table:
    Expand|Select|Wrap|Line Numbers
    1. ORD00812
    2. ORD00813
    3. ORD00814
    4. ORD00815
    5. ORD00816
    6. ORD00817
    7. ORD00818
    8. ORD00819
    9. ORD00820
    10. ORD00821
    11. ORD00822
    12. ORD00823
    13. ORD00824
    14. ORD00825
    15. ORD00826
    16. ORD00827
    17. ORD00828
    18. ORD00829
    19. ORD00830
    20. ORD00831
Feb 21 '12 #6

P: 45
Great stuff, as always.

No further explanation required. It works flawlessly.

Thank you so much for, once again, saving the day.
Feb 21 '12 #7

ADezii
Expert 5K+
P: 8,634
You are very welcome, JayF.
Feb 21 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.