Record Search (1 Viewer)

NearImpossible

Registered User.
Local time
Yesterday, 19:24
Joined
Jul 12, 2019
Messages
189
Currently I have some code setup to search a table for a specified string, based on the search criteria of either Serial Number, Asset Tag or Name.

What I would like to do is eliminate the need to select the search criteria and be able to just enter the search string and hit enter to have it search all 3 columns.

Any help is appreciated or if you know of a better way to do this, please let me know.

Code:
Private Sub SearchEquip_Click()
    'Checks to see if Search Criteria was selected
        If [Search] = "Select" Then
            MsgBox "Please select search criteria", , "Equipment Search"
            Exit Sub
        End If
        
    'Checks to see if there is a seach string
        If Nz(txt) = "" Then
            MsgBox "Please enter a search value", , "Equipment Search"
            Exit Sub
            
        Else
            Dim Rs As DAO.Recordset
            ' Searches Facility equipment by Serial Number
                If [Search] = "Serial Number" Then
                    If Not IsNull(Me![txt]) Then
                        Set Rs = Me.RecordsetClone
                        Rs.FindFirst "[Serial Number] Like '*" & Me.txt & "*'"
                        Me.[Serial Number].SetFocus
                            If Not Rs.EOF Then Me.Bookmark = Rs.Bookmark
                                Let txt = ""
                            Else
                                Exit Sub
                    End If
            
            ' Searches Facility equipment by Asset Tag
                ElseIf [Search] = "Asset Tag" Then
                    If Not IsNull(Me![txt]) Then
                        Set Rs = Me.RecordsetClone
                        Rs.FindFirst "[Asset Tag] Like '*" & Me.txt & "*'"
                        Me.[Asset Tag].SetFocus
                        If Not Rs.EOF Then Me.Bookmark = Rs.Bookmark
                            Let txt = ""
                        Else
                            Exit Sub
                    End If
            
            ' Searches Facility equipment by Device Name
                ElseIf [Search] = "Name" Then
                    If Not IsNull(Me![txt]) Then
                        Set Rs = Me.RecordsetClone
                        Rs.FindFirst "[Name] Like '*" & Me.txt & "*'"
                        Me.[DevName].SetFocus
                        If Not Rs.EOF Then Me.Bookmark = Rs.Bookmark
                            Let txt = ""
                        Else
                            Exit Sub
                        End If
                    End If
             
        ' Resets the Search criteria
                [Search] = "Select"
                
        End If
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:24
Joined
Oct 29, 2018
Messages
13,158
Hi. Quick question first... Would you be willing to filter the form to show the search results or continue to just "find" the first match? Just curious...
 

arnelgp

error reading drive A:
Local time
Today, 09:24
Joined
May 7, 2009
Messages
10,838
here is a modified version. you may do away with your combo/listbox.
Code:
Private Sub SearchEquip_Click()
    Static WillContinue As Boolean
        
    'Checks to see if there is a seach string
    If Nz(txt) = "" Then
        MsgBox "Please enter a search value", , "Equipment Search"
        [txt].SetFocus
        Exit Sub
            
    Else
        With Me.RecordsetClone
            If Not WillContinue Then
                .FindFirst _
                    "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[Name] Like '*" & Me.txt & "*'"
            Else
                .FindNext _
                    "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[Name] Like '*" & Me.txt & "*'"
            End If
            WillContinue = Not (.NoMatch)
            If WillContinue Then
                Me.Bookmark = .Bookmark
            End If
        End With
            
    End If
End Sub
 
Last edited:

NearImpossible

Registered User.
Local time
Yesterday, 19:24
Joined
Jul 12, 2019
Messages
189
Hi. Quick question first... Would you be willing to filter the form to show the search results or continue to just "find" the first match? Just curious...

For this one I just want it to go to that record, I do however have another form for a different purpose that filters based on the results, but i'm doing that with an access query.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:24
Joined
Oct 29, 2018
Messages
13,158
For this one I just want it to go to that record, I do however have another form for a different purpose that filters based on the results.
Sounds good. Thanks for answering my question. Please try out Arnel's suggestion and let us know how it goes. Cheers!
 

NearImpossible

Registered User.
Local time
Yesterday, 19:24
Joined
Jul 12, 2019
Messages
189
here is a modified version. you may do away with your combo/listbox.
Code:
Private Sub SearchEquip_Click()
    Static WillContinue As Boolean
    Dim Rs As DAO.Recordset
        
    'Checks to see if there is a seach string
    If Nz(txt) = "" Then
        MsgBox "Please enter a search value", , "Equipment Search"
        [txt].SetFocus
        Exit Sub
            
    Else
        With Me.RecordsetClone
            If Not WillContinue Then
                .FindFirst _
                    "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[Name] Like '*" & Me.txt & "*'"
            Else
                .FindNext _
                    "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[Name] Like '*" & Me.txt & "*'"
            End If
            WillContinue = Not (.NoMatch)
            If WillContinue Then
                Me.Bookmark = .Bookmark
            End If
        End With
            
    End If
End Sub


Can I do a set focus with this setup? Currently once it matches, it selects that field i.e.

Code:
"[Serial Number] Like '*" & Me.txt & "*' me.[Serial Number].SetFocus OR " & _
"[Asset Tag] Like '*" & Me.txt & "*' me.[Asset Tag].SetFocus OR " & _
"[Name] Like '*" & Me.txt & "*'"me.[Name].SetFocus

I'm traveling and not near my database to test and see for the next 6 hours
 

arnelgp

error reading drive A:
Local time
Today, 09:24
Joined
May 7, 2009
Messages
10,838
Code:
            If WillContinue Then
                Me.Bookmark = .Bookmark
                [COLOR="Blue"]Me.[Name].SetFocus[/COLOR]
            End If
 

NearImpossible

Registered User.
Local time
Yesterday, 19:24
Joined
Jul 12, 2019
Messages
189
So I tried arnels first posting and no matter what I enter, if it exists or not, it just selects the name field on the first record.

I have these 3 columns setup so no duplicates can exist so I commented out the following and still get the same results

Code:
Else
                .FindNext _
                    "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[Name] Like '*" & Me.txt & "*'"

If I add in the following, it does the same thing whether the S/N or Asset Tag exists or not, however if I enter one of the names, it highlights that record and then immediately moves to the name field on first record
Code:
Me.[Name].SetFocus
 

arnelgp

error reading drive A:
Local time
Today, 09:24
Joined
May 7, 2009
Messages
10,838
first it will find whatever the substring you type on the fields.
if it finds it on the first record it will be highlighted.
you need to press the "search" button again to search for the Next record.
but since you remove the Else, it will not find it but still go on and find the
First record.
 

NearImpossible

Registered User.
Local time
Yesterday, 19:24
Joined
Jul 12, 2019
Messages
189
Got it working!!!

I was testing your code in the button on click, however I had my old code on the txt after update so they were conflicting.

I placed your above code, to include the .findnext, in the txt after update and call it from the button on click this way it immediately searches for a match for those that like to hit the Enter Key after making an entry, and they can click the button if its not the exact match, but a partial match, to continue the search.


I did notice that if it is on the last matching record, I have to press the button twice to start the search over at the first record.

Is there a way to have it automatically start over with the first record, or display a message box that all records have been searched?

Also where can I insert a message box to display "No Match" if it doesn't find it?

Thanks again for your help !!!
 
Last edited:

NearImpossible

Registered User.
Local time
Yesterday, 19:24
Joined
Jul 12, 2019
Messages
189
Figured out how to get the "not found message", now just need some guidance on how to display a message once all records have been searched with the .FindNext.

Here's what I have so far
Code:
    Static WillContinue As Boolean
    Dim Rs As DAO.Recordset

    'Checks to see if there is a search string
    If Nz(txt) = "" Then
        MsgBox "Please enter a search value", , "Equipment Search"
        [txt].SetFocus
        Exit Sub
    End If
    
[COLOR="Red"]'Checks to see if value exists
    If DCount("*", "dbo_FacilityEquipment", "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[EquipmentName] Like '*" & Me.txt & "*'") = 0 Then
        MsgBox "No device found matching " & Me.txt, , "Device Not Found"
        Exit Sub
    Else[/COLOR]
        With Me.RecordsetClone
            If Not WillContinue Then
                .FindFirst _
                    "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[EquipmentName] Like '*" & Me.txt & "*'"
            Else
                .FindNext _
                    "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[EquipmentName] Like '*" & Me.txt & "*'"
            End If
            WillContinue = Not (.NoMatch)
            If WillContinue Then
                Me.Bookmark = .Bookmark
            End If
        End With
            
    End If
    
 ' Sets current record to match the Equipment ID when the record is slected for highlighting purposes
    Me.txtCurrentRecord = [EquipmentID]
 
Last edited:

NearImpossible

Registered User.
Local time
Yesterday, 19:24
Joined
Jul 12, 2019
Messages
189
Figured it out

Code:
            WillContinue = Not (.NoMatch)
            If WillContinue Then
                Me.Bookmark = .Bookmark
[COLOR="Red"]            Else
                MsgBox "All records have been searched"[/COLOR]
            End If
        End With

Thanks again for all your help !!
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:24
Joined
Oct 29, 2018
Messages
13,158
Hi. Congratulations! Good luck with your project.
 

arnelgp

error reading drive A:
Local time
Today, 09:24
Joined
May 7, 2009
Messages
10,838
maybe somewhere here:
Code:
    Static WillContinue As Boolean
    Dim Rs As DAO.Recordset

    'Checks to see if there is a search string
    If Nz(txt) = "" Then
        MsgBox "Please enter a search value", , "Equipment Search"
        [txt].SetFocus
        Exit Sub
    End If
    
'Checks to see if value exists
    If DCount("*", "dbo_FacilityEquipment", "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[EquipmentName] Like '*" & Me.txt & "*'") = 0 Then
        MsgBox "No device found matching " & Me.txt, , "Device Not Found"
        Exit Sub
    Else
        With Me.RecordsetClone
            If Not WillContinue Then
                .FindFirst _
                    "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[EquipmentName] Like '*" & Me.txt & "*'"
            Else
                .FindNext _
                    "[Serial Number] Like '*" & Me.txt & "*' OR " & _
                    "[Asset Tag] Like '*" & Me.txt & "*' OR " & _
                    "[EquipmentName] Like '*" & Me.txt & "*'"
               If (.NoMatch) Then
                   'show msg
                   Msgbox "No more matching record found."
                   'move to first record
                   .MoveFirst
                   Me.bookmark = .Bookmark
               End If
            End If
            WillContinue = Not (.NoMatch)
            If WillContinue Then
                Me.Bookmark = .Bookmark
            End If
        End With
            
    End If
    
 ' Sets current record to match the Equipment ID when the record is slected for highlighting purposes
    Me.txtCurrentRecord = [EquipmentID]
 

Users who are viewing this thread

Top Bottom