Cannot get Recordset.FindNext to work

[alexrubio]

Hello All,

I had this working and for some reason I broke it somewhere...

Basically I created a button to search for records based on the status of a checkbox and data in a textbox. The code searches for the first item found and prompts if the search needs to continue in order to find additional records.

This is the code:

If IsNull(txtAssetTagSearch) = True Then

    MsgBox "Please Enter Tag or Partial Tag to Search.", vbOKOnly + vbInformation, "AMS - Asset Tag Search"

    Exit Sub

End If

If Me.chkActiveRecs = True Then

       Me.Recordset.FindFirst "AssetTag Like '*" & txtAssetTagSearch & "*' And [Active] = True"

    If Me.Recordset.NoMatch Then

        MsgBox "No Record Found", vbOKOnly + vbInformation, "AMS - Asset Tag Search"

        Me!txtAssetTagSearch = Null

        Else

            Do While Not Me.Recordset.NoMatch

                If MsgBox("Continue Searching?", vbYesNo + vbQuestion, "AMS - Asset Tag Search") = vbNo Then

                    Me!txtAssetTagSearch = Null

                    Exit Sub

                End If

                Me.Recordset.FindNext "AsstTag Like '*" & txtAssetTagSearch & "*' And [Active] = True"

                    Me!txtAssetTagSearch = Null

            Loop

    End If

If I enter a bogus record, it works fine by saying "No records found", the first record is always found, the problem lies when the user clicks yes, no additional records are pulling up...

Your help is greatly appreciated...

Alex

[zmbd]

While we're pursuing your code for the gremlin(s):
1) what was the last thing you did?
2) put a STOP command in at before line-1. When the debugger opens, [F8] to step thru your code and see what is happening at the conditionals.

[zmbd]

Comment out line-33 of your code.
That should fix the issue... and line can be removed once you know things are working.

I also highly recommend building strings in a variable instead of directly in the function.

For example in lines 11 and 31 you build the string in the find() function. If you build the string first, then you can debug for errors.

'other variables.
   '
   Dim strFindThis as String
'other variables.
   '
   If IsNull(txtAssetTagSearch) = True Then
      MsgBox "Please Enter Tag or Partial Tag to Search.", _
         vbOKOnly + vbInformation, "AMS - Asset Tag Search"
      Exit Sub
   End If
   '
   If Me.chkActiveRecs = True Then
'
'>Build it here:
      strFindThis = "AssetTag Like '*" & _
         txtAssetTagSearch & "*' And [Active] = True"
'
'>Need to debug? then
'>Debug.Print strFindThis
'>press <ctrl+g> to open the immediate window to find the resolved string!  
'
'>Now use it here
      Me.Recordset.FindFirst strFindThis
      If Me.Recordset.NoMatch Then
         MsgBox "No Record Found", _
            vbOKOnly + vbInformation,
            "AMS - Asset Tag Search"
         Me!txtAssetTagSearch = Null
      Else
         Do While Not Me.Recordset.NoMatch
            If MsgBox("Continue Searching?", _
               vbYesNo + vbQuestion, _
               "AMS - Asset Tag Search") = vbNo Then
               Me!txtAssetTagSearch = Null
               Exit Sub
            End If
'>and again here
            Me.Recordset.FindNext strFindTHis
         Loop
      End If
   End If

Notice... if you had built the string first then the loop would not have noticed the change made to the form in the posted code at line 33

[alexrubio]

Comment out line-33 of your code.
That should fix the issue... and line can be removed once you know things are working.

Thanks for your reply zmbd, I had tried removing line 33 prior to posting and nothing happens.

I know the code is executing because I placed a messagebox before line 33 and it does display it, I just think for some reason the FindFirst command is not being executed and/or is not finding the next record...

UPDATE: I have the same code on another part of the database that I think its still working, will check it out and post back my findings...

[zmbd]

That tells me that the search may not be resolving as expected or that the record pointer may not be at the beginning of the record-set when you start your search.

[alexrubio]

That tells me that the search may not be resolving as expected or that the record pointer may not be at the beginning of the record-set when you start your search.

I have the same exact code working on another part of the database, the only difference is that the line 33 you had me comment out, is located below the "Loop" tag AND that code searches text only, this one is searching numbers, I have a third that searches numbers and text, but that one is not working as well...

[alexrubio]

Ugh!!! Found it! Typo on line #31!

That's what happens when you are looking at the code day in and day out, starts looking the same...

Thanks for the help ZMBD! Will look into your suggestion as well, probably the better way of doing it...

[zmbd]

Glad you found it ... those typo-Gremlins catch the best of us };-)

Looking close, I see the difference between lines 11 and 31 in OP. I was looking at the quotes and ampersands etc... easy miss and one reason why I do build that string first, especially if it's to be re-used throughout the code.

Glad we could help... as always here when you need us :0)

[alexrubio]

Glad you found it ... those typo-Gremlins catch the best of us };-)

Looking close, I see the difference between lines 11 and 31 in OP. I was looking at the quotes and ampersands etc... easy miss and one reason why I do build that string first, especially if it's to be re-used throughout the code.

Glad we could help... as always here when you need us :0)

Thanks Again zmbd, greatly appreciate it!