Access Search Engine Using VB
Posted on 2000-03-06
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:
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
intCounter = rec.RecordCount
Select Case intCounter
MsgBox "No records matched your search string: " & SearchText
MsgBox "The following record matched your search string '" & SearchText & "':" & Chr$(10) & strMatches
MsgBox "The following " & intCounter & " records matched your search string '" & SearchText & "'" & Chr$(10) & strMatches
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!