Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Posts
    59

    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?

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Access 97? It's 2010!

    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.

    Code:
    ' 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
         End If
    Next
    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.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Apr 2003
    Posts
    59
    Thanks, I've got that far but I can't get it to work out which field contains the search string.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    oh, tricky indeed!

    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";
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Apr 2003
    Posts
    59
    OK, so say I'm using Access 2003. Would anybody then have an idea how to do this? Can you bring up the dialog box and trap the results somehow?

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    A quick Google search reveals:

    RunCommand acCmdFind

    Open form with search dialog

    Docmd.DoMenuItem

    Access 2000:
    DoMenuItem Method

    Access 2003:
    DoMenuItem Method [Access 2003 VBA Language Reference]
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  7. #7
    Join Date
    Apr 2003
    Posts
    59
    I'm sorry if you got the impression I was asking a stupid question that could be easily googled. Trapping the results was the important part, not displaying the dialog box.

    Anyway I have now implemented my own code for this so I don't need an answer any more. Thanks everyone.

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    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.

    Have a good day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •