I have a list in a table “Task-TrainingStatus”:
Id Priority
1 Medium
2 Low
3 High
4 Lowest
5 N/A
6 Highest
My data table “Task-TrainingList” has:
Id PriorityLevel
1 3
2 3
3 6
4 2
5 1
6 2
7 4
8 5
I have a continuous form “TrainingList” that has a combobox “cboxPriority”
Control Source : PriorityLevel
Row Source : SELECT [Task-TrainingStatus].[ID], [Task-TrainingStatus].[Priority] FROM [Task-TrainingStatus] ORDER BY [Priority];
Bound Column: 1
Column Count: 2
Column Widths: 0";1"
The combobox acts as expected, it shows low, lowest, Medium, etc…
I have a label “lblPriority” with an on click event (simplified)
Private Sub lblPriority_Click()
Me.OrderBy = "PriorityLevel"
Me.OrderByOn = True
End Sub
The problem is that it sorts based on the numeric Id so I get:
Medium
Low
High
Lowest
N/A
Highest
and I want it to sort by the text
High
Highest
Low
Lowest
Medium
N/A
The sort is based on the underlying table value and I can’t figure out how to get from there to the text value.
How do I do it?
TIA
Your form needs to use a query as the record source:
SELECT [Task-TrainingList].ID,
[Task-TrainingList].PriorityLevel,
[Task-TrainingStatus].Priority
FROM [Task-TrainingList]
INNER JOIN [Task-TrainingStatus] ON [Task-TrainingList].PriorityLevel = [Task-TrainingStatus].ID;
You can then set the OrderBy property on the form to Priority or as an alternative, you can sort the data in the query:
SELECT [Task-TrainingList].ID,
[Task-TrainingList].PriorityLevel,
[Task-TrainingStatus].Priority
FROM [Task-TrainingList]
INNER JOIN [Task-TrainingStatus] ON [Task-TrainingList].PriorityLevel = [Task-TrainingStatus].ID
ORDER BY [Task-TrainingStatus].Priority;
6