473,320 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

"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?
Jul 14 '09 #1
17 6943
ChipR
1,287 Expert 1GB
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.
Expand|Select|Wrap|Line Numbers
  1. For each Table
  2. strSQL = "SELECT * FROM " & CurrentTable & " WHERE "
  3.   For each Field
  4.     strSQL = strSQL & CurrentField _
  5.              & " LIKE *" & strSeachString & "* OR "
  6.   Next
  7.   'Take off the last "OR" or & "False"
  8.   set records = db.OpenRecordSet(strSQL)
  9.   For each record
  10.     add to a list box or something to show on the form?
  11.   Next
  12. 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.
Jul 14 '09 #2
ChipR
1,287 Expert 1GB
Sorry, you may have to use the % operator with LIKE instead of *.
Jul 14 '09 #3
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!
Jul 14 '09 #4
ADezii
8,834 Expert 8TB
@QueenKirsty
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.
Jul 14 '09 #5
@ADezii
Perfect! Thanks!!!
Jul 14 '09 #6
ChipR
1,287 Expert 1GB
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.
Expand|Select|Wrap|Line Numbers
  1. intFieldCount = 0
  2. For each fld in db.TableDefs(CurrentTable)
  3.   intFieldCount = intFieldCount + 1
  4. Next fld
  5. strNewItem = ""
  6. For i = 0 to (intFieldCount-1) 'I think field index starts at 0?
  7.   strNewItem = strNewItem & records.Fields(i) & ";"
  8. Next
  9. myListBox.AddItem (strNewItem)
Hopefully ADezii has a cleaner solution though!
Jul 14 '09 #7
ADezii
8,834 Expert 8TB
@QueenKirsty
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_CHARS, 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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdSearch_Click()
    2. On Error GoTo Err_cmdSearch_Click
    3. Dim tdf As DAO.TableDef
    4. Dim MyDB As Database
    5. Dim MyRS As Recordset
    6. Dim intNumOfFields As Integer
    7. Dim intCounter As Integer
    8. Dim varSearchString As Variant
    9. Dim intMatchCounter As Integer
    10. Const conMIN_NUM_OF_CHARS As Integer = 4
    11.  
    12. varSearchString = Me![txtSearchString]
    13.  
    14. 'Let's not get ridiculous, need at least 4 Characters
    15. If Len(varSearchString) < conMIN_NUM_OF_CHARS Or IsNull(varSearchString) Then Exit Sub
    16.  
    17. Set MyDB = CurrentDb
    18.  
    19. DoCmd.Hourglass True
    20.  
    21. Debug.Print "The String [" & varSearchString & "] has been found in the following locations:"
    22. Debug.Print "******************************************************************************************"
    23. Debug.Print
    24.  
    25. For Each tdf In CurrentDb.TableDefs
    26.   'ignore System Tables
    27.   If Not tdf.Name Like "MSys*" Or Not tdf.Name Like "MSys*" Then
    28.     Set MyRS = MyDB.OpenRecordset(tdf.Name, dbOpenDynaset)
    29.       intNumOfFields = MyRS.Fields.Count
    30.         For intCounter = 0 To intNumOfFields - 1
    31.           Do While Not MyRS.EOF
    32.             If InStr(MyRS.Fields(intCounter).Value, varSearchString) > 0 Then
    33.              intMatchCounter = intMatchCounter + 1
    34.               Debug.Print "Match #" & Format$(intMatchCounter, "0000") & " | " & _
    35.                           "Rec Num: " & Format$(MyRS.AbsolutePosition + 1, "0000") & " | " & _
    36.                           "Table: " & tdf.Name & " | " & "Field: " & _
    37.                            MyRS.Fields(intCounter).Name & " | Value: " & _
    38.                            MyRS.Fields(intCounter).Value
    39.             End If
    40.             MyRS.MoveNext
    41.           Loop
    42.           MyRS.MoveFirst
    43.         Next
    44.   End If
    45. Next
    46.  
    47. DoCmd.Hourglass False
    48.  
    49. MyRS.Close
    50. Set MyRS = Nothing
    51.  
    52.  
    53. Exit_cmdSearch_Click:
    54.   Exit Sub
    55.  
    56. Err_cmdSearch_Click:
    57.   MsgBox Err.Description, vbExclamation, "Error in cmdSearch_Click()"
    58.   DoCmd.Hourglass False
    59.   If Not MyRS Is Nothing Then
    60.     MyRS.Close
    61.     Set MyRS = Nothing
    62.   End If
    63.     Resume Exit_cmdSearch_Click
    64. End Sub
  6. OUTPUT
    Expand|Select|Wrap|Line Numbers
    1. The String [sales] has been found in the following locations:
    2. ******************************************************************************************
    3.  
    4. Match #0001 | Rec Num: 0009 | Table: Categories | Field: CategoryName | Value: Sales Trinkets
    5. Match #0002 | Rec Num: 0001 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    6. Match #0003 | Rec Num: 0004 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    7. Match #0004 | Rec Num: 0006 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    8. Match #0005 | Rec Num: 0011 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    9. Match #0006 | Rec Num: 0012 | Table: Customers | Field: ContactTitle | Value: Sales Agent
    10. Match #0007 | Rec Num: 0015 | Table: Customers | Field: ContactTitle | Value: Sales Associate
    11. Match #0008 | Rec Num: 0016 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    12. Match #0009 | Rec Num: 0019 | Table: Customers | Field: ContactTitle | Value: Sales Agent
    13. Match #0010 | Rec Num: 0020 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    14. Match #0011 | Rec Num: 0023 | Table: Customers | Field: ContactTitle | Value: Assistant Sales Agent
    15. Match #0012 | Rec Num: 0027 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    16. Match #0013 | Rec Num: 0028 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    17. Match #0014 | Rec Num: 0030 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    18. Match #0015 | Rec Num: 0031 | Table: Customers | Field: ContactTitle | Value: Sales Associate
    19. Match #0016 | Rec Num: 0035 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    20. Match #0017 | Rec Num: 0036 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    21. Match #0018 | Rec Num: 0037 | Table: Customers | Field: ContactTitle | Value: Sales Associate
    22. Match #0019 | Rec Num: 0039 | Table: Customers | Field: ContactTitle | Value: Sales Associate
    23. Match #0020 | Rec Num: 0040 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    24. Match #0021 | Rec Num: 0041 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    25. Match #0022 | Rec Num: 0044 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    26. Match #0023 | Rec Num: 0048 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    27. Match #0024 | Rec Num: 0050 | Table: Customers | Field: ContactTitle | Value: Sales Agent
    28. Match #0025 | Rec Num: 0053 | Table: Customers | Field: ContactTitle | Value: Sales Associate
    29. Match #0026 | Rec Num: 0054 | Table: Customers | Field: ContactTitle | Value: Sales Agent
    30. Match #0027 | Rec Num: 0055 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    31. Match #0028 | Rec Num: 0058 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    32. Match #0029 | Rec Num: 0059 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    33. Match #0030 | Rec Num: 0060 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    34. Match #0031 | Rec Num: 0064 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    35. Match #0032 | Rec Num: 0065 | Table: Customers | Field: ContactTitle | Value: Assistant Sales Representative
    36. Match #0033 | Rec Num: 0066 | Table: Customers | Field: ContactTitle | Value: Sales Associate
    37. Match #0034 | Rec Num: 0067 | Table: Customers | Field: ContactTitle | Value: Assistant Sales Agent
    38. Match #0035 | Rec Num: 0068 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    39. Match #0036 | Rec Num: 0071 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    40. Match #0037 | Rec Num: 0072 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    41. Match #0038 | Rec Num: 0075 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    42. Match #0039 | Rec Num: 0081 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    43. Match #0040 | Rec Num: 0082 | Table: Customers | Field: ContactTitle | Value: Sales Associate
    44. Match #0041 | Rec Num: 0083 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    45. Match #0042 | Rec Num: 0084 | Table: Customers | Field: ContactTitle | Value: Sales Agent
    46. Match #0043 | Rec Num: 0086 | Table: Customers | Field: ContactTitle | Value: Sales Representative
    47. Match #0044 | Rec Num: 0088 | Table: Customers | Field: ContactTitle | Value: Sales Manager
    48. Match #0045 | Rec Num: 0001 | Table: Employees | Field: Title | Value: Sales Representative
    49. Match #0046 | Rec Num: 0002 | Table: Employees | Field: Title | Value: Vice President, Sales
    50. Match #0047 | Rec Num: 0003 | Table: Employees | Field: Title | Value: Sales Representative
    51. Match #0048 | Rec Num: 0004 | Table: Employees | Field: Title | Value: Sales Representative
    52. Match #0049 | Rec Num: 0005 | Table: Employees | Field: Title | Value: Sales Manager
    53. Match #0050 | Rec Num: 0006 | Table: Employees | Field: Title | Value: Sales Representative
    54. Match #0051 | Rec Num: 0007 | Table: Employees | Field: Title | Value: Sales Representative
    55. Match #0052 | Rec Num: 0008 | Table: Employees | Field: Title | Value: Inside Sales Coordinator
    56. Match #0053 | Rec Num: 0009 | Table: Employees | Field: Title | Value: Sales Representative
    57. Match #0054 | Rec Num: 0002 | Table: Employees | Field: Notes | Value: REMOVED for brevity
    58. Match #0055 | Rec Num: 0003 | Table: Employees | Field: Notes | Value: REMOVED for brevity
    59. Match #0056 | Rec Num: 0005 | Table: Employees | Field: Notes | Value: REMOVED for brevity
    60. Match #0057 | Rec Num: 0006 | Table: Employees | Field: Notes | Value: REMOVED for brevity
    61. Match #0058 | Rec Num: 0078 | Table: Products | Field: ProductName | Value: Test Sales Record
    62. Match #0059 | Rec Num: 0004 | Table: Shippers | Field: CompanyName | Value: Test Sales Shipper
    63. Match #0060 | Rec Num: 0030 | Table: Suppliers | Field: CompanyName | Value: Test Sales Supplier
    64. Match #0061 | Rec Num: 0003 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
    65. Match #0062 | Rec Num: 0008 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
    66. Match #0063 | Rec Num: 0009 | Table: Suppliers | Field: ContactTitle | Value: Sales Agent
    67. Match #0064 | Rec Num: 0011 | Table: Suppliers | Field: ContactTitle | Value: Sales Manager
    68. Match #0065 | Rec Num: 0014 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
    69. Match #0066 | Rec Num: 0017 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
    70. Match #0067 | Rec Num: 0018 | Table: Suppliers | Field: ContactTitle | Value: Sales Manager
    71. Match #0068 | Rec Num: 0021 | Table: Suppliers | Field: ContactTitle | Value: Sales Manager
    72. Match #0069 | Rec Num: 0024 | Table: Suppliers | Field: ContactTitle | Value: Sales Representative
    73. Match #0070 | Rec Num: 0027 | Table: Suppliers | Field: ContactTitle | Value: Sales Manager
    74. 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.
Jul 14 '09 #8
ChipR
1,287 Expert 1GB
Certainly much easier to display only the field where the value is found, rather than the entire record, if you can tolerate that.
Jul 14 '09 #9
ADezii
8,834 Expert 8TB
@ChipR
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).
Jul 14 '09 #10
ChipR
1,287 Expert 1GB
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 :)
Jul 14 '09 #11
mshmyob
904 Expert 512MB
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,
Jul 14 '09 #12
ADezii
8,834 Expert 8TB
@mshmyob
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)!
Jul 14 '09 #13
Thanks guys, all very useful. I have been pointed to the following sample db that works pretty much the way you were describing.

http://www.hitechcoach.com/index.php...d=11&Itemid=28

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!!! :)
Jul 15 '09 #14
FishVal
2,653 Expert 2GB
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
Jul 15 '09 #15
@FishVal
That is a very good point. What I have actually done is created a facility to identify the tables that ought to be searched. I have populated this myself in the first instance and have set up a way of allowing the admin user to add more tables to this as needed. It also allows the user to search a subset of tables of their choosing.
Jul 15 '09 #16
mshmyob
904 Expert 512MB
@ADezii
Super duper AND top secret on top of that, WOW!!!! I always thought you were holding out on us now I know for certain.

Using PKs is much better, I always like using the PK as the lookup.

and don't worry about your 3rd logic - Fish has taken care of that.

cheers,
Jul 15 '09 #17
ADezii
8,834 Expert 8TB
@FishVal
Excellent point, FishVal! Definitely thinking 'Outside-the-Box'!
Jul 15 '09 #18

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Alastair | last post by:
Hello guys, I've been building a search facility for an intranet site I'm part of developing and we've been building a search engine using Index Server. It mostly works, however there have been...
87
by: ziliath | last post by:
I recently tried out the Google "top coder" contest, as a C++ coder. I noticed immediately that they expected me to know STL. To which I say, what the fuck?! I may be missing something, but at...
5
by: junky_fellow | last post by:
Each time i submit some pattern to "google", it shows search took 0.XX seconds for exploring millions of web pages. When i look for efficient ways of searching a string, they always say compare...
2
by: Phin | last post by:
Hi, I would like to have the "google suggests" functionality on a web page like it is here: http://www.google.com/webhp?complete=1&hl=en I have downloaded the AJAX .NET wrapper...
8
by: DQ dont quit | last post by:
I'm currently working on a ASP.Net / C# / SQL 2000 project that involves the entering of keywords, that a web user enters, and then searching MSWord documents for those words. This information...
15
by: Sri Ragaventhirar | last post by:
char *s = "Google" Where this "Google" will be stored? Why it can't be changed? like s='h'; -this is giving error please explain this.
0
by: passion | last post by:
"Specialized Search Engines" along with Google Search Capability (2 in 1): http://specialized-search-engines.blogspot.com/ Billions of websites are available on the web and plenty of extremely...
4
by: Stef Mientki | last post by:
hello, In a program I want to download (updated) files from google code (not the svn section). I could find a python script to upload files, but not for downloading. Anyone has a hint or a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.