The comboboxes are locked and value cannot be selected from list so why use comboboxes and not textboxes?
And why would you want to populate a combobox with this 'highest' category? Are users allowed to change this value?
Options:
1. VBA function that cycles through fields of current record and returns the 'highest' category encountered, like:
Code:
Function GetHighest() As String
Dim x As Integer
Dim intLevel As Integer, intLevel1 As Integer, strLevel As String
For x = 1 To 10
If Not IsNull(Me.Controls("VM R" & x & " Rating")) Then
strLevel = Me.Controls("VM R" & x & " Rating")
intLevel1 = Switch(strLevel = "Critical", 4, strLevel = "High", 3, strLevel = "Moderate", 2, strLevel = "Low", 1)
If intLevel1 > intLevel Then intLevel = intLevel1
End If
Next
GetHighest = Choose(intLevel, "Low", "Moderate", "High", "Critical")
End Function
Expression in textbox: =GetHighest()
2. UNION query to rearrange the 10 fields into normalized structure