none
Looking for a method to let users change the order of records on a form. RRS feed

  • Question

  • I'm looking for a way to give users the ability to change the order of records displayed on a form. The recordsource for the form is a local table of customers and I want the user to be able to manipulate the order they are listed. Here's one thing I tried, but it wasn't very user friendly. I created a Sequence field in the table and included ORDER BY that field in the form's recordsource, and let users change the Sequence value. But that is extremely awkward for the user. For example, if the form started with the following records displayed:

    Jones     1
    Smith     2
    Brown    3
    Wilson    4

    If the user wants to rearrange these so that Jones appears at the end, he would have to manually change Jones to 4, Smith to 1, Brown to 2, Wilson to 3. I'm looking for a much simpler way to automate this process.

    Any suggestions would be very appreciated!

    Tuesday, October 26, 2010 9:18 PM

Answers

  • Thanks, Jeanette. These were help suggestions. However, I was looking for a more complicated solution. Instead of just moving a record up or down one position at a time, I wanted the user to be able to move a record to an entirely different location in the sequence by typing in a new sequence number. For example, if a user wants to move item #4 to position # 25, all he would have to do is type 25 in the sequence field and the whole recordset would be reordered. Actually, I figured out a way to do it using code that steps through every record in the recordset and figures out what new sequence number each record gets. But thanks again for your help!

    Mike

    • Marked as answer by Bessie Zhao Wednesday, November 3, 2010 7:10 AM
    Tuesday, November 2, 2010 8:36 PM

All replies

  • Here  are a couple of ideas.

    1. Use a listbox and let users move the record up or down
    http://www.rogersaccesslibrary.com/forum/topic322.html

    2. Use a bound subform and let users move the record up or down
    http://www.rogersaccesslibrary.com/forum/topic321.html


    Jeanette Cunningham (Access MVP) Pakenham, Victoria Australia
    Tuesday, October 26, 2010 10:30 PM
  • A thing you could do is to display your records in a subform  in datasheet view (kind of like a table view - I call it mySubform) on your mainform.  Then (for the example here) you could place a button on the main form which would change the ordering of the records as follows:

    Private Sub Command4_Click()
       Me.mySubform.Form.RecordSource = "Select * from tblx Order By rowID Desc"
       Me.Requery
       Me.Refresh
    End Sub

    You could do this  from a listbox also where you list all the fields in the table being displayed that you could order by on, and the user could select a field to order on (Asc or Desc), and on the listbox click event when the user selects the desired field to order on the subform gets requeried and refreshed and the new ordering is now displayed

    ---------------------------------------------------

    please mark as Answered if this answered your question

    Tuesday, October 26, 2010 10:45 PM
  • Thanks, Jeanette. These were help suggestions. However, I was looking for a more complicated solution. Instead of just moving a record up or down one position at a time, I wanted the user to be able to move a record to an entirely different location in the sequence by typing in a new sequence number. For example, if a user wants to move item #4 to position # 25, all he would have to do is type 25 in the sequence field and the whole recordset would be reordered. Actually, I figured out a way to do it using code that steps through every record in the recordset and figures out what new sequence number each record gets. But thanks again for your help!

    Mike

    • Marked as answer by Bessie Zhao Wednesday, November 3, 2010 7:10 AM
    Tuesday, November 2, 2010 8:36 PM
  • I am interested to know how a user would know that record #4 needed to move to #25. It's OK if there is only one record that needs to move. If most of the records are out of order, how can the user get the records in order by changing the sequence number.
    Jeanette Cunningham (Access MVP) Pakenham, Victoria Australia
    Wednesday, November 3, 2010 8:00 AM
  • The application is a list of stops in a delivery route. The stops are mostly in the same order every night, but there are a few changes. The users are the delivery drivers and they use the app to put the stops in the best driving order for the night. For example, the list might look like this initially:

    1    Jim, 123 Main St.
    2    Mike, 456 Main St.
    3    Sally, 99 First Ave.
    4    Helen, 45 Second Ave
    ...
    24   Bob, 21 Oak
    25   Jane, 88 Pine
    26   Steve, 77 Maple

    Let's say that tonight the driver wants to make Mike's delivery between the deliveries to Bob and Jane. To do this, the driver changes the sequence number for Mike to 24. The code then calculates that Sally=2, Helen=3 ... Bob=23, Mike=24.

    Ideally, I would love to find a thirdy-party control that works like changing the order of a list on an iPhone. The user would click, drag and drop an item to a new location and be able to see the other items slide out of the way.

     

    Wednesday, November 3, 2010 3:52 PM
  • Mike,
     
        You wish to provide a convenient way for the user to move a given record to an entirely different location (as desired) in the sequence.
     
        In this context, my sample db named InsertRows might be of interest to you. It is in access 2000 file format and is available at Rogers Access Library. Link -
     
        Movement of selected row or group of rows to a different desired position (equivalent to Drag & Drop) is accomplished by selecting a check box on the identified record (or group of records), followed by double click on the destination record next to which the selected record (or group of records) is required to be moved.
     
    Best wishes,
    A.D. Tejpal
    ------------
     
    ----- Original Message -----
    Newsgroups: Msdn.en-US.accessdev
    Sent: Wednesday, November 03, 2010 02:06
    Subject: Re: Looking for a method to let users change the order of records on a form.

    Thanks, Jeanette. These were help suggestions. However, I was looking for a more complicated solution. Instead of just moving a record up or down one position at a time, I wanted the user to be able to move a record to an entirely different location in the sequence by typing in a new sequence number. For example, if a user wants to move item #4 to position # 25, all he would have to do is type 25 in the sequence field and the whole recordset would be reordered. Actually, I figured out a way to do it using code that steps through every record in the recordset and figures out what new sequence number each record gets. But thanks again for your help!

    Mike


    A.D. Tejpal
    Wednesday, November 3, 2010 5:54 PM
  • A.D.

    Thank you. Yes, this accomplishes pretty much what I'm trying to do. But I still wish I could find a third party control that works like re-ordering a list works on an iPhone. Between the iPhone OS and Web 2.0 controls, user interfaces have gotten so much more sophisticated than Access.

    Thanks,
    Mike

    Wednesday, November 3, 2010 11:51 PM