Create Sort Order 4

[SMHSleepy]

Hey there all, I have a problem and hope there's an easy solution.
My main table has an autonumber primary index (MainID). I've been using this field to sort my reports because I want them to display in the order that they were entered. However, occasionally I want to insert a record in this table instead of adding it to the end. I already figured I can't mess around with the autonumber so I thought of creating a (SortOrder) field and populating with chronologic numbers. Then every time I add a record, I would assign (SortOrder+1) to the new record. This would work sort of like an autonumber right? Okay, so now if I want to insert a record between say record 9 and 10 of 15 records, I would need to write some code that would change the SortOrder numbers of records 10-15 to 11-16, thus freeing up number 10 to add my new record. Can anyone help me with the code to do this? Or is there and easier way? Thanks!

 

[PHV]

Or is there and easier way?
Why not using a decimal number ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SMHSleepy]

I thought of that also but then what if I try to insert another record in the same place? How do I make sure that decimal number hasn't already been used? Sounds like it would be more tricky with the error checking.

 

[randy700]

How do I make sure that decimal number hasn't already been used?
Well, if you know you need to insert between 2 records, you must have some way of looking at the SortOrder field, including the inserted decimal numbers.


Randy

 

[SMHSleepy]

Okay, say I want to insert a record between 9 and 10. I add a record with SortOrder = 9 + 0.1 which should do the trick. Now what if later on I want to add another record after 9? How do I know if I already have a 9.1 or not? Sure I can always make it 9.05 so that it goes between 9 and 9.1 but I need to know the next number. i.e. I can't just make a generic SortOrder + 0.1 statement. Is there a way to find out the next chronological number of SortOrder and make the new number half way in between?

 

[MajP]

Here is a demo showing several ways to do this.

http://www.4shared.com/document/AqGkNhVS/MajPListSort_1_.html

I like to have a listbox where you can select an item and then hit up and down arrows to move the item in the list. Using a listbox you can move the items all around the list and then when you close the form set the order equal to the index of the listbox.

The other examples uses arrows on the form to change the sort order and recalculate all of the sort orders.

 

[addy]

I did something similar in a database which manages individual documents which are combined into controlled hard copies.

The way I did it was to have a list box which lists the Documents in a particular dossier.

I then select from the list box where I want my new document to appear after, so in your example you would select the record in sort order 9. Then on my 'Insert' button, I have some code which captures the value selected and then does a SQL update statement along the lines of:

UPDATE myTable SET SortOrder = SortOrder + 1 WHERE SortOrder > mySelectedValue

This will update 10 to 11, 11 to 12..etc.

Then do a SQL insert into your table with the relevant values for your record and its sort order will be your selected value (from your list box) + 1, i.e. 10.

On my form, I've also added up and down arrows to shuffle Documents up or down one place and again, I use something similar to update the relevant values.

 

[SMHSleepy]

MajP, great demo as usual. Perhaps a little too much for my needs right now but will keep it handy for future reference. Thanks!

Addy, that's right up my alley except instead of a listbox, I'll just create an "Insert" button to execute the SQL update statement. Will let you know if I run into problems. Thanks!

 

[MajP]

The list box sort is a little involved, but the Form Sort just requires you to Change the sort field name.

Just put an up and down command button in the record and change "JobStepNumber" to your sort field.

It swaps the current record with the next or previous. ( 2 becomes 1 and 1 becomes 2).

Place in module

Public Sub SetInitialSort(SortField, frm)
  Dim rs As DAO.Recordset
  Set rs = frm.Recordset
  Do While Not rs.EOF
    rs.Edit
    rs.Fields(SortField) = rs.AbsolutePosition + 1
    rs.Update
    rs.MoveNext
  Loop
  rs.MoveFirst
End Sub

Public Sub moveSortUp(SortField, frm As Access.Form)
  Dim rsClone As DAO.Recordset
  Dim lngNewSort As Long
  Dim lngOldSort As Long
  Set rsClone = frm.RecordsetClone
  rsClone.AbsolutePosition = frm.Recordset.AbsolutePosition
  If Not (IsNull(rsClone.Fields(SortField)) Or rsClone.AbsolutePosition <= 0) Then
    lngOldSort = rsClone.Fields(SortField)
    lngNewSort = lngOldSort - 1
    rsClone.Edit
    rsClone.Fields(SortField) = lngNewSort
    rsClone.Update
    rsClone.MovePrevious
    rsClone.Edit
    rsClone.Fields(SortField) = lngOldSort
    rsClone.Update
  frm.Requery
  frm.Recordset.FindFirst SortField & " = " & lngNewSort
  End If
End Sub

Public Sub MoveSortDown(SortField, frm As Access.Form)
  Dim rsClone As DAO.Recordset
  Dim lngNewSort As Long
  Dim lngOldSort As Long
  Set rsClone = frm.RecordsetClone
  rsClone.AbsolutePosition = frm.Recordset.AbsolutePosition
  If Not (IsNull(rsClone.Fields(SortField)) Or rsClone.AbsolutePosition >= rsClone.RecordCount - 1) Then
    lngOldSort = rsClone.Fields(SortField)
    lngNewSort = lngOldSort + 1
    rsClone.Edit
    rsClone.Fields(SortField) = lngNewSort
    rsClone.Update
    rsClone.MoveNext
    rsClone.Edit
    rsClone.Fields(SortField) = lngOldSort
    rsClone.Update
  frm.Requery
  frm.Recordset.FindFirst SortField & " = " & lngNewSort
  End If
End Sub

Place on Form and provide you field name

Private Sub Form_Load()
  'Set the sort values based on the query order
  SetInitialSort "JobStepNumber", Me
End Sub

Private Sub cmdDown_Click()
  MoveSortDown "JobStepNumber", Me
End Sub

Private Sub cmdListUp_Click()
 moveSortUp "JobStepNumber", Me
End Sub

 

[SMHSleepy]

Thanks MajP, but I think this only works with existing records. If I wanted to insert a new record, I would have to add it to the end and then use the MoveSortUp command button to move it up to where I wanted it. Not practical for my purposes. The more I think of it, I may be better off using a decimal as suggested by PHV and Randy. Reason being, if I want to insert a record near the beginning of a 50,000 record table, it would probably take a long time to renumber that many records. I'll just use error checking to make sure I haven't already inserted a record between two numbers. Like I said, I'm sure this will help me out in future projects so I'm keeping it handy. Thanks again.