I decided to stay up a bit longer and try to solve this for you. Perform the following steps on a
copy of your database:
1.) Add two new fields to tblOrders:
NewItemNo Text (4)
RecNum Autonumber
The RecNum field is required to add some order to what is otherwise an unordered bucket of
records.
2.) Create a new query with the following SQL statement:
SELECT CallNo, ItemNo, NewItemNo, RecNum
FROM tblORDERS
ORDER BY CallNo, ItemNo, RecNum;
Name this new query: qryOrders
3.) If desired, for testing purposes, create an update query which will allow you to nullify the
NewItemNo field
UPDATE tblORDERS SET tblORDERS.NewItemNo = Null;
Name this new query: qupdNullifyNewItemNo (or whatever you want to name it).
4.) Create a new module. Name it: basReorderItemNo (or whatever you want, as long as you do not
name it ItemNo, which is the name of the subroutine contained in this module. Paste the following
code into this new module. Make sure to set a reference to the "Microsoft DAO 3.6 Object
Library".
Option Compare Database
Option Explicit
' This code requires a reference to the "Microsoft DAO 3.6 Object Library".
Sub ItemNo()
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strCallNo As String
Dim strItemNo As String
Dim strNewItemNo As String
Dim lngNewItemNo As Long
Dim lngRecords As Long
Dim lngCounter As Long
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryOrders", dbOpenDynaset)
rs.MoveLast ' Note: MoveLast is req. to get an accurate recordcount
rs.MoveFirst
lngRecords = rs.RecordCount
lngCounter = 1
Do Until rs.EOF
strCallNo = rs("CallNo")
lngNewItemNo = 0
' Execute this loop for all CallNo that are the same
Do Until strCallNo <> rs("CallNo")
lngNewItemNo = lngNewItemNo + 1
rs.Edit
rs("NewItemNo") = Format(CStr(lngNewItemNo), "0000")
rs.Update
If lngCounter < lngRecords Then
rs.MoveNext
lngCounter = lngCounter + 1
Else
GoTo EndProc
End If
Loop
Loop
EndProc:
' Inform user of success if we get this far
MsgBox "All ItemNo values have been sucessfully updated", _
vbInformation, "Success..."
ExitProc:
' Cleanup
On Error Resume Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in ItemNo Subroutine..."
Resume ExitProc
End Sub
5.) Then click on Debug > Compile. Correct any compile errors which might result due to word
wrap in this posting.
6.) Position the cursur anywhere within this subroutine. Then press the F5 button. You should
see a message indicating success.
7.) Open tblOrders and inspect the results. If everything worked okay, you can delete the
original ItemNo field and the RecNum field. If you'd like to test it again, then run the optional
update query produced in step 3 first.
I will send a zipped copy of the sample database to your e-mail address as a separate message.
Tom
___________________________________________
The fourth and fifth records in the initial set of data indicate a CallNo = 1P02. However, the
fourth and fifth records of the updated data show a new CallNo = 1P03. I thought your initial
question only dealt with updating the ItemNo values. Is this a typo?
Sorry that I didn't take a look at this earlier, but I have reached the end of my weekend. I live
in Bellevue, WA., so I think you are 16 (?) hours ahead of me. I can work on this some more
towards the end of this week, but if someone else wishes to jump in right now, please do so.
Tom
___________________________________________
Tom;
Thanks for the patience. I am working in Korea therefore the time difference.
The name of teh table is tblORDERS. The table is set up as shown below;
CallNo ItemNo
1P01 0001
1P01 0001A
1P02 0001
1P02 0002
1P02 0002
The ItemNo could repeat if there was a change in the ModelNo.
Since the ModelNo is mute at this time I left it out of the equation.
I need to update the table as you have in the second part of your response;
1P01 0001
1P01 0002
1P02 0001
1P03 0001
1P03 0002
Thanks.
___________________________________________
If I understand you correctly, your data currently looks something like this:
CallNo ItemNo
1 1
1 2
1 3
2 4
2 5
2 6
2 7
3 8
3 9
4 10
5 11
5 12
and you want to renumber the ItemNo values, so that the data looks something like this:
CallNo ItemNo
1 1
1 2
1 3
2 1
2 2
2 3
2 4
3 1
3 2
4 1
5 1
5 2
Is this correct? If not, please show an example of how your data currently looks, and what you
want it to end up looking like. Also, please provide the name of your table.
Tom
___________________________________________
Tom;
Thanks for the quick response. It appears I was not clear on defining
the task. I should have stated "1 through ~" instead of "to ~". The
table has several orders with several Call Numbers. It was discovered
later that the ItemNo(s) need to be sequential and cannot repeat. I am
looking for a way to loop through the table beginning with the first
CallNo and assign a number 1. The next record under that CallNo should
be 2 .... When it comes to a new CallNo it starts over with 1. Thanks
for the assist.
___________________________________________
Have you tried something like this (substitute tblCalls with the name of your table):
UPDATE tblCalls
SET tblCalls.[ITEM NO] = "~"
WHERE tblCalls.[ITEM NO]="1";
Notes:
Try this on a copy of your table first (or on a copy of the entire database).
The [ITEM NO] field must be defined as a text datatype. You cannot update a numeric datatype to
the "~" character.
Tom
_________________________________________
I have a table that has Fields [CALL NO] and [ITEM NO]. The Item No is
used to show the number of Items assigned to the Call No. I would
like to run a query that will update the first Item No starting with 1
to ~. When it comes to a different Call No it will start over again at
1. I was thinking this was an IIF would accomplish the task, however
it does not appear to work as planned. TIA
