Combo Box will not display list in correct order

[ronymaxwell]

I have code attached to a command button to fill a Combo Box with data from a music collection. A letter of the alphabet is entered into a Text Box then records beginning with that letter are copied from a table, either by Artist or Title. They are saved to a temporary table at which time they are in no particular order. Those records are copied to a further table and saved in alphabetical order. This table is then used to fill the Combo Box.

I used two temporary tables because the records were not displayed in the correct order. I hoped this might cure it, it did not. The records are in order in the table but not in the Combo Box.

This is the code

Private Sub Command68_Click()
'SEARCH AND FILL COMBO BOX
On Error GoTo errTrap

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tempList;"
DoCmd.RunSQL "DELETE * FROM aList;"
'aList used if Artist searched
DoCmd.RunSQL "DELETE * FROM tList;"
'tList used if Title searched

If Len(sArtist.Value) = 0 And Len(sTitle.Value) = 0 Then
    MsgBox "No Letter of the Alphabet entered to Search"
    DoCmd.SetWarnings True
    Exit Sub
ElseIf Len(sArtist.Value) > 0 Then
    DoCmd.RunSQL "INSERT INTO tempList SELECT rId, rTitle, rArtist, rLength, rSize FROM rList WHERE Left$(rArtist, 1) = '" & Left$(sArtist.Value, 1) & "';"
ElseIf Len(sTitle.Value) > 0 Then
    DoCmd.RunSQL "INSERT INTO tempList SELECT rId, rTitle, rArtist, rLength, rSize FROM rList WHERE Left$(rTitle, 1) = '" & Left$(sTitle.Value, 1) & "';"
End If

If Len(sArtist.Value) > 0 Then
    DoCmd.RunSQL "INSERT INTO aList SELECT rId, rTitle, rArtist, rLength, rSize FROM tempList ORDER BY rArtist;"
ElseIf Len(sTitle.Value) > 0 Then
    DoCmd.RunSQL "INSERT INTO tList SELECT rId, rTitle, rArtist, rLength, rSize FROM tempList ORDER BY rTitle;"
End If

If Len(sArtist.Value) > 0 Then
    cb2.RowSource = "aList"
ElseIf Len(sTitle.Value) > 0 Then
    cb2.RowSource = "tList"
End If

DoCmd.SetWarnings True

errExit:
Exit Sub

errTrap:
MsgBox "Search Item Not Found"
Resume errExit
End Sub

 

[smig]

no need for temp tables
try this code (Please give your commands meaningful names :banghead

Private Sub Command68_Click()
'SEARCH AND FILL COMBO BOX
On Error GoTo errTrap

dim strSQL as string

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tempList;"
DoCmd.RunSQL "DELETE * FROM aList;"
'aList used if Artist searched
DoCmd.RunSQL "DELETE * FROM tList;"
'tList used if Title searched

If Len(sArtist.Value) = 0 And Len(sTitle.Value) = 0 Then
    MsgBox "No Letter of the Alphabet entered to Search"
    Exit Sub
ElseIf Len(sArtist.Value) > 0 Then
    strSQL = "SELECT rId, rTitle, rArtist, rLength, rSize FROM rList WHERE Left$(rArtist, 1) = '" & Left$(sArtist.Value, 1) & "' ORDER BY rArtist"
ElseIf Len(sTitle.Value) > 0 Then
    strSQL = "SELECT rId, rTitle, rArtist, rLength, rSize FROM rList WHERE Left$(rTitle, 1) = '" & Left$(sTitle.Value, 1) & "' ORDER BY rTitle"
End If

cb2.RowSource = strSQL 
cb2.requery

errExit:
Exit Sub

errTrap:
MsgBox "Search Item Not Found"
Resume errExit
End Sub

 

[ronymaxwell]

Thank you, Smig. That works perfectly.

 

[smig]

Thank you, Smig. That works perfectly.

Glad I could help


You can delete all the DoCmd part

also, you should put your comments above the relative lines of code.