Order items in list box

#1 (**permalink**) 28-Jul-2006, 08:17 AM

hi all --
I have a combobox (name cboProgram);
Once the program is loaded, a listbox of associated Elements are
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10974-order-items-in-list-box.html
generated.

The listbox (name lboElement), which has ElementID, ElementName,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10974
ElementOrder

When the items appear in the list box, I have the "UP" "DOWN" arrows to
the left of it to allow the user to change the ElementOrder.

How can this be done?

Thanks!

*

#2 (**permalink**) 28-Jul-2006, 08:17 AM

wrote in message
news:1149690155.363042.295480@y43g2000cwc.googlegr oups.com
> hi all --
> I have a combobox (name cboProgram);
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10974
> Once the program is loaded, a listbox of associated Elements are
> generated.
>
> The listbox (name lboElement), which has ElementID, ElementName,
> ElementOrder
>
> When the items appear in the list box, I have the "UP" "DOWN" arrows
> to the left of it to allow the user to change the ElementOrder.
>
> How can this be done?

Where do the list items come from? If it's a table, and the table
contains the fields ElementID, ElementName, and ElementOrder, then the
process of moving an element up or down is going to involve swapping the
values of ElementOrder in two records -- probably via update queries--
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10974
and then requerying the list box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

#3 (**permalink**) 28-Jul-2006, 08:17 AM

The list items come from
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10974
tblElements
ElementID
ElementName
ElementOrder

Dirk Goldgar wrote:
> wrote in message
> news:1149690155.363042.295480@y43g2000cwc.googlegr oups.com
> > hi all --
> > I have a combobox (name cboProgram);
> > Once the program is loaded, a listbox of associated Elements are
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10974
> > generated.
> >
> > The listbox (name lboElement), which has ElementID, ElementName,
> > ElementOrder
> >
> > When the items appear in the list box, I have the "UP" "DOWN" arrows
> > to the left of it to allow the user to change the ElementOrder.
> >
> > How can this be done?
>
> Where do the list items come from? If it's a table, and the table
> contains the fields ElementID, ElementName, and ElementOrder, then the
> process of moving an element up or down is going to involve swapping the
> values of ElementOrder in two records -- probably via update queries--
> and then requerying the list box.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)

#4 (**permalink**) 28-Jul-2006, 08:17 AM

wrote in message
news:1149692178.103309.101990@g10g2000cwb.googlegr oups.com
> The list items come from
> tblElements
> ElementID
> ElementName
> ElementOrder
>
> Dirk Goldgar wrote:
>> wrote in message
>> news:1149690155.363042.295480@y43g2000cwc.googlegr oups.com
>>> hi all --
>>> I have a combobox (name cboProgram);
>>> Once the program is loaded, a listbox of associated Elements are
>>> generated.
>>>
>>> The listbox (name lboElement), which has ElementID, ElementName,
>>> ElementOrder
>>>
>>> When the items appear in the list box, I have the "UP" "DOWN" arrows
>>> to the left of it to allow the user to change the ElementOrder.
>>>
>>> How can this be done?
>>
>> Where do the list items come from? If it's a table, and the table
>> contains the fields ElementID, ElementName, and ElementOrder, then
>> the process of moving an element up or down is going to involve
>> swapping the values of ElementOrder in two records -- probably via
>> update queries-- and then requerying the list box.

I hadn't written an example of this before, so I took a moment now to do
it. Set your list box's RowSource property to this:

SELECT ElementID, ElementName, ElementOrder
FROM tblElements
ORDER BY tblElements.ElementOrder;

Name your "Up" and "Down" buttons "cmdMoveUp" and "cmdMoveDown",
respectively.

Now add the following event procedures to your form's class module:

'----- start of code -----
Private Sub cmdMoveDown_Click()

On Error GoTo Err_Handler

Dim lngCurrItem As Long
Dim lngCurrOrder As Long
Dim lngCurrRow As Long
Dim lngNextItem As Long
Dim lngNextOrder As Long
Dim lngNextRow As Long

Dim ws As DAO.Workspace
Dim db As DAO.Database

Dim fInTrans As Boolean

With Me.lboElement

If IsNull(.Value) Then
DoCmd.Beep
Exit Sub
End If

lngCurrRow = .ItemsSelected(0)
If lngCurrRow = (.ListCount - 1) Then
DoCmd.Beep
Exit Sub
End If

lngCurrItem = .Value
lngCurrOrder = .Column(2)

lngNextRow = lngCurrRow + 1
lngNextItem = .ItemData(lngNextRow)
lngNextOrder = .Column(2, lngNextRow)

Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)

ws.BeginTrans
fInTrans = True

db.Execute _
"UPDATE tblElements SET ElementOrder = " & lngNextOrder & _
" WHERE ElementID = " & lngCurrItem, _
dbFailOnError

db.Execute _
"UPDATE tblElements SET ElementOrder = " & lngCurrOrder & _
" WHERE ElementID = " & lngNextItem, _
dbFailOnError

ws.CommitTrans

.Requery

End With

Exit_Point:
On Error Resume Next
If fInTrans Then
ws.Rollback
End If
Set db = Nothing
Set ws = Nothing
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub

Private Sub cmdMoveUp_Click()

On Error GoTo Err_Handler

Dim lngCurrItem As Long
Dim lngCurrOrder As Long
Dim lngCurrRow As Long
Dim lngPrevItem As Long
Dim lngPrevOrder As Long
Dim lngPrevRow As Long

Dim ws As DAO.Workspace
Dim db As DAO.Database
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10974

Dim fInTrans As Boolean

With Me.lboElement

If IsNull(.Value) Then
DoCmd.Beep
Exit Sub
End If

lngCurrRow = .ItemsSelected(0)
If lngCurrRow = 0 Then
DoCmd.Beep
Exit Sub
End If

lngCurrItem = .Value
lngCurrOrder = .Column(2)

lngPrevRow = lngCurrRow - 1
lngPrevItem = .ItemData(lngPrevRow)
lngPrevOrder = .Column(2, lngPrevRow)

Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)

ws.BeginTrans
fInTrans = True

db.Execute _
"UPDATE tblElements SET ElementOrder = " & lngPrevOrder & _
" WHERE ElementID = " & lngCurrItem, _
dbFailOnError

db.Execute _
"UPDATE tblElements SET ElementOrder = " & lngCurrOrder & _
" WHERE ElementID = " & lngPrevItem, _
dbFailOnError

ws.CommitTrans

.Requery

End With

Exit_Point:
On Error Resume Next
If fInTrans Then
ws.Rollback
End If
Set db = Nothing
Set ws = Nothing
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10974
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of code -----

NOTE: the above code assumes that the list box has its ColumnHeads
property set to No. If you're displaying column heads in the list box,
a couple of minor changes have to be made to the code.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

#5 (**permalink**) 28-Jul-2006, 08:17 AM

perfect ! thanks!

Dirk Goldgar wrote:
> wrote in message
> news:1149692178.103309.101990@g10g2000cwb.googlegr oups.com
> > The list items come from
> > tblElements
> > ElementID
> > ElementName
> > ElementOrder
> >
> > Dirk Goldgar wrote:
> >> wrote in message
> >> news:1149690155.363042.295480@y43g2000cwc.googlegr oups.com
> >>> hi all --
> >>> I have a combobox (name cboProgram);
> >>> Once the program is loaded, a listbox of associated Elements are
> >>> generated.
> >>>
> >>> The listbox (name lboElement), which has ElementID, ElementName,
> >>> ElementOrder
> >>>
> >>> When the items appear in the list box, I have the "UP" "DOWN" arrows
> >>> to the left of it to allow the user to change the ElementOrder.
> >>>
> >>> How can this be done?
> >>
> >> Where do the list items come from? If it's a table, and the table
> >> contains the fields ElementID, ElementName, and ElementOrder, then
> >> the process of moving an element up or down is going to involve
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10974
> >> swapping the values of ElementOrder in two records -- probably via
> >> update queries-- and then requerying the list box.
>
> I hadn't written an example of this before, so I took a moment now to do
> it. Set your list box's RowSource property to this:
>
> SELECT ElementID, ElementName, ElementOrder
> FROM tblElements
> ORDER BY tblElements.ElementOrder;
>
> Name your "Up" and "Down" buttons "cmdMoveUp" and "cmdMoveDown",
> respectively.
>
> Now add the following event procedures to your form's class module:
>
> '----- start of code -----
> Private Sub cmdMoveDown_Click()
>
> On Error GoTo Err_Handler
>
> Dim lngCurrItem As Long
> Dim lngCurrOrder As Long
> Dim lngCurrRow As Long
> Dim lngNextItem As Long
> Dim lngNextOrder As Long
> Dim lngNextRow As Long
>
> Dim ws As DAO.Workspace
> Dim db As DAO.Database
>
> Dim fInTrans As Boolean
>
> With Me.lboElement
>
> If IsNull(.Value) Then
> DoCmd.Beep
> Exit Sub
> End If
>
> lngCurrRow = .ItemsSelected(0)
> If lngCurrRow = (.ListCount - 1) Then
> DoCmd.Beep
> Exit Sub
> End If
>
> lngCurrItem = .Value
> lngCurrOrder = .Column(2)
>
> lngNextRow = lngCurrRow + 1
> lngNextItem = .ItemData(lngNextRow)
> lngNextOrder = .Column(2, lngNextRow)
>
> Set ws = DBEngine.Workspaces(0)
> Set db = ws.Databases(0)
>
> ws.BeginTrans
> fInTrans = True
>
> db.Execute _
> "UPDATE tblElements SET ElementOrder = " & lngNextOrder & _
> " WHERE ElementID = " & lngCurrItem, _
> dbFailOnError
>
> db.Execute _
> "UPDATE tblElements SET ElementOrder = " & lngCurrOrder & _
> " WHERE ElementID = " & lngNextItem, _
> dbFailOnError
>
> ws.CommitTrans
>
> .Requery
>
> End With
>
> Exit_Point:
> On Error Resume Next
> If fInTrans Then
> ws.Rollback
> End If
> Set db = Nothing
> Set ws = Nothing
> Exit Sub
>
> Err_Handler:
> MsgBox Err.Description, vbExclamation, "Error " & Err.Number
> Resume Exit_Point
>
> End Sub
>
> Private Sub cmdMoveUp_Click()
>
> On Error GoTo Err_Handler
>
> Dim lngCurrItem As Long
> Dim lngCurrOrder As Long
> Dim lngCurrRow As Long
> Dim lngPrevItem As Long
> Dim lngPrevOrder As Long
> Dim lngPrevRow As Long
>
> Dim ws As DAO.Workspace
> Dim db As DAO.Database
>
> Dim fInTrans As Boolean
>
> With Me.lboElement
>
> If IsNull(.Value) Then
> DoCmd.Beep
> Exit Sub
> End If
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10974
> lngCurrRow = .ItemsSelected(0)
> If lngCurrRow = 0 Then
> DoCmd.Beep
> Exit Sub
> End If
>
> lngCurrItem = .Value
> lngCurrOrder = .Column(2)
>
> lngPrevRow = lngCurrRow - 1
> lngPrevItem = .ItemData(lngPrevRow)
> lngPrevOrder = .Column(2, lngPrevRow)
>
> Set ws = DBEngine.Workspaces(0)
> Set db = ws.Databases(0)
>
> ws.BeginTrans
> fInTrans = True
>
> db.Execute _
> "UPDATE tblElements SET ElementOrder = " & lngPrevOrder & _
> " WHERE ElementID = " & lngCurrItem, _
> dbFailOnError
>
> db.Execute _
> "UPDATE tblElements SET ElementOrder = " & lngCurrOrder & _
> " WHERE ElementID = " & lngPrevItem, _
> dbFailOnError
>
> ws.CommitTrans
>
> .Requery
>
> End With
>
> Exit_Point:
> On Error Resume Next
> If fInTrans Then
> ws.Rollback
> End If
> Set db = Nothing
> Set ws = Nothing
> Exit Sub
>
> Err_Handler:
> MsgBox Err.Description, vbExclamation, "Error " & Err.Number
> Resume Exit_Point
>
> End Sub
> '----- end of code -----
>
> NOTE: the above code assumes that the list box has its ColumnHeads
> property set to No. If you're displaying column heads in the list box,
> a couple of minor changes have to be made to the code.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)