list box, move up move down

G

george

Hi to all,

I have tblCategories containing CatID, CatName and CatRelativePosition.
CatID is an autonumber field, CatName is a text field and CatRelativePosition
a long integer field.

I have added the last field in order to be used in a list box based on this
table, lstCategories, where the user will be able to change at wish the
relative order that Categories appear in the list box by using cmdMoveUp and
cmdMoveDown.

Unfortunately, I still have not figured out the code I'm going to use behind
the two command buttons. Can someone please help either with a suggestion
about the code or by directing me to some other site where something
pertaining to my problem can be found?

thanks in advance, george
 
R

Ron Weiner

Here is some working code that does this. I am manulipulating a List box
(lstParts) that has 3 Columns. Here is the row source for the list box:
SELECT ID, Descr, Seq FROM tblParts ORDER BY Seq

The Column widths are set to 0;1;0

The form has two buttons cmdDn and cmdUp. Here is the code behind that
click event of those buttons.

Private Sub cmdDn_Click()
Dim strsql As String
Dim lngOldVal As Long
If lstParts.ListIndex > -1 And lstParts.ListIndex < lstParts.ListCount -
1 Then
' First Update the Seq of the record below with our Seq
strsql = "UPDATE tblParts SET Seq = " _
& lstParts.Column(2) & " WHERE Seq = " _
& lstParts.Column(2) + 1
CurrentDb.Execute strsql
' Then increment our seq
strsql = "UPDATE tblParts SET Seq = " _
& lstParts.Column(2) + 1 & " WHERE ID = " _
& lstParts.Value
CurrentDb.Execute strsql
' Finaly update the list box
lngOldVal = lstParts.Value
lstParts.Requery
lstParts.Value = lngOldVal
lstParts.Selected(lstParts.ListIndex) = True
End If
End Sub

Private Sub cmdUp_Click()
Dim strsql As String
Dim lngOldVal As Long
If lstParts.ListIndex > 0 Then
' First Update the Seq of the record above with our Seq
strsql = "UPDATE tblParts SET Seq = " _
& lstParts.Column(2) & " WHERE Seq = " _
& lstParts.Column(2) - 1
CurrentDb.Execute strsql
' Then decrement our seq
strsql = "UPDATE tblParts SET Seq = " _
& lstParts.Column(2) - 1 & " WHERE ID = " _
& lstParts.Value
CurrentDb.Execute strsql
' Finaly update the list box
lngOldVal = lstParts.Value
lstParts.Requery
lstParts.Value = lngOldVal
lstParts.Selected(lstParts.ListIndex) = True
End If
End Sub

The only gotcha with is senario is that it is imperitive that the Seq column
NOT have any holes in the number sequence.

Ron W
www.WorksRite.com
 
Top