Change Sort Order Combo Box

  • Thread starter Thread starter Michael
  • Start date Start date
M

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
 
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
Click to expand...

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.
 
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
 
Back
Top