Solved

Access Search Engine Using VB

Posted on 2000-03-06
21
222 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 250 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

839 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