"Google" style search function

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • QueenKirsty
    New Member
    • Jul 2009
    • 14

    "Google" style search function

    I have been asked to create a "google" style search functon for my SQL server 2000 db using and access 2003 (.adp) front end form. I would like to be able to enter something into a text box and have all the records from different tables (where that string occurs in the record) shown in a popup form, from which the user can select one of the records to go to (i.e. they will double click on the record and it will open the proper form for that table at that record).

    Any suggestions? Is this even possible in access?
  • ChipR
    Recognized Expert Top Contributor
    • Jul 2008
    • 1289

    #2
    I'd say it's possible, but not exactly easy. How comfortable are you with VBA? Here's some quick pseudocode for how I'd start.
    Code:
    For each Table
    strSQL = "SELECT * FROM " & CurrentTable & " WHERE "
      For each Field
        strSQL = strSQL & CurrentField _
                 & " LIKE *" & strSeachString & "* OR "
      Next
      'Take off the last "OR" or & "False"
      set records = db.OpenRecordSet(strSQL)
      For each record
        add to a list box or something to show on the form?
      Next
    Next
    The problem will be showing results from tables with different structures together. I'm thinking a list box with a number of columns equal to the number of fields in the table with the most fields.

    Comment

    • ChipR
      Recognized Expert Top Contributor
      • Jul 2008
      • 1289

      #3
      Sorry, you may have to use the % operator with LIKE instead of *.

      Comment

      • QueenKirsty
        New Member
        • Jul 2009
        • 14

        #4
        I think I get the idea. Cycle through each field in each table and add any matching records to a recordset?

        I though about this but didn't know how to concatenate a recordset from different tables with non-matching fields.

        Any more hints? :o) I have quite a lot of experience with VBA but am not a full-on expert!

        Comment

        • ADezii
          Recognized Expert Expert
          • Apr 2006
          • 8834

          #5
          Originally posted by QueenKirsty
          I have been asked to create a "google" style search functon for my SQL server 2000 db using and access 2003 (.adp) front end form. I would like to be able to enter something into a text box and have all the records from different tables (where that string occurs in the record) shown in a popup form, from which the user can select one of the records to go to (i.e. they will double click on the record and it will open the proper form for that table at that record).

          Any suggestions? Is this even possible in access?
          I wrote some code awhile ago that may/may not help you. It will search every Field in every Record of every Table for a Search String that the Users specifies in a Text Box. I'll look it up and get back to you.

          Comment

          • QueenKirsty
            New Member
            • Jul 2009
            • 14

            #6
            Originally posted by ADezii
            I wrote some code awhile ago that may/may not help you. It will search every Field in every Record of every Table for a Search String that the Users specifies in a Text Box. I'll look it up and get back to you.
            Perfect! Thanks!!!

            Comment

            • ChipR
              Recognized Expert Top Contributor
              • Jul 2008
              • 1289

              #7
              If you get a count of the fields in the table, you can add the record to a multi-column list box. This will work with different length records, since you don't have to have data in each column.
              Code:
              intFieldCount = 0
              For each fld in db.TableDefs(CurrentTable)
                intFieldCount = intFieldCount + 1
              Next fld
              strNewItem = ""
              For i = 0 to (intFieldCount-1) 'I think field index starts at 0?
                strNewItem = strNewItem & records.Fields(i) & ";"
              Next
              myListBox.AddItem (strNewItem)
              Hopefully ADezii has a cleaner solution though!

              Comment

              • ADezii
                Recognized Expert Expert
                • Apr 2006
                • 8834

                #8
                Originally posted by QueenKirsty
                I have been asked to create a "google" style search functon for my SQL server 2000 db using and access 2003 (.adp) front end form. I would like to be able to enter something into a text box and have all the records from different tables (where that string occurs in the record) shown in a popup form, from which the user can select one of the records to go to (i.e. they will double click on the record and it will open the proper form for that table at that record).

                Any suggestions? Is this even possible in access?
                Found it, QueenKirsty. The following code, when executed, will:
                1. Look at the Search String that the User entered into a Text Box, in this case txtSearchString .
                2. If the Length of the String is >= the length as specified in the Constant conMIN_NUM_OF_C HARS, the code falls through. You can change the value of the Constant if you so desire, it is just to prevent some ridiculous Search for a String such as 'as'.
                3. The code then searches every single Field in every single Record in every single Table to see if it exists somewhere within the Field.
                4. If a Match is found, the results are printed to the Immediate Window in the following Format:
                  1. A Running Match Number
                  2. The Record Number (Absolute) in the Table where the Match was found
                  3. Table Name
                  4. Field Name in Table where Match was found
                  5. The actual Found Value containing the Search String
                5. I'll post the code below, any Questions please feel free to ask. I ran the code against the Northwind Sample Database with some dummy entries added. The String to search for was 'Sales'. Matches in the [Notes] Field in the Employees Table were eliminated for the sake of brevity.
                  Code:
                  Private Sub cmdSearch_Click()
                  On Error GoTo Err_cmdSearch_Click
                  Dim tdf As DAO.TableDef
                  Dim MyDB As Database
                  Dim MyRS As Recordset
                  Dim intNumOfFields As Integer
                  Dim intCounter As Integer
                  Dim varSearchString As Variant
                  Dim intMatchCounter As Integer
                  Const conMIN_NUM_OF_CHARS As Integer = 4
                  
                  varSearchString = Me![txtSearchString]
                  
                  'Let's not get ridiculous, need at least 4 Characters
                  If Len(varSearchString) < conMIN_NUM_OF_CHARS Or IsNull(varSearchString) Then Exit Sub
                  
                  Set MyDB = CurrentDb
                  
                  DoCmd.Hourglass True
                  
                  Debug.Print "The String [" & varSearchString & "] has been found in the following locations:"
                  Debug.Print "******************************************************************************************"
                  Debug.Print
                  
                  For Each tdf In CurrentDb.TableDefs
                    'ignore System Tables
                    If Not tdf.Name Like "MSys*" Or Not tdf.Name Like "MSys*" Then
                      Set MyRS = MyDB.OpenRecordset(tdf.Name, dbOpenDynaset)
                        intNumOfFields = MyRS.Fields.Count
                          For intCounter = 0 To intNumOfFields - 1
                            Do While Not MyRS.EOF
                              If InStr(MyRS.Fields(intCounter).Value, varSearchString) > 0 Then
                               intMatchCounter = intMatchCounter + 1
                                Debug.Print "Match #" & Format$(intMatchCounter, "0000") & " | " & _
                                            "Rec Num: " & Format$(MyRS.AbsolutePosition + 1, "0000") & " | " & _
                                            "Table: " & tdf.Name & " | " & "Field: " & _
                                             MyRS.Fields(intCounter).Name & " | Value: " & _
                                             MyRS.Fields(intCounter).Value
                              End If
                              MyRS.MoveNext
                            Loop
                            MyRS.MoveFirst
                          Next
                    End If
                  Next
                  
                  DoCmd.Hourglass False
                  
                  MyRS.Close
                  Set MyRS = Nothing
                  
                  
                  Exit_cmdSearch_Click:
                    Exit Sub
                  
                  Err_cmdSearch_Click:
                    MsgBox Err.Description, vbExclamation, "Error in cmdSearch_Click()"
                    DoCmd.Hourglass False
                    If Not MyRS Is Nothing Then
                      MyRS.Close
                      Set MyRS = Nothing
                    End If
                      Resume Exit_cmdSearch_Click
                  End Sub
                6. OUTPUT
                  Code:
                  The String [sales] has been found in the following locations:
                  ******************************************************************************************
                  
                  Match #0001 | Rec Num: 0009 | Table: Categories | Field: CategoryName | Value: Sales Trinkets
                  Match #0002 | Rec Num: 0001 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0003 | Rec Num: 0004 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0004 | Rec Num: 0006 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0005 | Rec Num: 0011 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0006 | Rec Num: 0012 | Table: Customers | Field: ContactTitle | Value: Sales Agent
                  Match #0007 | Rec Num: 0015 | Table: Customers | Field: ContactTitle | Value: Sales Associate
                  Match #0008 | Rec Num: 0016 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0009 | Rec Num: 0019 | Table: Customers | Field: ContactTitle | Value: Sales Agent
                  Match #0010 | Rec Num: 0020 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0011 | Rec Num: 0023 | Table: Customers | Field: ContactTitle | Value: Assistant Sales Agent
                  Match #0012 | Rec Num: 0027 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0013 | Rec Num: 0028 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0014 | Rec Num: 0030 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0015 | Rec Num: 0031 | Table: Customers | Field: ContactTitle | Value: Sales Associate
                  Match #0016 | Rec Num: 0035 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0017 | Rec Num: 0036 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0018 | Rec Num: 0037 | Table: Customers | Field: ContactTitle | Value: Sales Associate
                  Match #0019 | Rec Num: 0039 | Table: Customers | Field: ContactTitle | Value: Sales Associate
                  Match #0020 | Rec Num: 0040 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0021 | Rec Num: 0041 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0022 | Rec Num: 0044 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0023 | Rec Num: 0048 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0024 | Rec Num: 0050 | Table: Customers | Field: ContactTitle | Value: Sales Agent
                  Match #0025 | Rec Num: 0053 | Table: Customers | Field: ContactTitle | Value: Sales Associate
                  Match #0026 | Rec Num: 0054 | Table: Customers | Field: ContactTitle | Value: Sales Agent
                  Match #0027 | Rec Num: 0055 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0028 | Rec Num: 0058 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0029 | Rec Num: 0059 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0030 | Rec Num: 0060 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0031 | Rec Num: 0064 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0032 | Rec Num: 0065 | Table: Customers | Field: ContactTitle | Value: Assistant Sales Representative
                  Match #0033 | Rec Num: 0066 | Table: Customers | Field: ContactTitle | Value: Sales Associate
                  Match #0034 | Rec Num: 0067 | Table: Customers | Field: ContactTitle | Value: Assistant Sales Agent
                  Match #0035 | Rec Num: 0068 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0036 | Rec Num: 0071 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0037 | Rec Num: 0072 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0038 | Rec Num: 0075 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0039 | Rec Num: 0081 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0040 | Rec Num: 0082 | Table: Customers | Field: ContactTitle | Value: Sales Associate
                  Match #0041 | Rec Num: 0083 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0042 | Rec Num: 0084 | Table: Customers | Field: ContactTitle | Value: Sales Agent
                  Match #0043 | Rec Num: 0086 | Table: Customers | Field: ContactTitle | Value: Sales Representative
                  Match #0044 | Rec Num: 0088 | Table: Customers | Field: ContactTitle | Value: Sales Manager
                  Match #0045 | Rec Num: 0001 | Table: Employees | Field: Title | Value: Sales Representative
                  Match #0046 | Rec Num: 0002 | Table: Employees | Field: Title | Value: Vice President, Sales
                  Match #0047 | Rec Num: 0003 | Table: Employees | Field: Title | Value: Sales Representative
                  Match #0048 | Rec Num: 0004 | Table: Employees | Field: Title | Value: Sales Representative
                  Match #0049 | Rec Num: 0005 | Table: Employees | Field: Title | Value: Sales Manager
                  Match #0050 | Rec Num: 0006 | Table: Employees | Field: Title | Value: Sales Representative
                  Match #0051 | Rec Num: 0007 | Table: Employees | Field: Title | Value: Sales Representative
                  Match #0052 | Rec Num: 0008 | Table: Employees | Field: Title | Value: Inside Sales Coordinator
                  Match #0053 | Rec Num: 0009 | Table: Employees | Field: Title | Value: Sales Representative
                  Match #0054 | Rec Num: 0002 | Table: Employees | Field: Notes | Value: REMOVED for brevity
                  Match #0055 | Rec Num: 0003 | Table: Employees | Field: Notes | Value: REMOVED for brevity
                  Match #0056 | Rec Num: 0005 | Table: Employees | Field: Notes | Value: REMOVED for brevity
                  Match #0057 | Rec Num: 0006 | Table: Employees | Field: Notes | Value: REMOVED for brevity
                  Match #0058 | Rec Num: 0078 | Table: Products | Field: ProductName | Value: Test Sales Record
                  Match #0059 | Rec Num: 0004 | Table: Shippers | Field: CompanyName | Value: Test Sales Shipper
                  Match #0060 | Rec Num: 0030 | Table: Suppliers | Field: CompanyName | Value: Test Sales Supplier
                  Match #0061 | Rec Num: 0003 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
                  Match #0062 | Rec Num: 0008 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
                  Match #0063 | Rec Num: 0009 | Table: Suppliers | Field: ContactTitle | Value: Sales Agent
                  Match #0064 | Rec Num: 0011 | Table: Suppliers | Field: ContactTitle | Value: Sales Manager
                  Match #0065 | Rec Num: 0014 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
                  Match #0066 | Rec Num: 0017 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
                  Match #0067 | Rec Num: 0018 | Table: Suppliers | Field: ContactTitle | Value: Sales Manager
                  Match #0068 | Rec Num: 0021 | Table: Suppliers | Field: ContactTitle | Value: Sales Manager
                  Match #0069 | Rec Num: 0024 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
                  Match #0070 | Rec Num: 0027 | Table: Suppliers | Field: ContactTitle | Value: Sales Manager
                  Match #0071 | Rec Num: 0028 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
                7. P.S. - The results could have just as easily been written to a Table, File, List Box (not recommended), etc.

                Comment

                • ChipR
                  Recognized Expert Top Contributor
                  • Jul 2008
                  • 1289

                  #9
                  Certainly much easier to display only the field where the value is found, rather than the entire record, if you can tolerate that.

                  Comment

                  • ADezii
                    Recognized Expert Expert
                    • Apr 2006
                    • 8834

                    #10
                    Originally posted by ChipR
                    Certainly much easier to display only the field where the value is found, rather than the entire record, if you can tolerate that.
                    You are 100% correct ChipR, but in the initial Post the OP needed some type of mechanism for actually accessing the Records containing the Found Matches. If this is still the case, you would need the Table Name, Field Name, and Record Number. I could of course be completely off track, in which case I went way overboard on this one! (LOL)!
                    have all the records from different tables (where that string occurs in the record) shown in a popup form, from which the user can select one of the records to go to (i.e. they will double click on the record and it will open the proper form for that table at that record).

                    Comment

                    • ChipR
                      Recognized Expert Top Contributor
                      • Jul 2008
                      • 1289

                      #11
                      No, I think you were right on. I meant that your match with a standard set of fields is much better than my method of trying to display all the fields in the record if any matched, as I expected :)

                      Comment

                      • mshmyob
                        Recognized Expert Contributor
                        • Jan 2008
                        • 903

                        #12
                        Nice solution ADezii as usual but I would like to point out to the OP that there is one slight flaw :).

                        The OP has indicated that they want an end user to click on the record and open a form related to that record. Keep in mind there are really no such things as record numbers in Access and if you open a form based on the selected record number generated by ADezii's code you will need to ORDER your records in the exact same way. Also if any records have been deleted or added after this code has been run and before the end user selects the record you will get the wrong record even if you have the exact same ORDER as ADezii used.

                        Maybe you could try using Bookmarks instead.

                        cheers,

                        Comment

                        • ADezii
                          Recognized Expert Expert
                          • Apr 2006
                          • 8834

                          #13
                          Originally posted by mshmyob
                          Nice solution ADezii as usual but I would like to point out to the OP that there is one slight flaw :).

                          The OP has indicated that they want an end user to click on the record and open a form related to that record. Keep in mind there are really no such things as record numbers in Access and if you open a form based on the selected record number generated by ADezii's code you will need to ORDER your records in the exact same way. Also if any records have been deleted or added after this code has been run and before the end user selects the record you will get the wrong record even if you have the exact same ORDER as ADezii used.

                          Maybe you could try using Bookmarks instead.

                          cheers,
                          Excellent point, mshmyob! I was actually aware of the flaws in my logic, but decided to use this code as a starting point anyway. Based on the assumption that all of the Tables have a Primary Key, my next and revised super duper top secret logic, would be as follows:
                          1. For each iteration of the TableDefs Collection, find out what the Primary Key is for that Table.
                          2. At each String Search match, record the value of the Primary Key instead of the Record Number.
                          3. The Dump at each Match would now consist of:
                            1. Match Number
                            2. Primary Key Value (instead of Record Number)
                            3. Table Name
                            4. Field Name
                            5. Value in Match Field
                          4. Of course, this logic would have some flaws also, but I am genuinely interested in any comment(s) you may have on this approach.
                          5. Logic #3 - don't have it at this time! (LOL)!

                          Comment

                          • QueenKirsty
                            New Member
                            • Jul 2009
                            • 14

                            #14
                            Thanks guys, all very useful. I have been pointed to the following sample db that works pretty much the way you were describing.



                            It assumes that the first field in each table is the PK (but it is in my case so that's OK) and gives me a semicolon delimlimited string. I can then use the PK and table name to open the correct form and fo the the record.

                            Thanks again for all the help!!! :)

                            Comment

                            • FishVal
                              Recognized Expert Specialist
                              • Jun 2007
                              • 2656

                              #15
                              Just a thought.

                              What about iterating Recordsources of available (or relevant) forms instead of iterating available tables?

                              This way you:
                              • doesn't get useless hits which couldn't be opened via existing forms
                              • search in context of "records" displayed by form which in relational database are mostly expected not to be records of particular table but a records of table join or filtered table
                              • .....
                              • PROFIT


                              Regards,
                              Fish

                              Comment

                              Working...