Sorting drop down lists from combo boxes

[brillig12]

Hi,

I am using a form which brings up data on staff once they have been selected in one of three combo boxes. These are salary number, first name and surname. Once a selection has been made then the other two boxes are autopopulated and the data is sourced from the tables. This is all working correctly but all three combo boxes source their lists from the same table. The data in the table is sorted by salary number so the drop down lists are as well.

Is there a simple way that I can get all three drop downs to display in ascending order?

Currently the name combo boxes are near useless because they display the names in a random order.

Any help offered is much appreciated.

Thanks!

 

[pbaldy]

Rather than the rowsource being the table, change it to SQL so you can add sorting to it. Do that by clicking on the ellipsis (...) to the far right on that row.

 

[brillig12]

Thanks for the tip. I could not get it to work however. I clicked on the (...) and added the sort feature to the SQL but all three list remain sorted by the order of the table. Is there something else I needed to have done?

 

[missinglinq]

What is the exact SQL statement you used for the RowSource of the combobox?

It should be something on the order of

SELECT YourTableName.SurName, YourTableName.FName FROM YourTableName ORDER BY [SurName];

where (in this case) you want to sort the combobox by the SurName field.

 

[brillig12]

The SQL is as below:

SELECT PersonalDetails.SalaryNumber, PersonalDetails.Forenames, PersonalDetails.Surname FROM PersonalDetails ORDER BY PersonalDetails.Forenames;

Am new to SQL so am glad of any light you can shed on this

 

[pbaldy]

That should be ordered on the Forename field. You're saying it is not? Can you post a sample?

 

[missinglinq]

Where are you placing that RowSource? That will pull all records from the PersonalDetails table, yet you describe what is called cascading comboboxes. This means that the RowSource for the next combobox is being set in code in the first cbo's AfterUpdate event, depending on which staff member is selected. It is here that you need to adjust your SQL so that the Order By is done as you want, not in the RowSource Property in the Properties Sheet.

Or am I misunderstanding what you're doing?

 

[brillig12]

My apologies. Upon checking I can see that there are three fields directly below the three combo boxes. When a selection is made in any of the combo boxs then the are all cleared and the three fields below are populated. I was convinced that the combo boxes were populated but it is not the case.

In answer to other questions the RowSource is Table/Query and the vba which runs todo with the combo boxes is given below. There are two subs for each combo box:

Private Sub cboSicknessFore_Click()
On Error GoTo NoRecords_ForCombo_Error
Dim strWhere As String
strWhere = cboSicknessFore.Column(0)
Call GetPersonalJobAddressData(strWhere)
Call ClearAllComboBoxes
Call PopulateGrids(strWhere)
'Clean up
No_Worries_Exit:
Exit Sub
NoRecords_ForCombo_Error:
    MsgBox "No records found, check database!", vbCritical, "Fatal Error"
    Resume No_Worries_Exit

End Sub
Private Sub cboSicknessFore_NotInList(NewData As String, Response As Integer)
    cboSicknessFore.Value = ""
    Call ComboBoxNotInListMsg(Response)
End Sub
Private Sub cboSicknessSal_Click()
On Error GoTo NoRecords_ForCombo_Error
Dim strWhere As String
strWhere = cboSicknessSal.Column(0)
Call GetPersonalJobAddressData(strWhere)
Call ClearAllComboBoxes
Call PopulateGrids(strWhere)
'Clean up
No_Worries_Exit:
Exit Sub
NoRecords_ForCombo_Error:
    MsgBox "No records found, check database!", vbCritical, "Fatal Error"
    Resume No_Worries_Exit

End Sub
Private Sub cboSicknessSal_NotInList(NewData As String, Response As Integer)
    cboSicknessSal.Value = ""
    Call ComboBoxNotInListMsg(Response)
End Sub
Private Sub cboSicknessSur_Click()
On Error GoTo NoRecords_ForCombo_Error
Dim strWhere As String
strWhere = cboSicknessSur.Column(0)
Call GetPersonalJobAddressData(strWhere)
Call ClearAllComboBoxes
Call PopulateGrids(strWhere)
'Clean up
No_Worries_Exit:
Exit Sub
NoRecords_ForCombo_Error:
    MsgBox "No records found, check database!", vbCritical, "Fatal Error"
    Resume No_Worries_Exit

End Sub
Private Sub cboSicknessSur_NotInList(NewData As String, Response As Integer)
    cboSicknessSur.Value = ""
    Call ComboBoxNotInListMsg(Response)
End Sub

It's a bit over my head so any help is appreciated.

Thanks!