Sort Recordset then add consequtive numbers to a non-key field.

[CutAndPaste]

I'm helping our local Scouts with a Christmas tree order database and pre-delivery, we need to create labels for each tree. I'm using Access 2003

As the orders come in from customers in any order for location, delivery day and tree size, I need to sort the records with a query which sorts by delivery days, then by Tree size, then by ascending Postal Code (qrySortDeliveryOrder). This is fine and no problem.

However, in order to ease tree sorting prior to delivery, each tree needs a label with a unique number assigned, based upon the sort generated by qrySortDeliveryOrder. I've got a number field [treeID] in my order details table ready to have this number inserted.

So once all the orders are in and no more are to be added, I need to run the sort query and then add an incremental number to each record based on the sort order.

I'm guessing that I'll have to loop through the recordset starting at the first record in the sorted query numbering this as 001 and then go through all of the remaining trees as 002, 003 etc, to the last tree.

I'm a little hazy on this and would welcome some pointers to get me going.

Any ideas please?

 

[Steve R.]

Try this. This must be run from your query.

Sub addnumber()
Dim intCounter As Integer
Dim dbs As dao.Database
Dim rms As dao.Recordset
Set dbs = CurrentDb
Set rms = dbs.OpenRecordset("YourQueryName", dbOpenDynaset)
intCounter = 0
rms.MoveFirst
Do Until rms.EOF
    intCounter = intCounter + 1
    rms.edit
    rms![intnumber] = intCounter
    rms.Update
    Debug.Print intCounter
    rms.MoveNext
Loop

End Sub

 

[RuralGuy]

Here's another idea that may be useful. http://www.lebans.com/rownumber.htm