Change Sort Order Combo Box

[Michael]

Hi Folks - I think this is straightforward, but I am brain dead at the
moment ... I currently have a combox box that uses a query to 'look-up' a
doctor's name and number. When the user clicks the drop-down, the fields
displayed are doctor's lastname and doctor's number. The Row Source is set
to sort by the doctor's last name. I would like a way for the user to sort
by doctor's number. In other words, I need a way to dynamically change the
sort order field of the combo box row source. Any ideas? Thanks.

Michael

 

[fredg]

Hi Folks - I think this is straightforward, but I am brain dead at the
moment ... I currently have a combox box that uses a query to 'look-up' a
doctor's name and number. When the user clicks the drop-down, the fields
displayed are doctor's lastname and doctor's number. The Row Source is set
to sort by the doctor's last name. I would like a way for the user to sort
by doctor's number. In other words, I need a way to dynamically change the
sort order field of the combo box row source. Any ideas? Thanks.

Michael

You can, for example, code the combo box double-click event:

If InStr(Me![ComboName].RowSource, "Order By tblDoctors.DoctorName") >
0 Then
Me![ComboName].RowSource = "SELECT tblDoctors.ID,
tblDoctors.DoctorName FROM tblDoctors ORDER BY tblDoctors.ID;"
Else
Me![ComboName].RowSource = "SELECT tblDoctors.ID,
tblDoctors.DoctorName FROM tblDoctors ORDER BY tblDoctors.DoctorName;"
End If

Me.AFriend.Requery

The change in rowsource will toggle when the combo is double-clicked,
but it will not be saved when the form is closed.

Change the control, table, and field names as needed.

 

[Guest]

Change the sort order of the the combo's row source and requery it. I have
something similar:

If Me.cmdComboResort.Caption = "By Name" Then
Me.cboDoctors.Rowsource = "SELECT DoctorLastName, DoctorFirstName,
DoctorNumber From DoctorTable ORDER BY DoctorLastName;"
Me.cmdComboResort.Caption = "By Number"
Else
Me.cboDoctors.Rowsource = "SELECT DoctorLastName, DoctorFirstName,
DoctorNumber From DoctorTable ORDER BY DoctorNumber;"
Me.cmdComboResort.Caption = "By Name"
End If