Pkey Search not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HLCruz - 11 Apr 2008 17:11 GMT

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

Reply to this Message

Bob Cutler - 11 Apr 2008 18:23 GMT

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.

> 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

Reply to this Message

Klatuu - 11 Apr 2008 19:56 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> 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

Reply to this Message

HLCruz - 11 Apr 2008 20:39 GMT

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,
[quoted text clipped - 19 lines]
>>
>> End Sub

Reply to this Message

Klatuu - 11 Apr 2008 20:56 GMT

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.

Signature

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?
[quoted text clipped - 24 lines]
> >>
> >> End Sub

Reply to this Message