By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,207 Members | 1,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,207 IT Pros & Developers. It's quick & easy.

Need Help with a Search Form in Access 2003

100+
P: 418
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFind_Click()
  2.     Dim strSQL As String
  3.     Dim Criteria As String
  4.  
  5.     strSQL = "SELECT RecordingID as ID, RecordingTitle, CategoryID, TypeID, RecordingArtistID" & _
  6.              "FROM tblCDDetails WHERE "
  7.     If IsNull(Me.RecordingTitle) And _
  8.        IsNull(Me.MusicCategoryID) And _
  9.        IsNull(Me.TypeID) And _
  10.        IsNull(Me.RecordingArtistID) Then
  11.             MsgBox "Must Enter at least one value in " & _
  12.                    "order to search database.", vbOKOnly
  13.     Else
  14.         If Not IsNull(Me.RecordingTitle) Then
  15.             If Len(Criteria) > 0 Then
  16.                 Criteria = Criteria & " AND RecordingTitle = '" & Me.RecordingTitle & "'"
  17.             Else
  18.                 Criteria = Criteria & "RecordingTitle = '" & Me.RecordingTitle & "'"
  19.             End If
  20.         End If
  21.         If Not IsNull(Me.MusicCategoryID) Then
  22.             If Len(Criteria) > 0 Then
  23.                 Criteria = Criteria & " AND MusicCategoryID = '" & Me.MusicCategoryID & "'"
  24.             Else
  25.                 Criteria = Criteria & "MusicCategoryID = '" & Me.MusicCategoryID & "'"
  26.             End If
  27.         End If
  28.         If Not IsNull(Me.TypeID) Then
  29.             If Len(Criteria) > 0 Then
  30.                 Criteria = Criteria & " AND TypeID = '" & Me.TypeID & "'"
  31.             Else
  32.                 Criteria = Criteria & "TypeID = '" & Me.TypeID & "'"
  33.             End If
  34.         End If
  35.         If Not IsNull(Me.RecordingArtistID) Then
  36.             If Len(Criteria) > 0 Then
  37.                 Criteria = Criteria & " AND RecordingArtistID = '" & Me.RecordingArtistID & "'"
  38.             Else
  39.                 Criteria = Criteria & "RecordingArtistID = '" & Me.RecordingArtistID & "'"
  40.             End If
  41.         End If
  42.  
  43.         strSQL = strSQL & Criteria
  44.         Me.ResultList.ColumnCount = 4
  45.         Me.ResultList.BoundColumn = 1
  46.         Me.ResultList.ColumnHeads = True
  47.         Me.ResultList.ColumnWidths = "720;2160;1440;1440"
  48.         Me.ResultList.RowSourceType = "Table/Query"
  49.         Me.ResultList.RowSource = strSQL
  50.         Me.ResultList.Requery
  51.     End If
  52. End Sub
  53.  
Aug 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: 13
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:
Expand|Select|Wrap|Line Numbers
  1. ? 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.
Aug 28 '08 #2

100+
P: 418
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:
Expand|Select|Wrap|Line Numbers
  1. ? 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.
Aug 30 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.