473,836 Members | 2,139 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Retroactive numbering system update query.

45 New Member
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
7 1826
32,584 Recognized Expert Moderator MVP
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
45 New Member
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
8,834 Recognized Expert Expert
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
45 New Member
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
8,834 Recognized Expert Expert
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
    6. Set MyDB = CurrentDb
    7. Set rstSeq = MyDB.OpenRecordset(strTableName, dbOpenDynaset)
    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
    19. rstSeq.Close
    20. Set rstSeq = Nothing
    21. End Function
  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. 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
45 New Member
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
8,834 Recognized Expert Expert
You are very welcome, JayF.
Feb 21 '12 #8

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

Similar topics

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 ---------------------------------------------------------------------------------------------------------------------------------------------------- CODE Dim strSQL As String
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 report vba instead of making different queries. Here's my query sql: UPDATE Draw SET Draw.Billed = Yes WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)="Hudson"));
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 wouldn't work (nothing changed): UPDATE tblManuals SET tblManuals.PARTNUM = Trim(); Would someone please tell me how to do an update query that will trim the spaces?
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 the record that is selected in the list box? The data updates right, but I can't get the update query to do anything but update all of the records. Thanks, Andrew
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 AddressDescription of Entity 456 = AddressDescription of Entity_ID 123 Address1 of Entity 456 = Address1 of Entity_ID 123 City of Entity 456 = City of Entity_ID 123
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" Transactions = name of the table I want to update balance = name of the field i want to update daily balance= name of the query result that I want to move to the table
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 update query to update other fields from another table in our collection system. I am able to update every field except for one and it gives me unknown error -7776, then the help with says error 3000. It won't update this one field for about...
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
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) Handles btnSave.Click Dim strSqlEditRecord As String Dim myCmd As OleDb.OleDbCommand myCmd = Nothing strSqlEditRecord = "UPDATE tblMain SET Per_Hour_Quota= '" & txtQuota.Text & "' WHERE EnteredRecord= '" &...
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 numbering doesn't start always, running more times the update query, from the same starting parameter assigned (1000000000). It seems to me that it takes memory last number calculated and running prox update it starts from last table row updated. I need...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.