Solved

Access Search Engine Using VB

Posted on 2000-03-06
21
218 Views
Last Modified: 2008-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:

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!
0
Comment
Question by:punker
  • 12
  • 7
  • 2
21 Comments
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
 
LVL 10

Expert Comment

by:paasky
Comment Utility
sorry echo (netlag). Remember also to change "library" in recordset opening to the table name where the fields are looped.
0
 

Author Comment

by:punker
Comment Utility
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
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
 
LVL 10

Expert Comment

by:paasky
Comment Utility
(so Me = currentform)
0
 
LVL 25

Expert Comment

by:clockwatcher
Comment Utility
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
 
LVL 25

Expert Comment

by:clockwatcher
Comment Utility
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
 

Author Comment

by:punker
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:punker
Comment Utility
>strSQL = "SELECT * FROM Library WHERE Publisher = ' " & SearchText & " ' "

I tried this a long while ago and it didn't work either.
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
 

Author Comment

by:punker
Comment Utility
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
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
 

Author Comment

by:punker
Comment Utility
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
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
 
LVL 10

Accepted Solution

by:
paasky earned 250 total points
Comment Utility
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
 

Author Comment

by:punker
Comment Utility
Adjusted points to 250
0
 

Author Comment

by:punker
Comment Utility
I thought you deserved a few extra points for putting up with me.

Thanks so much for your help!
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
Thank you very much. ;-)

I'm glad I could assist you punker.

Good luck with your project!
Paasky
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now