Search Using a Textbox

[ckeener]

I have this Code that is supposed let me enter text into a textbox and it is supposed to search down the listbox (lstSongs) and go to the song I am typng in as I type it. It lets me type things into the textbox(txtSongSearch) but the listbox does not search down to the record I type into the listbox.

Private Sub txtSongSearch_Change()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT SongRef, [Sort Title], Artist, Album, [St Code], Length, Tempo, " & _
"Rating FROM qrySongsSwitchboard WHERE [Sort Title] = '" & Me.txtSongSearch.Text & "*'"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.RecordCount > 0 Then
rs.MoveFirst
Me!lstSongs = rs!SongRef
End If

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Can any one tell what I am doing improperly?? It works when I use it from a table.

 

[hneal98]

Change the = to Like.

 

[ckeener]

Well, I just tried that and the same thing is happening. Basically here is what I want to do. I want to type the name of the song in the textbox and have it sort through the songs on the list box to the name of the song I am typing. For example, I type in "c" in the textbox, it goes to the first song starting with the letter "c"; I type in "cr" then it goes down to the first song starting with "cr"; and it keeps going as I type until it finds the song in the listbox closest to what I am typing. This code works in a different form.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT AlbumRef, Title " & _
"FROM tblAlbums WHERE Title LIKE '" & _
Me!txtAlbumInput.Text & "*'"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.RecordCount > 0 Then
rs.MoveFirst
Me!lstAlbum = rs!AlbumRef
End If

rs.Close
Set rs = Nothing
Set db = Nothing

 

[hneal98]

I also put an order by in the query that populated the list box under the list box's RowSource property, and I put an order by in this query and it worked perfectly for me.

One other difference i did was this statement:

Me!lstAlbum = rs!AlbumRef

I changed it to

rs.Fields("AlbumRef&quot

You wouldn't think it would make a difference though.

 

[PeteJohnston]

Maybe you could try using a combo box instead. If you create it with RowSourceType = Table/Query, set the RowSource as "SELECT AlbumRef, Title FROM tblAlbums ORDER BY Title" and set it so that the first column width is 0. That way it only shows the Title even though the AlbumRef is still the bound column. As you type in the title it moves the selected item in the combo box.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[ckeener]

Thanks, that would definitely work but I have to have the listbox.

The Code with AlbumRef, Title is the Code that already works and that I was trying to modify to work in the code that I included in my first post. I kinda really only included the secod snippit of code to show what I was trying to modify.

I am sorry for the confusion.

Caleb