Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Listbox Move Up or Down (Access 97)

    Hi

    A strange but true question.

    I have a listbox called listbox2 which is populated by table with a single field. This table in turn is populated from another listbox (using a simulated drag and drop process between the two listboxes). The purpose of listbox2 is to display records from the first listbox in a user defined sort order (obviously, listbox2 is unsorted). The results are then printed out.

    However, the user may wish to re-order record(s) further in listbox2. So I'm thinking of using up and down user defined command buttons to move a record up or down in listbox2. The tack I have been using is to use DAO to open the recordset, delete the selected record from listbox2 and then move up or down one position, and then add it back in. The problem is it deletes and adds it back into the same spot regardless of using any the move methods in between.

    This is the code I'm been playing around with for the up command button:

    Dim ItemNo As Integer
    Dim Item As String
    Dim StrQuote As Variant

    If Me.listbox2.ItemsSelected.Count <> 0 Then
    ItemNo = Me. listbox2.ItemsSelected.Item(0)
    Item = Me. listbox2.Column(0, ItemNo)

    Dim DB As Database
    Dim Rst As Recordset

    Set DB = CurrentDb
    Set Rst = DB.OpenRecordset("tblFileNameSort")
    StrQuote = Chr$(34)

    With Rst
    .MoveLast
    .MoveFirst
    .Index = "TxtFileNameSort"
    .Seek "=", Item
    .Delete
    .MovePrevious
    .AddNew
    !TxtFileNameSort = Item
    .Update
    End With

    Rst.Close

    End If
    Me.listbox2.Requery


    So, a couple of questions.
    1 Is this the best way of doing it? If so, Q2 What code do I need to accomplish it.

    Many Thanks

    Regards

    WTH

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Listbox Move Up or Down (Access 97)

    Unfortunately, this won't work. The physical order of records in an Access table is irrelevant. Moreover, you aren't inserting the record between other records at all, a new record is always placed at the end of the table.
    In order to be able to move the items in the list box, you must either have a field on which to sort the records (or change the row source type of the list box to Value List.) Say you add a numeric (long integer) field SortField to the underlying table. Initially, this is populated with 1, 2, 3, ... The Row Source of the list box is

    SELECT DisplayField, SortField FROM ... ORDER BY SortField

    The list box has two columns, the second one is hidden by setting its column width to 0.

    When the user wants to move an item up or down, you retrieve the SortField value of the selected item and that of the next item or previous item. Use DAO or SQL statements to swap the SortField values of the records corresponding to these items. Then requery the list box.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Move Up or Down (Access 97)

    Hi Hans

    Thanks

    I thought that may be the case, but I hoped that there would be a workaround. I'll give your suggestion a try tomorrow as it seems the best appropriate. Thanks again.

    Regards
    WTH

  4. #4
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Listbox Move Up or Down (Access 97)

    Hi Hans

    Just to say I got it work based on your suggestion, although it was a bit more complicated that I originally thought (although that could be me just overcomplicating matters). I guess it just confirms the old adage of what looks like a simple programming job is usually a lot harder to implement than at first glance.

    Regards
    WTH

Posting Permissions

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