POP-up Form to Search By

[Warren]

I would like to incorporate a pop-up form and enter
search

My tables Are Structured As Follows:

RECORDINGS TABLE: MUSIC CATEGORY TABLE
RecordingsID MusicCategoryID

RecordingTitle MusicCategory
RecordingArtistId

MusicCAtegoryID ARTIST TABLE
MusicFormat ArtistID

Notes Artist Name

NumberofTracks

TRACKS TABLE:
TRACKID
TrackNumber
TrackTitle
TrackLength
TrackTempo
TrackSpecialty
ArtistID
I Would Like to search by the following fields:
Recording Artist
Music Category
Recording Title
Track Tempo
Track Specialty
Track Title
I Was thinking that maybe I could use a set-up like
the "DateRange" form that pops up in a few of MS examples,
but I am stumped at how to code it with the number of
search criteria I have.
Any help would be GREATLY APPRECIATED
Thank You in Adavance

 

[Allen Browne]

The basic idea is to create a report based on a query that shows everything.
Then use the WhereCondition of the OpenReport action so it opens limited to
just the ones the user has chosen. The WhereCondition is built up by
concatenating the values together from the boxes where the user made an
entry.

The code below illustrates what to use in the Event Procedure of a command
button named "cmdPreview" that opens a preview of your report:

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

If Not IsNull(Me.cboArtistID) Then
strWhere = strWhere & "([ArtistID] = " & _
Me.cboArtistID & ") AND "
End If
If Not IsNull(Me.cboCategoryID) Then
strWhere = strWhere & "([CategoryID] = " & _
Me.cboCategoryID & ") AND "
End If
'etc for your other boxes.

'Remove trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End Sub

Note: You need additional quotes if the field being filtered is text.
For example, if the cboCategoryID were text, you would use:
strWhere = strWhere & "([CategoryID] = """ & _
Me.cboCategoryID & """) AND "