Need Help with a Search Form in Access 2003

[MNNovice]

I am working on a database on my CD collection using Access 2003. Some of the table structures are given below.

Table 1 tblMusicCategory

Field 1: MusicCategoryID - Auto Number (PK)
Field 2: MusicCategory - text
Field 3: MusicCategoryAbbv - text

Table 2 tblArtists
Field 1 ArtistID - Auto Number (PK)
Field 2 ArtistName - text
Field 3 ArtistAbbv – text

Table 4 tbType

Field 1: TypeID - Auto Number (PK)
Field 2: Type - text


Table 4 tblCDDetails

Field 1: RecordingD - Auto Number (PK)
Field 2: SerialNumber - Text
Field 3: RecordingTitle - Text
Field 4: MusicCategoryID - FK
Field 5: ArtistID – FK
Field 6: TypeID – FK
Field7: LanguageID – FK

To enter CDs I use a form called frmCDDetails which is based on tblCDDetails .

I am trying to create a Search Form which can help me check whether a CD is already entered or not, eg.. To do this, I created a form called frmSearch where I have several “unbound” text boxes to enter search criteria (Artist name or Category or Title or Type etc). I added a ListBox to display the findings and a command button cmdFind to execute the search engine.

I added the following CODES to the “Click” event of the cmdFind button. But when I click on FIND I get the four blank columns with no data in it. I entered an Artist’s name or a CD Title that I know exist in the database. Still I get foul blank columns.

What am I doing wrong? Can someone please help? Please e-mail me with any questions: ** address removed as per rules **. Many thanks in advance.

Here are the CODES.

Private Sub cmdFind_Click()
    Dim strSQL As String
    Dim Criteria As String
 
    strSQL = "SELECT RecordingID as ID, RecordingTitle, CategoryID, TypeID, RecordingArtistID" & _
             "FROM tblCDDetails WHERE "
    If IsNull(Me.RecordingTitle) And _
       IsNull(Me.MusicCategoryID) And _
       IsNull(Me.TypeID) And _
       IsNull(Me.RecordingArtistID) Then
            MsgBox "Must Enter at least one value in " & _
                   "order to search database.", vbOKOnly
    Else
        If Not IsNull(Me.RecordingTitle) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND RecordingTitle = '" & Me.RecordingTitle & "'"
            Else
                Criteria = Criteria & "RecordingTitle = '" & Me.RecordingTitle & "'"
            End If
        End If
        If Not IsNull(Me.MusicCategoryID) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND MusicCategoryID = '" & Me.MusicCategoryID & "'"
            Else
                Criteria = Criteria & "MusicCategoryID = '" & Me.MusicCategoryID & "'"
            End If
        End If
        If Not IsNull(Me.TypeID) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND TypeID = '" & Me.TypeID & "'"
            Else
                Criteria = Criteria & "TypeID = '" & Me.TypeID & "'"
            End If
        End If
        If Not IsNull(Me.RecordingArtistID) Then
            If Len(Criteria) > 0 Then
                Criteria = Criteria & " AND RecordingArtistID = '" & Me.RecordingArtistID & "'"
            Else
                Criteria = Criteria & "RecordingArtistID = '" & Me.RecordingArtistID & "'"
            End If
        End If
 
        strSQL = strSQL & Criteria
        Me.ResultList.ColumnCount = 4
        Me.ResultList.BoundColumn = 1
        Me.ResultList.ColumnHeads = True
        Me.ResultList.ColumnWidths = "720;2160;1440;1440"
        Me.ResultList.RowSourceType = "Table/Query"
        Me.ResultList.RowSource = strSQL
        Me.ResultList.Requery
    End If
End Sub
 

[NaftoliGug]

Hi. I didn't notice anything wrong right away, but I'll tell you how I usually debug these types of problems.
Set a breakpoint in the _Click event after the entire string is composed. The first thing I would do is when the breakpoint is hit, go to the immediate window and print out the contents of the string:

? sqlString

(or whatever the string is called.
Examine the output and see if there's anything wrong with the sql statement.
If you don't notice anything wrong, I use a utility method I wrote that executes an SQL statement and prints the results in the immediate window, using CurrentDb.OpenRecordset and iterating over the records and then over each field and printing it out. It may be easier for you to create a query in access and copy/paste the generated SQL statement into SQL view, and see if it gives you an error when switching to Datasheet view.
However before going that far in many cases you can open the properties for the list box while the program is running, after the search has been executed - especially in Access 2007, in layout view. Then go to the row source and click the ... button. If you don't get an error right away see what you get in Datasheet view.

[MNNovice]

Thanks for your suggestions. I was able to solve my problem.

Hi. I didn't notice anything wrong right away, but I'll tell you how I usually debug these types of problems.
Set a breakpoint in the _Click event after the entire string is composed. The first thing I would do is when the breakpoint is hit, go to the immediate window and print out the contents of the string:

? sqlString

(or whatever the string is called.
Examine the output and see if there's anything wrong with the sql statement.
If you don't notice anything wrong, I use a utility method I wrote that executes an SQL statement and prints the results in the immediate window, using CurrentDb.OpenRecordset and iterating over the records and then over each field and printing it out. It may be easier for you to create a query in access and copy/paste the generated SQL statement into SQL view, and see if it gives you an error when switching to Datasheet view.
However before going that far in many cases you can open the properties for the list box while the program is running, after the search has been executed - especially in Access 2007, in layout view. Then go to the row source and click the ... button. If you don't get an error right away see what you get in Datasheet view.