Microsoft Access Office VB VBA Help and Examples

Creating Search Forms

Although Access contains some great find functions it really doesn't cater for searching memo fields and returning scored hits. The following example shows you how to return a result for a search for text give the number of word hits. It relies on very little code and a few queries (All of which should be reasonably quick). The results return a count of the number of hits.

Create a new form not based on any recordset. Put a text box on it and call it SearchText. Create a button on the form and call it Search. Create 8 textboxes called word1 to word8. Bring up the code window and paste the following in.

Private Sub Search_Click()
      On Error GoTo Err_search_Click    
      Dim Spacepos As Long            'Position of next space in search string    
      Dim Lengthstr As Long           'Length of search string    
      Dim Texttemp As String          'Search String'   
      Dim x as integer
      Texttemp = SearchText.Value      'set texttemp variable to value of entire text box    
      If Texttemp ="" Then  Exit Sub   'If no text exit
      Texttemp = Texttemp & Space(8)'Add  8 spaces to ensure string does not stop short
      Lengthstr = Len(Texttemp)            'Get  length of  string for 
      for x = 1 to 8        
            Spacepos = InStr(1,Texttemp,  "      ", 1) 'Get next space 
            Select Case x 
                  Case 1
                        Word1.Value = (Left(Texttemp, (Spacepos - 1)))
                  Case 2
                        Word2.Value = (Left(Texttemp, (Spacepos - 1)))
                  Case 3
                        Word3.Value = (Left(Texttemp, (Spacepos - 1)))
                  Case 4
                         Word4.Value = (Left(Texttemp, (Spacepos - 1)))
                  Case 5
                         Word5.Value = (Left(Texttemp, (Spacepos - 1)))
                  Case 6
                         Word6.Value = (Left(Texttemp, (Spacepos - 1)))
                  Case 7
                         Word7.Value = (Left(Texttemp, (Spacepos - 1)))
                  Case 8
                         Word8.Value = (Left(Texttemp, (Spacepos - 1)))
            End select 
            Texttemp = Right(Texttemp, (Lengthstr -  Spacepos)) 'Delete that word and space 
            Lengthstr =  Len(Texttemp) 'Get length  of string 
      Exit Sub  
      MsgBox Err.Description 
      Resume Exit_search_Click 
End Sub

Save the form as frm_Search and close the form. Create a new query drag in the table you are searching and drag in only the unique id for the record and the field you want to search for text. Click off the show checkbox for the field you are searching for text (This query will now just return the unique id). In the criteria for the field you are searching  paste the following (This is the compare statement)

Like ("*" & [Forms]![frm_Search]![Word1] & "*") Or Like ([Forms]![frm_Search]![Word1] & "*") Or Like ("*" & [Forms]![frm_Search]![Word1]) Or Like [Forms]![frm_Search]![Word1]

Save the query as qry_SearchWord1  close the query and copy it 7 times creating qry_SearchWord1 through to qry_SearchWord8. In each of the queries replace Word1 in the line above with Word2 through to Word8 respectively for each query.

Create another query. Make it a union query by selecting Query -> SQL Specific -> Union  Then paste the following SQL into the resulting window.

SELECT qry_SearchWord1.* 
FROM qry_SearchWord1
UNION ALL SELECT qry_SearchWord2.*
FROM qry_SearchWord2
UNION ALL SELECT qry_SearchWord3.*
FROM qry_SearchWord3
UNION ALL SELECT qry_SearchWord4.*
FROM qry_SearchWord4
UNION ALL SELECT qry_SearchWord5.*
FROM qry_SearchWord5
UNION ALL SELECT qry_SearchWord6.*
FROM qry_SearchWord6
UNION ALL SELECT qry_SearchWord7.*
FROM qry_SearchWord7
UNION ALL SELECT qry_SearchWord8.*
FROM qry_SearchWord8

Save the query as qry_SearchUnion. Close it create a new query. Bring in the qry_SearchUnion query. Drag the uniqueid field into the fields twice. Choose from the menu view Totals. Under one of the fields total should be Group By and the other should be Count. Save the query as qry_SearchResults and close it.

Run the search form. You should be able to type in the words you want to search for seperated by spaces, and hit the search button. It should open the query results for you. 

The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.