Modify Record Finding Code 2

[lars7]

Hi there,

I have a form to search for staff by pay number but if there is no match the code I have will show the first record which I dont want.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Pay Number] = '" & Me![Text1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

I tried the following to give me a message box and the text boxes to go to new record.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Pay Number] = '" & Me![Text1] & "'"
If Not rs.EOF Then
MsgBox "cant find paynumber"
DoCmd.GoToRecord , , acNewRec
Forms!formJobDescriptions!FormStaffLookup!Text1.DefaultValue = ""
End If

but the message box always pops up even when the pay number is right. Can anyone help me modify it please.

 

[Golom]

Since you are using DAO, be aware that a "FindFirst" that doesn't find anything doesn't reposition to EOF as does the "Find" command in ADO.

It rather sets the "NoMatch" property so you need

    Dim rs As Object
    Set rs = Me.Recordset.Clone
        rs.FindFirst "[Pay Number] = '" & Me![Text1] & "'"
    If [red]rs.NoMatch[/red] Then
        MsgBox "Can't find paynumber"
        DoCmd.GoToRecord , , acNewRec
        Forms!formJobDescriptions!FormStaffLookup!Text1.DefaultValue = ""
    End If

 

[lars7]

Thanks Golam,
I wasn't aware of that, your code works great.

 

[lars7]

Hi Guys,

I thought this was working but it's not finding the record if it exists but only tells me if the record doesn't. Does anyone know why.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Pay Number] = '" & Me![Text1] & "'"
If rs.NoMatch Then
MsgBox "Can't find paynumber"
DoCmd.GoToRecord , , acNewRec
Forms!formJobDescriptions!FormStaffLookup!Text1.DefaultValue = ""
End If

 

[busol]

Make sure you have same types: If text1 is a string, make sure pay number is also a string, or convert to string with CStr()

rs.FindFirst "CStr([Pay Number]) = '" & Me![Text1] & "'"

 

[Zion7]

busul may be right???

But more importantly, you're not setting the bookmark!!!

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Pay Number] = '" & Me![Text1] & "'"
If rs.NoMatch Then
MsgBox "Can't find paynumber"
DoCmd.GoToRecord , , acNewRec
Forms!formJobDescriptions!FormStaffLookup!Text1.DefaultValue = ""
Else
Me.Bookmark = rs.Bookmark
End If

 

[lars7]

Thanks Guys for replying.

Zion your code works (for finding and not finding)

Cheers.