re-order of list box problem 1

[ibib3]

Hi I'm rather new to VBA but I found and adapted this code to reorder the records of table using a list box on a form curtesy of a chap called Greg Davey I think.

Private Sub cmdUp_Click()

Dim intSelected As Integer
Dim db As Database
Dim rs As Recordset

intSelected = Me.lst1.Column(0, Me.lst1.ListIndex)

Set db = CurrentDb
Set rs = db.OpenRecordset("ListData", dbOpenDynaset)

' find the record that is selected in the listbox
rs.FindFirst "[ListIndex] = " & intSelected
rs.Edit
rs.Fields("ListIndex") = 999
rs.Update
' find the record before the one selected in the listbox
rs.FindFirst "[ListIndex] = " & intSelected - 1
rs.Edit
rs.Fields("ListIndex") = intSelected
rs.Update
' change the 999 record to the correct nmber
rs.FindFirst "[ListIndex] = " & 999
rs.Edit
rs.Fields("ListIndex") = intSelected - 1
rs.Update
rs.Close
Me.lst1.Requery
End Sub

The idea is to use a form to change the priority of a list of people held on a table and it works great.

However the problem I have is that although the selected record moves up or down in the order as required, the selection remains in the original position.
example: two records, Mr Smith (2nd on the list) and Mr Jones (3rd on the list), selecting Mr Jones and pressing the up button moves Mr Jones to 2nd place in the order and Mr Smith moves to 3rd place however the selected record is now Mr Smith. What I need is to still have the originally record (Mr Jones) selected so if necessary I can move him further up the list say to 1st place.

It is probably a very simple problem to solve but I have tried many times to solve it without success, any help would be gratefully appreciated.

 

[JeremyNYC]

Would it work to put this after rs.close?
me!lst1.ListIndex = intselected -1

That should select the item one up from the one that was selected, which should now be the one that was selected!

Neat code, by the way.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JeremyNYC]

No, that clearly doesn't work. sorry. I'm still playing with it. I'll get back in a flash.

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JeremyNYC]

OK, put this line _after_ requerying the listbox:
Me!lbxAccount.Selected(intSelected - 2) = True

That does the trick in my test.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[ibib3]

Many thanks for that Jeremy, I had tried your previous solution too with no success, but your final solution works great.