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?
17 6943
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. - 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.
Sorry, you may have to use the % operator with LIKE instead of *.
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!
@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.
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. - 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!
@QueenKirsty
Found it, QueenKirsty. The following code, when executed, will: - Look at the Search String that the User entered into a Text Box, in this case txtSearchString.
- 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'.
- The code then searches every single Field in every single Record in every single Table to see if it exists somewhere within the Field.
- If a Match is found, the results are printed to the Immediate Window in the following Format:
- A Running Match Number
- The Record Number (Absolute) in the Table where the Match was found
- Table Name
- Field Name in Table where Match was found
- The actual Found Value containing the Search String
- 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.
- 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
- OUTPUT
- 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
- P.S. - The results could have just as easily been written to a Table, File, List Box (not recommended), etc.
Certainly much easier to display only the field where the value is found, rather than the entire record, if you can tolerate that.
@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).
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 :)
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,
@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: - For each iteration of the TableDefs Collection, find out what the Primary Key is for that Table.
- At each String Search match, record the value of the Primary Key instead of the Record Number.
- The Dump at each Match would now consist of:
- Match Number
- Primary Key Value (instead of Record Number)
- Table Name
- Field Name
- Value in Match Field
- Of course, this logic would have some flaws also, but I am genuinely interested in any comment(s) you may have on this approach.
- Logic #3 - don't have it at this time! (LOL)!
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!!! :)
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
@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.
@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,
@FishVal
Excellent point, FishVal! Definitely thinking 'Outside-the-Box'! Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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.
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
| |