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.
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. - 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
-
- Sample Function Call:
- 'Sequentially Number the [Seq_ID] Field in the Orders Table, prefixed with 'ORD'
-
Call fMakeSequential("Orders", "Seq_ID", "ORD")
-
- 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
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.
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.
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.
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.
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. - 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
-
- Sample Function Call:
- 'Sequentially Number the [Seq_ID] Field in the Orders Table, prefixed with 'ORD'
-
Call fMakeSequential("Orders", "Seq_ID", "ORD")
-
- 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
Great stuff, as always.
No further explanation required. It works flawlessly.
Thank you so much for, once again, saving the day.
You are very welcome, JayF.
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
...
|
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...
|
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...
|
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...
|
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...
|
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"
...
|
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...
|
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)...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |