Problem sorting by alphabetical order...

Hello:

I've got a lists of risk ids that display in one box "strID" and I've created another box "strID1"to display the risk ids as well as the descriptions. I can't figure out how to display them in alphabetical order. The code is below:

Private Sub cmdProcess_Click()
Dim x, y As Integer

Dim cBox, cQuery, cQuery1 As String

Dim strID, strID1, strThreshhold As String, rs As dao.Recordset, sql As String

strID = ""

If Me.cboQuarter.Value <> "" And Me.cboYear.Value <> "" And Me.cboSite.Value <> "" And Me.cbothreshold.Value <> "" Then

If Trim(Me.cboSite.Value) = "All" Then

Me.cboSite.Value = ""

End If

cQuery = "quarter = '" & Trim(Me.cboQuarter.Value) & "' And year = " & Trim(Str(Me.cboYear.Value)) & " And Risk_ID like '" & Trim(Me.cboSite.Value) & "*'"

For x = 1 To 5
For y = 1 To 5

strID = ""

sql = "select risk_ID, risk_description from risks where " & cQuery & strThreshhold & " And Int(residual_consequence_score) =" & Trim(Str(y)) & " And Int(residual_likelihood_score) = " & Trim(Str(x))

Set rs = CurrentDb.OpenRecordset(sql)

cBox = "Risk" & Trim(Str(x)) & Trim(Str(y))

Me.Controls(cBox).Value = ""
Do Until rs.EOF


strID = strID & " " & rs("risk_id")
strID1 = strID1 & " " & rs("risk_id") & " - " & rs("risk_description") & vbCrLf '****Can anyone get this to display alphabetically****?
rs.MoveNext
Me.Controls(cBox).Value = strID

Loop

Next y
Next x

Me.Controls!RiskDesc = strID1

If Trim(Me.cboSite.Value) = "" Then

Me.cboSite.Value = "All"

End If

Me.cmdPrint.Enabled = True

Me.Refresh

Else

MsgBox ("Please select a value before clicking the Generate Chart button")

End If

Debug.Print sql

End Sub

Any help is appreciated. I'm a novice coder and using VB 6.5 and Access 2003.

Code:

---

sql = "SELECT risk_ID, risk_description
      FROM    risks 
      WHERE " & cQuery & strThreshhold &
                " And Int(residual_consequence_score) =" &
                  Trim(Str(y)) &
                " And Int(residual_likelihood_score) = " &
                  Trim(Str(x))
      ORDER BY risk_description

---

Thanks for the suggestion... Unfortunately this is still not working. Is the new sql statement in the correct place? It needs to be after the loop so it just affects the display box w/ the descriptions.

Code:

---

Private Sub cmdProcess_Click()
Dim x, y As Integer

Dim cBox, cQuery, cQuery1 As String

Dim strID, strID1, strThreshhold As String, rs As dao.Recordset, sql As String

strID = ""


If Me.cboQuarter.Value <> "" And Me.cboYear.Value <> "" And Me.cboSite.Value <> "" And Me.cbothreshold.Value <> "" Then

    If Trim(Me.cboSite.Value) = "All" Then
   
    Me.cboSite.Value = ""
       
    End If 

    cQuery = "quarter = '" & Trim(Me.cboQuarter.Value) & "' And year = " & Trim(Str(Me.cboYear.Value)) & " And Risk_ID like '" & Trim(Me.cboSite.Value) & "*'"
   
    For x = 1 To 5

    For y = 1 To 5

    strID = ""

    sql = "select risk_ID, risk_description from risks where " & cQuery & strThreshhold & " And Int(residual_consequence_score) =" & Trim(Str(y)) & " And Int(residual_likelihood_score)  = " & Trim(Str(x))
     
    Set rs = CurrentDb.OpenRecordset(sql)

    cBox = "Risk" & Trim(Str(x)) & Trim(Str(y))

    Me.Controls(cBox).Value = ""
    Do Until rs.EOF
             

    strID = strID & " " & rs("risk_id")
    strID1 = strID1 & " " & rs("risk_id") & " - " & rs("risk_description") & vbCrLf
    rs.MoveNext
    Me.Controls(cBox).Value = strID

    Loop

    sql1 = "SELECT risk_ID, risk_description from risks where " & cQuery & strThreshhold & " And Int(residual_consequence_score) =" & Trim(Str(y)) & " And Int(residual_likelihood_score) = " & Trim(Str(x)) & " order by risk_ID"

    Next y

    Next x
   
        Me.Controls!RiskDesc = strID1
   
    If Trim(Me.cboSite.Value) = "" Then
   
    Me.cboSite.Value = "All"
   
             
    End If

    Me.cmdPrint.Enabled = True

    Me.Refresh

Else

    MsgBox ("Please select a value for Site before clicking the Generate Chart button")

End If

  Debug.Print sql

End Sub

---

Sorry, didn't checked out the rest of your code...
It seems to me that you want to fill a Combo box with some data from a recordset
Then you don't need the For-Next

1.- Open your recordset with the desired filters (quarter, year, site, etc) and order it by description
2.- Then process your recordset form bof to eof and add items to the combobox

Quote:

---

Originally Posted by jggtz

Sorry, didn't checked out the rest of your code...
It seems to me that you want to fill a Combo box with some data from a recordset
Then you don't need the For-Next

1.- Open your recordset with the desired filters (quarter, year, site, etc) and order it by description
2.- Then process your recordset form bof to eof and add items to the combobox

---

Thanks... Will try out.