POP-up Form to Search By

  • Thread starter Thread starter Warren
  • Start date Start date
W

Warren

I would like to incorporate a pop-up form and enter
search
My tables Are Structured As Follows:
Click to expand...
RECORDINGS TABLE: MUSIC CATEGORY TABLE
RecordingsID MusicCategoryID
RecordingTitle MusicCategory
RecordingArtistId
Click to expand...
MusicCAtegoryID ARTIST TABLE
MusicFormat ArtistID
Notes Artist Name
Click to expand...
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
 
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 "
 
Back
Top