473,327 Members | 1,997 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Retroactive numbering system update query.

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

7 1804
NeoPa
32,556 Expert Mod 16PB
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
JayF
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
8,834 Expert 8TB
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
JayF
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
8,834 Expert 8TB
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
JayF
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
8,834 Expert 8TB
You are very welcome, JayF.
Feb 21 '12 #8

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

Similar topics

2
by: lea | last post by:
i got this error msg while i trying to run the UPDATE query.there is n underlying.. ERROR MSG :An unhandled exception of typ 'System.Data.OleDb.OleDbException' occurred in system.data.dll ...
5
by: Don Seckler | last post by:
I have an update query that runs when a report closes. I have several reports that will need to run the update query with diferent criteria. I'd like to simply make the criteria change in the...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
5
by: Andrew | last post by:
I've got a list box that selects a record on a subform, and in that subform are a few text fiels and a button that runs an update query. How do I have the update query button run and only update...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
7
by: Mark Carlyle via AccessMonster.com | last post by:
I have this update query that I am trying to run. I know the syntax is messed up but do not know how to correct it. Select 'UPDATE', Transactions,'Set = where = ' From "Get Daily Balances" ...
2
by: mike11d11 | last post by:
Our company has a collection system with a list of accounts in it, I connect to this system with access using an ODBC connection. I pull in the account #'s and other fields, then I have to run and...
1
by: RamanS | last post by:
I have these variables: Dim str1Year As String Dim str5Years As String str1Year = "1 Year" str5Years = "5 Years" and this query statement If Me.frmEvergreen.Value = 1 Then
10
by: juster21 | last post by:
I am trying to create an update query but it is failing to make any changes. Below is the code I have... Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)...
1
by: giovannino | last post by:
Dear all, I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !). 1) Given that I'm not a programmer I can't understand why...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.