combo box to change sortorder on list box fields?

[pablavo]

Hello,

I've looked through the forums for an answer to this and wouldn't expect this to be a hard one.

On my form I have a list box which display three fields.

I also have a combo box that I want to use to sort the order of each of the fields.

The drop down will display the three fields that are in the list box. When a user can selects a field name from the drop down, the sort order will change in the list box.

I've looked at orderby property and can't seem to get anything working there.

Does anyone know of any articles perhaps?

thanks for all help

 

[maxmangion]

Hi,

in order to sort based on a combo box value, i normally use something like the following from on the OnClick event of a command button:

Private Sub btnSort_Click()
On Error GoTo Err_btnSort_Click
'Dim ctl As Control
'For Each ctl In Me.Form.Controls
'If TypeOf ctl Is Label Then
'ctl.BackColor = 12632256
'End If
'Next ctl
Select Case Me.cboSort
Case "WordID"
Me.WordID.SetFocus
DoCmd.RunCommand acCmdSortAscending
'Me.WordID_Label.BackColor = vbRed
Case "Category"
Me.Category.SetFocus
DoCmd.RunCommand acCmdSortAscending
'Me.Category_Label.BackColor = vbRed
Case "EnglishWord"
Me.EnglishWord.SetFocus
DoCmd.RunCommand acCmdSortAscending
'Me.EnglishWord_Label.BackColor = vbRed
Case "MalteseWord"
Me.MalteseWord.SetFocus
DoCmd.RunCommand acCmdSortAscending
'Me.MalteseWord_Label.BackColor = vbRed
End Select
Exit_btnSort_Click:
Exit Sub
Err_btnSort_Click:
MsgBox Err.Description, vbInformation + vbOKOnly, "Error #" & Err.Number
Resume Exit_btnSort_Click
End Sub

You can omit the commented lines, as i use those to change the color so that it indicates me which field the sort has been applied to.

However, i am not sure if this code is the best approach, but nonetheless it works for me.

 

[namliam]

Use the "on change" event of your combobox to add/change the order by of the recordsource for you listbox.

Then you may need to requery it... not sure.

 

[pablavo]

I was thinking about something that would utilise the on_Change event.

Thanks maxmangion for the code.

namliam, you're helping me a lot this summer. Greatful for it

 

[namliam]

Tho totaly unindented, Max' code is intended for use on forms not for use on Listbox.

And to be honest Max, it is less than optimal...
This:

Case "Category"
    Me.Category.SetFocus
    DoCmd.RunCommand acCmdSortAscending

should read

Case "Category"
    Me.orderby = "[Category]"
    Me.OrderByOn = True

It is much more flexible and more intuative to the user IMHO. Because you dont need to change the focus on the controls.

 

[maxmangion]

Hi namliam,

in fact that's why i told him that it is not the best approach, however, i remember myself running into some trouble when using the order by, and orderbyon (which unfortunately i don't remember exactly the issue).

But, now i will simply try to substitute the code with your suggestion, to see if i get the same error which i used to get.

Thx

 

[pablavo]

I've tried both suggestions, however, I'm not sure how to reference the list box's fields. The list box and combo box are on a unbound form.

If I put the previous code in, there's no field names to reference.

I thought It'd work if I used the something like

Me.lstboxName.column.(0)

Still not sure

 

[namliam]

Me.ListboxName will return the value selected in the listbox.

You cannot sort a listbox directly, but you have to sort its rowsource...