How do I create a SEARCH in Access?

[Guest]

I am trying to create a searchable MP3 database by song/artist.

Then write it as a webpage, I do DJ work and thought this would be a GREAT
way to search my music instead of using a program.

I created an access DB containing all the artists and song titles and other
info that I want, but I cannot find a way to create a SEARCH for any item, I
can get all the colums in a list box, but not able to get it to do what I
want.

Artist {BOX} Title {BOX} Album {BOX} Stored Location {BOX}

I want to search for any data in either box and show up in them all.
Like the CDDB works.

 

[Allen Browne]

This example assumes you have a continuous form, bound to your table, and in
the Form Header section you have unbound text boxes where the user can enter
the start of an artist, title, album, location, or any combination thereof.

These unbound boxes are named "txtFindArtist", "txtFindTitle", etc. The code
examines each box, builds up a filter string from whichever ones have an
entry, and then filters the form to only those entries that match them all.
It uses a wildcard at the end, so that you can enter just the start of the
name in any box.

Beside the text boxes, add a command button to actually apply the filter.
Set its On Click property to
[Event Procedure]
Click the Build button (...) beside this.
Access opens the code window.
The event procedure code will need to look like this:


Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtFindArtist) Then
strWhere = strWhere & "([Artist] Like """ & Me.txtFindArtist & "*"")
AND "
End If

If Not IsNull(Me.txtFindTitle) Then
strWhere = strWhere & "([Title] Like """ & Me.txtFindTitle & "*"")
AND "
End If

'etc for other boxes.

'Remove the trailing " AND ", and apply filter.
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
MsgBox "No criteria"
End If
End Sub


The example assumes you are using a flat-file structure (i.e. you do not
have a table of Artists, related by ArtistID).