One additional question though. This method will number sequntially for all my already existing numbers.
So using my example above I would get the following:
And then when a different three digit is used it would be:
I would want the 121 to start with 1 as well. So it would be:
In the link provided, it mentioned a table for the first available number, but only using a single value, would that work if I were to list the my 3 digit codes and then link it to my query?
Also I think i am going to need some assistance in writing this out. I've tried to do it but I am not doing something right, it keeps dissapearing from the query.
My Tables are called: CDM_20120308 which contains the information I want to use, the field is "Chg Cat", I did create another table called "Start Numbers" which has 2 fields in it: "ChgCat" and "NumberField". Chg Cat contains the 3 digit number that would be in the other table and NumberField contains a 1 as the starting point for each.
You generally wouldn't use that in a query. You'd have it somewhere in the form where the user is entering new records, to assign the new number. If you just want to display a number in a query, you'd use DCount() or a subquery. What exactly are you trying to accomplish?
Here is AC 2000 code that can be used as a model to do this conversion. The 3-digit codes are initially put in a temporary table
to ensure that they are in the correct order (ascending), and a field is added to hold the new code. You can run this from the
module in the attached file.
Dim rst As Recordset
Dim recCount As Long, index As Long
Dim seqNo As Integer
Dim saveValue As String, tempText As String
Dim sqlText As String
sqlText = "SELECT Table1.Code1, 0 AS Code2 INTO TempTbl "
sqlText = sqlText & "FROM Table1 "
sqlText = sqlText & "ORDER BY Table1.Code1;"
Set rst = CurrentDb.OpenRecordset("TempTbl", dbOpenTable)
saveValue = rst!Code1
recCount = rst.RecordCount
For index = 1 To recCount
seqNo = 1
Do While rst!Code1 = saveValue
tempText = "00000" & CStr(seqNo)
rst!Code2 = rst!Code1 & Right(tempText, 6)
seqNo = seqNo + 1
If rst.EOF Then Exit For
saveValue = rst!Code1
Set rst = Nothing