Results 1 to 8 of 8
  1. #1
    Drak is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    22

    Using a recordset to find ANY records

    I'm trying to replace access built-in search feature. I would like to find a record in the table, but searching in ANY FIELD. I have a table with about 10 fields. (Name, lastname, phone number etc), and I would like the record to search each field for a specific string,



    I have this so far, but it doesn't work correctly..
    Code:
    Public Function FindRecord(FormToSearch As Form) As String
        
        Dim vResult As Variant
        
        vResult = InputBox("Type the criteria you wish to search for", "Find")
        
        If IsNullString(vResult) Then
            MsgBoxWrapper "You must enter a string to search for"
        Else
        
            Dim daoRecordSet As DAO.Recordset
            Dim daoField As DAO.Field
            Set daoRecordSet = FormToSearch.RecordsetClone
            
            daoRecordSet.MoveFirst
            Do While Not daoRecordSet.EOF
                For Each daoField In daoRecordSet.Fields
                    If InStr(1, daoField.Value, vResult) Then
                        FormToSearch.Bookmark = daoRecordSet
                    End If
                Next daoField
            Loop
            
        End If
            
    End Function

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You need to move to the next record within the loop.
    If Not daoRecordSet.EOF Then daoRecordSet.MoveNext
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Drak is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    22
    Quote Originally Posted by June7 View Post
    You need to move to the next record within the loop.
    If Not daoRecordSet.EOF Then daoRecordSet.MoveNext
    Thanks so much. How do I get the recordset to start from the bookmark, instead of the first record? So I can use a "find next" function.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Is this what you want? Instead of MoveFirst:
    daoRecordSet.FindFirst "fieldname='" & vResult & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Drak is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    22
    Quote Originally Posted by June7 View Post
    Is this what you want? Instead of MoveFirst:
    daoRecordSet.FindFirst "fieldname='" & vResult & "'"
    My issue is that I don't persay know the "fieldname". I would like it to search all the fields. Which is does currently, and pulls up the first record it finds, but there is more than one record containing let's say, the string "michigan". So I would like it to continue form the last record found, and go to the next record containing "michigan" in any field.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How many fields are involved? The FindFirst can have multiple criteria.

    Why would same value be in multiple fields. Sounds like data is not normalized.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Drak is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    22
    Well there is a field called "state" and there is multiple users that have "michigan" entered. But if the user wants to search for lets say a zip code, again multiple users in the table have the same zip code.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-24-2011, 08:01 AM
  2. Recordset not returning records
    By TinaCa in forum Programming
    Replies: 3
    Last Post: 08-03-2011, 09:26 AM
  3. Testing Recordset for Records
    By AccessGeek in forum Programming
    Replies: 5
    Last Post: 03-22-2011, 12:26 PM
  4. Find Records Query
    By sullyman in forum Programming
    Replies: 1
    Last Post: 10-28-2009, 08:49 AM
  5. select records in recordset
    By sksriharsha in forum Programming
    Replies: 3
    Last Post: 09-05-2009, 11:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums