Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Search Engine Using VB

Posted on 2000-03-06
21
Medium Priority
?
227 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
ID: 2588191
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
ID: 2588216
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
ID: 2588225
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 10

Expert Comment

by:paasky
ID: 2588245
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
ID: 2588324
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
ID: 2588374
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
ID: 2588390
(so Me = currentform)
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 2588803
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
ID: 2588809
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
ID: 2588815
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
 

Author Comment

by:punker
ID: 2588833
>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
ID: 2588919
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
ID: 2588927
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
ID: 2591791
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
ID: 2592146
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
ID: 2592304
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
ID: 2592406
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 1000 total points
ID: 2592618
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
ID: 2597936
Adjusted points to 250
0
 

Author Comment

by:punker
ID: 2597940
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
ID: 2598040
Thank you very much. ;-)

I'm glad I could assist you punker.

Good luck with your project!
Paasky
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

879 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