Pkey Search not working

[HLCruz via AccessMonster.com]

I have a search text field in the header of my form that I've used in many
other instances to search but for some reason my code wasn't working today.
I've messed around with it a little this morning and seemed to have made it
worse ... it must be one of those days ...

Maybe a fresh set of eyes would catch my mistake? Thanks to anyone who will
look ...

Private Sub txtIDSearch_AfterUpdate()
' Find the record that matches the control.
On Error GoTo ProcError
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MailingID] = """ & Str(Nz(Me![txtIDSearch], 0)) & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ExitProc:
Exit Sub
ProcError:
MsgBox "Error - Key Not Found"
Resume ExitProc

End Sub

Private Sub txtIDSearch_Enter()

' Find the record that matches the control.

On Error GoTo ProcError
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MailingID] = """ & Str(Nz(Me![txtIDSearch], 0)) & """"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

ExitProc:
Exit Sub

ProcError:
MsgBox "Error - Key not found"
Resume ExitProc


End Sub

 

[Bob Cutler]

I would change

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

to

If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

The NoMatch property was designed to be used with Find operations.

 

[Klatuu]

What data type is [MailingID] ? As written, it is for a text data type, but
ususally when I see ID on the end of a field name, I expect a numeric field,
usually a long integer, but that's just me.

My I show you an alternative way to do this:

Private Sub txtIDSearch_AfterUpdate()
' Find the record that matches the control.
On Error GoTo ProcError

With Me.RecordsetClone
.FindFirst "[MailingID] = """ & Str(Nz(Me![txtIDSearch], 0)) & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

I have an aversion to one line If statements.

 

[HLCruz via AccessMonster.com]

It is a numeric field ... will your alternative code work on only a text
field as well? Is the Str function causing my problem?

Thanks so much, I appreciate it.

What data type is [MailingID] ? As written, it is for a text data type, but
ususally when I see ID on the end of a field name, I expect a numeric field,
usually a long integer, but that's just me.

My I show you an alternative way to do this:

Private Sub txtIDSearch_AfterUpdate()
' Find the record that matches the control.
On Error GoTo ProcError

With Me.RecordsetClone
.FindFirst "[MailingID] = """ & Str(Nz(Me![txtIDSearch], 0)) & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

I have an aversion to one line If statements.

I have a search text field in the header of my form that I've used in many
other instances to search but for some reason my code wasn't working today.

[quoted text clipped - 40 lines]

End Sub

 

[Klatuu]

Yes, the Str function is a problem. You are trying to compare a number to text.
It will work with any data type, it just requires the correct syntax.
Here is the syntax for a numeric field"

With Me.RecordsetClone
.FindFirst "[MailingID] = " & Nz(Me![txtIDSearch], 0)
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Now, I am suspicious of searching for a 0 if the control is Null.
--
Dave Hargis, Microsoft Access MVP

It is a numeric field ... will your alternative code work on only a text
field as well? Is the Str function causing my problem?

Thanks so much, I appreciate it.

What data type is [MailingID] ? As written, it is for a text data type, but
ususally when I see ID on the end of a field name, I expect a numeric field,
usually a long integer, but that's just me.

My I show you an alternative way to do this:

Private Sub txtIDSearch_AfterUpdate()
' Find the record that matches the control.
On Error GoTo ProcError

With Me.RecordsetClone
.FindFirst "[MailingID] = """ & Str(Nz(Me![txtIDSearch], 0)) & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

I have an aversion to one line If statements.

I have a search text field in the header of my form that I've used in many
other instances to search but for some reason my code wasn't working today.

[quoted text clipped - 40 lines]

End Sub