Unanswered: Can I implement the "binoculars" function in VB in Access 97?
I would like to provide a user with a functionality similar to the binoculars, or "Edit, Find" dialog box, allowing them to search any field for a string. I am using Access 97.
I would like to do this so I can provide a list of found records, preferably with an indication of which field the string has been found in. This would improve on the dialog box which requires the user to keep clicking "find next" as it jumps through the records. Basically, I want to provide a "find all" button!
However, I can't see how to do it easily. The Seek method only allows 14 fields to be searched. The FindNext method requires a field name in the criteria. I can of course open a recordset from a query that has a criterion on every field, but then I can't identify which field the string appears in. In my head I can see this turning into a huge amount of code for something that clearly already exists in Access. So am I missing something simple?
Anyway, it's been a while since using Acc97, so I can not answer the interface question.
However, using a recordset object and iterating through the fields collection would be a "code light" way of doing your search, but it would be slow.
How about using the recordset to iterate though the fields collection to create a query string that searches for a match in any field, then implement the query you just created (as a query or on the form's filter property, or, if the string is too big, apply it to the underlying query)?
Acc97 may have a limit to the number of characters in the SQL property, but within reason, it will work, and it will be rather light on code (not to mention it will work again and again with out recoding).
If you want the user to be able to edit it, then save the query def as an actual query and open it for the user or apply it to a form.
' On the fly code (meaning typos exist and it's not exactly correct)
Dim rdD As DAO.RecordSet
Dim fld As DAO.Field
Dim sVal As String ' the string you are looking for
Dim sSQL As String
Dim blStarted As Boolean
sVal = Chr(34) & Nz(Me.txtSearchValue,"*") & chr(34)
blStarted = False
sSQL = ""
Set rsD = CurrentDB.OpenRecordset("Select Top 1 From Query1")
' or Set rsD = Me.RecordsetClone ' to use the form's recordsource
For Each fld in rsD.Fields
If fld.Type = dbText then
If blStarted Then sSQL = sSQL & " OR "
blStarted = True
sSQL = sSQL & "[" & fld.Name & "] = " & sVal
Set fld = Nothing
Set rsD = Nothing
'Now, sSQL has the Where Clause to find any record with a Text field matching the value you specify. Use it on the form's filter property, use it on a query, etc.
I know of a way that is only mildly more complicated that the first:
Using a similar technique as described before, cycle through the recordset's fields to create a query string that creates a big UNION query. The union query will be 3 columns wide: Record Key, Field Name, Field Value. The query will take the multi column table and reduce it to a single column.
Then, filter that by the string of interest and you will get a list of records (by key) with a field (by name) that matches your value.
For example, lets say my table is: redID, Text1, Text2, Text3
The resulting query string should look something like:
Select recID, "Text1" As recFld, Text1 As recDat
Union Select recID, "Text2" As recFld, Text2 As recDat
Union Select recID, "Text3" As recFld, Text3 As recDat
Where recDat = "SomeString";
I wasn't implying anything negative - sorry if you took offense!
I didn't know of a way to do it, so I was searching several things myself and shared the results. As indicated in the links, you can implement the search function (as the original question asked) but you can't get the results in code, at least not directly.
Glad you found a way to do it in code. If the code is different than the method I attached, would you mind sharing? It gives options to others who have a similar question.