automatic sort number increment

Topic

New Reply

I have a table with 3000+ records grouped into 30+ areas. Each area has a “Sort” field with a delivery order number. I am trying to write a routine to take a sorted subtable and insert a new sort number sequenced by 10 (ie: 10, 20, 30, etc.) This gives room for inserting directions, new stops, etc. until the “in-between” numbers are used and then the subtable must be renumbered. I have tried to use the row number (rownumber * 10) but cannot get the proper commands to get the row number out. I am developing in 97 and 2000, but the application will be most widely used in 97 at this time. Thanks for any/all help. Dave

Reply | Quote

Replies

Do you want to do this in code or in a query?
Do you want to do this just once or at certain times to reset/recreate these values to restore the gap of 10?

Reply | Quote

This should be a macro or module. The users should just be able to click on a button to achieve the new number sequence. Thanks for your input.

Reply | Quote

Try the following (DAO so it should work on Access 97, if you use ADO on Access 200 may need some mods). I assume your sort field is not unique and that there are records in the table.

Private Function ResetSortSpace(szArea as string) as long
On Error Goto Err_ResetSortSpace
dim DB as database, RS as recordset, s as string, lCount as long
const lIncrement = 10
set DB = Currentdb
DB.BeginTrans
s = “SELECT sortField FROM sourceTable WHERE (areaField = ‘” & szArea & “‘) ORDER BY sortField”)
set RS = DB.Openrecordset(s)
RS.MoveFirst
lCount = lIncrement
do
RS.Edit
RS!sortField = lCount
RS.Update
RS.MoveNext
lCount = lCount ‘plus’ lIncrement ‘the editor is not accepting a ‘plus’ sysmbol!!!!
loop until RS.EOF
RS.Close
DB.CommitTrans

ResetSortSpace = 0
exit function

Err_ResetSortSpace:
DB.Rollback

ResetSortSpace = 1 ‘Or other values if you want to trap different errors
exit function

end function

Reply | Quote

dim db as database
dim strSQL as string
set db = currentdb()
strSQL = “UPDATE tblYourTable SET fldRownumber = fldRownumber * 10

db.execute strSQL, dbFailOnError

set db = Nothing

Reply | Quote