Access Search Engine Using VB

I've got a real simple database in Access 97, two tables. One table I'd like to search on. This table is basically a library catalogue: author, title, subject, publisher, etc.

What I've got is a text box on a form next to a search button. (The form isn't attached to any table or query or anything.) I've gotten it so that when you type something into the box and click the search button, it passes the information to a VB function (called DoSearch()) and (for now) simply displays the text in a MsgBox, just to prove the information is actually being passed.

I'm new to working with VB and Access, and this is what I'd like to do:
I'd like to have the function go through all the fields for each record in the database and pick out any records that have any field matching (using the SQL statment "LIKE") the search text. In other words, a search on "Alaska" would bring up a book with the title "The Alaskan Frontier" and another book with the publisher "Alaska State University". How the results are displayed (via a query, form, report) is irrelevant. I just want to get this search working and quickly!

This is some code I got that is supposed to search ONE field in a database:

Function DoSearch(SearchText)

Dim db As Database
Dim rec As Recordset
Dim strSQL As String
Dim strMatches As String
Dim intCounter As Integer

strSQL = "SELECT * FROM Library WHERE Publisher = " & SearchText
Set db = CurrentDb()
Set rec = db.OpenRecordset(strSQL)

Do Until rec.EOF
    strMatches = strMatches & Chr$(10) & rec!ID
    rec.MoveNext
Loop

intCounter = rec.RecordCount

Select Case intCounter
    Case 0
        MsgBox "No records matched your search string: " & SearchText
    Case 1
        MsgBox "The following record matched your search string '" & SearchText & "':" & Chr$(10) & strMatches
    Case Else
        MsgBox "The following " & intCounter & " records matched your search string '" & SearchText & "'" & Chr$(10) & strMatches
End Select

rec.Close
       
End Function

Unfortunately, this results in run-time error 3061: Too few parameters. Expected 1. Note that the LIKE statement is not even in use, as that also results in the same error.

I could really use some help getting this thing up and running fast. I haven't been able to find anything of help in either books or on the internet, so anything would be appreciated!
punkerAsked:
Who is Participating?
 
paaskyConnect With a Mentor Commented:
I found the reason.. actually I should have taken care of it in my code. There are spaces in field names so they need brackets [field name] in the beginning and the end of them. In my example code all the field names were like "fieldname" so the it worked fine.

Change this line in the form code:

strWHERE = strWHERE & .Fields(b).Name & strCondition & Chr(34) & Me.txtSearch & Chr(34) & " OR " 
           
to

strWHERE = strWHERE & "[" & .Fields(b).Name & "]" & strCondition & Chr(34) & Me.txtSearch & Chr(34) & " OR "

and you get my code working in your own database application.

Hope this helps,
Paasky
0
 
paaskyCommented:
Hello punker,

Here's a sample code for you. It creates SQL and the records matching are shown in a list box (lstBox).

Private Sub B_Find_Click()
    Dim rst As Recordset
    Dim lMatches As Long
    Dim strSELECT As String
    Dim strWHERE As String
    Dim strCondition As String
    Dim b As Byte
   
    strSELECT = "SELECT * FROM library WHERE "
   
    ' Search with wildcards? If possible, search with exact word
    If InStr(1, Me.txtSearch, "*") > 0 Or _
       InStr(1, Me.txtSearch, "?") Then
        strCondition = " LIKE "
    Else
        strCondition = " = "
    End If
   
    Set rst = CurrentDb.OpenRecordset("library")
    With rst
        For b = 0 To .Fields.Count - 1
            ' search all the text type fields
            If .Fields(b).Type = dbText Then
                strWHERE = strWHERE & .Fields(b).Name & strCondition & Chr(34) & Me.txtSearch & Chr(34) & " AND"
            End If
        Next
    End With
    set rst = Nothing

    strWHERE = strWHERE & " 1 = 1 "
    Debug.Print
    Me.lstBox.RowSource = strSELECT & strWHERE
    Me.txtMatchFound = Me.lstBox.ListCount
   
End Sub

Regards,
Paasky
0
 
paaskyCommented:
Note that you can use this function with just a little modification with any table or query. Just modify the value of string strSELECT.

Note also, that if the wildcard characters * or ? are not used in search text box (strSearch), the query is build using equal comparison (=).

Hope this helps,
Paasky
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
paaskyCommented:
Note that you can use this function with just a little modification with any table or query. Just modify the value of string strSELECT.

Note also, that if the wildcard characters * or ? are not used in search text box (strSearch), the query is build using equal comparison (=).

Hope this helps,
Paasky
0
 
paaskyCommented:
sorry echo (netlag). Remember also to change "library" in recordset opening to the table name where the fields are looped.
0
 
punkerAuthor Commented:
With your code, I'm receiving the error: "Invalid use of the keyword 'Me'". For the life of me I can't figure out what "Me" is supposed to do. Maybe I'm missing the point entirely?
0
 
paaskyCommented:
I have implemented that example code in form's button (B_Find). Me.xxx are form control's (txtSearch and txtMatchFound are textboxes, lstBox is a listbox).

paasky

0
 
paaskyCommented:
(so Me = currentform)
0
 
clockwatcherCommented:
The reason the code that you originally posted is asking for a parameter is that you didn't enclose your search criteria in quotes.

Instead of this:

strSQL = "SELECT * FROM Library WHERE Publisher = " & SearchText

You should have used this:

strSQL = "SELECT * FROM Library WHERE Publisher = '" & SearchText & "'"
0
 
clockwatcherCommented:
It's hard to read that-- here it is spaced out (Don't used the spaced out version.  It's just easier to see the symbols.)

strSQL = "SELECT * FROM Library WHERE Publisher = ' " & SearchText & " ' "
0
 
punkerAuthor Commented:
I'll work on this, but I'm a bit confused, since I don't know VB all that well. There aren't any list boxes on my form, and I have no idea what to do with txtMatchFound.

If anyone else has any suggestions, please add them.
0
 
punkerAuthor Commented:
>strSQL = "SELECT * FROM Library WHERE Publisher = ' " & SearchText & " ' " 

I tried this a long while ago and it didn't work either.
0
 
paaskyCommented:
punker,

where do you want your query result to see? In listbox? On report?

txtMatchFound textbox is only used to show many hits the query returned. If you like you can show the "records found" with a message box.

I created you a sample database you can see how I use the code in the form. You can download it from http://www.iwn.fi/paasky/pub/search.mdb

Paasky

PS. I also noticed a bug in my previous example code - there should be OR instead of AND. That's fixed in my sample database.

Here's corrected code:

....
....
                strWHERE = strWHERE & .Fields(b).Name & strCondition & Chr(34) & Me.txtSearch & Chr(34) & " OR "
            End If
        Next
    End With
    strWHERE = Left(strWHERE, Len(strWHERE) - 3)
....    
....
0
 
paaskyCommented:
punker, there should not be any spaces after '

also if there are '-characters in field value your criteria won't work. That's the reason why I use chr(34) = ".

0
 
punkerAuthor Commented:
Thanks paasky for the sample database, it has helped so much! One question: Does it matter if I have indexed fields? That seems to be the only notable difference between my database and your database, and yet when I use the search form in mine, it returns nothing into the listbox, and if I search on something I know does not exist in the database, the MatchFound field always reads 1 record found.

Still working the bugs out...

0
 
paaskyCommented:
punker, I tested my search form and got value 0 into "MatchFound" field if the number of hits was zero, did you get result 1 with my sample database or your own? How does the indexing changed the search result? They shouldn't effect anyway...

Note also that the keyword is searched only from text type fields, not memo fields.

If you like to change that, modify this line from

If .Fields(b).Type = dbText Then
               
to

If .Fields(b).Type = dbText or .Fields(b).type = dbMemo Then


If you like, send me your database (or little sample of it if it's very big) and I check what's causing the problems there.

regards,
Paasky
0
 
punkerAuthor Commented:
No, that's what's confusing me. It works fine in your database but not in mine. No matter what I search on, the search result is always 1, and it will not display any results. All my fields, except for the Key field are text, so that shouldn't be a problem either.

I admit to being an idiot when it comes to VB, but this is ridiculous! =)
0
 
paaskyCommented:
Well, these thing are sometimes difficult and it takes time learn them well. Copy the code from my example database, not from the first example code I posted here.

You could either send me your database (apaasky@hotmail.com) or add the following line before End Sub and when you run the search, write the message down and post it here. Is your search table name "library"?

   ...
   Msgbox strSELECT & strWHERE

End Sub


Paasky
0
 
punkerAuthor Commented:
Adjusted points to 250
0
 
punkerAuthor Commented:
I thought you deserved a few extra points for putting up with me.

Thanks so much for your help!
0
 
paaskyCommented:
Thank you very much. ;-)

I'm glad I could assist you punker.

Good luck with your project!
Paasky
0
All Courses

From novice to tech pro — start learning today.