rst.FindFirst - now it works, then it doesn't!

[patjones]

Hi all:

I have a form called frmChecks, with a textbox txtSearchBox. The After Update event for txtSearchBox is connected to code that searches for the inputted value in a linked table (tblEmployees):

Dim rst As DAO.Recordset
Dim strCriteria As String, errStr As String
 
strCriteria = "[tblEmployees].[fldEmplNum] Like '*" & Me!txtSearchBox.Value & "*'"
errStr = "Employee not found..."
 
Set rst = Me.RecordsetClone
 
rst.Requery
 
rst.FindFirst strCriteria
 
If rst.NoMatch Then
    MsgBox errStr, vbInformation, "Check Tracking System"
    rst.MoveFirst
End If
 
Me.Bookmark = rst.Bookmark
Me!cboCheckNum = rst!fldCheckNum
 
rst.Close
Set rst = Nothing
 

This works fantastically. Then, I have another form, frmAddChecks, with a text box txtEmplERN. This textbox is connected to nearly identical code for it's After Update event:

 
Dim rst As DAO.Recordset
Dim strCriteria As String
 
strCriteria = "[tblEmployees].[fldEmplNum] Like '*" & Me!txtEmplERN.Value & "*'"
 
Set rst = Me.RecordsetClone
 
rst.Requery
 
rst.FindFirst strCriteria
 
If rst.NoMatch Then 
     'Code to raise not found error
Else
     'Code to do some other stuff during normal execution
End If
 
rst.Close
Set rst = Nothing
 

Both forms have the exact same SQL query as a recordsource.

The problem is that the second set of code won't work! It always executes the error branch of the If statement, even though I type in values that are definitely in the table, and which are found by the first set of code. I just don't get it...

Any thoughts? Thank you.

[missinglinq]

Are txtSearchBox and txtEmplERN both formatted as the same datatype?

Linq ;0)>

[patjones]

Are txtSearchBox and txtEmplERN both formatted as the same datatype?

Linq ;0)>

Hi:

Thank you for your response. The formatting on these text boxes isn't really set. It's just open format because it's always a seven-digit text string that the user enters.

The reason I left the format open rather than making it a number is because sometimes that seven digit text string starts with one or more zeroes, and if you format something like that as a number then it cuts the zeroes off.

So, being that the code is the same in both places, it's hard for me to understand why it would behave differently. I do see what you're saying though; something has to be different somewhere...

[patjones]

Hi:

Thank you for your response. The formatting on these text boxes isn't really set. It's just open format because it's always a seven-digit text string that the user enters.

The reason I left the format open rather than making it a number is because sometimes that seven digit text string starts with one or more zeroes, and if you format something like that as a number then it cuts the zeroes off.

So, being that the code is the same in both places, it's hard for me to understand why it would behave differently. I do see what you're saying though; something has to be different somewhere...

I forgot to post here and point out that I solved this problem simply by creating a new form, pasting all my controls and code over to it, and recompiling. Things worked fine. It seems like the original was somehow corrupt, if that's possible...

Anyway, thanks so much for your input!