Find code help

[Wylie C]

I have a table (tblSongs) with a field (Album) in it. I have a form with a
listbox (lstTitle) that after selecting the album name, I want to show all
the fields (Track, Artist, Title) of the album selected in the listbox (like
a query would display.) Essentially I want to do the equivelant of what a
query would do after inputting the entire album name).

I have the following code but I am unable to pick up the Album in the code.

Private Sub cmdFindAlbum_Click()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset

strCriteria = Me.lstTitle
rst.CursorType = adOpenDynamic 'opens recordset to support all cursor and
record movements
rst.Open "tblSongs ORDER BY tblSongs.Album;", CurrentProject.Connection
Debug.Print rst.State
Debug.Print Album
Dim s As String
s = rst.Fields(0)
With rst
rst.MoveFirst 'move to first rec
Do While Not .EOF
rst.Find ("s = '" & strCriteria & "'") 'locate correct rec
rst.MoveNext
If rst.EOF Then 'returns eof if no
match found
rstClose
Exit Sub 'exit
End If 'else do this
Loop
End With

End Sub

 

[Tim Ferguson]

I want to show all
the fields (Track, Artist, Title) of the album selected in the listbox
(like a query would display.)

With MyListBox
.RowSource = "SELECT Track, Artist, Title " & _
"FROM Songs " & _
"WHERE Album = """ & Me.lisAlbums.Value & """ " & _
"ORDER BY Track ASC"
.Requery
End With



If you are using ADO you might have to use single quotes rather than
double: I'm not sure about rowsource values:-

... WHERE Album = '" & Me.lisAlbums.Value & "' ...


Of course, if the Album value is numeric rather than string, then you
don't need any internal quotes at all:-

... WHERE Album = " & Me.lisAlbums.Value & " ...


Hope that helps


Tim F