Results 1 to 5 of 5
  1. #1
    daweir
    Guest

    automatic sort number increment

    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

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automatic sort number increment

    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?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automatic sort number increment

    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

  4. #4
    daweir
    Guest

    Re: automatic sort number increment

    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.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automatic sort number increment

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •