I could not get a combo box to display a list in a Search Form. My object is to select an artist's name and have his/her CDs listed on the subform through a qry. But I couldn't do it. When I tried to type an artist's name, I get an error message telling me the name does not exist in the list. I was successful in getting data by typing a CD title but couldn't get the Combo box for the Artist's search to work. Can someone please help?
Details follow:
Table 1: tblArtists
ArtistID - Auto (PK)
ArtistName- Text
Table2: tblCDDetails
RecordID - Auto (PK)
RecordingTitle - Text
ArtistID - FK
Table 3: tblzNull
ItemID - Auto (PK)
ItemName - Text
Query 1: qryCD
RecordingID
RecordingTitle
ArtistID
ArtistName
Main Form 1: frmFIND
Text box 1 = txtCDTitle (Unbound)
Combo Box 1 = cboArtist (Unbound) but has this code under Row Source
Expand|Select|Wrap|Line Numbers
- SELECT 0 as ArtistID, "<ALL>" as ArtitstName FROM tblzNull UNION ALL SELECT tblArtists.ArtistID, tblArtists.ArtistName FROM tblArtists
- ORDER BYArtistName;
RecordingID
RecordingTitle
ArtistName
Command Button 1: cmdFIND
Expand|Select|Wrap|Line Numbers
- Private Sub cmdFIND_Click()
- ' Update the record source
- Me.sfrmCD.Form.RecordSource = "SELECT * FROM qryCD " & BuildFilter
- ' Requery the subform
- Me.sfrmCD.Requery
- End Sub
- Private Sub Form_Load()
- ' Clear the search form
- cmdCLEAR_Click
- End Sub
- Private Function BuildFilter() As Variant
- Dim varWhere As Variant
- Dim varItem As Variant
- Dim intIndex As Integer
- varWhere = Null ' Main filter
- ' Check for LIKE CD Title
- If Me.txtCDTitle > "" Then
- varWhere = varWhere & "[RecordingTitle] LIKE """ & Me.txtCDTitle & "*"" AND "
- End If
- ' Check for ArtistID
- If Me.cboArtist > 0 Then
- varWhere = varWhere & "[ArtistName] = " & Me.cboArtist & " AND "
- End If
- ' Check if there is a filter to return...
- If IsNull(varWhere) Then
- varWhere = ""
- Else
- varWhere = "WHERE " & varWhere
- ' strip off last "AND" in the filter
- If Right(varWhere, 5) = " AND " Then
- varWhere = Left(varWhere, Len(varWhere) - 5)
- End If
- End If
- BuildFilter = varWhere
- End Function