Problem sorting by alphabetical order...

[JuniorPhatFarm]

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.

[jggtz]

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 

[JuniorPhatFarm]

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.

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

[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

[JuniorPhatFarm]

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.