Code For - If Record Is Not Found Then MsBox etc. 2

[dominicdunmow]

I use the following code for a find record commandbutton on one a form.

I would like a message box to appear if the requested record is not found. Can anyone help me with the addition I need to make to this:-

Private Sub Find_Click()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Opp_ID_Search], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[RoyVidar]

[tt]rs.FindFirst "[ID] = " & Str(Nz(Me![Opp_ID_Search], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
else
msgbox "record not found"
end if[/tt]

Roy-Vidar

 

[Crowley16]

how's about

if not ... then
...
else
...
endif

--------------------
Procrastinate Now!

 

[jtseltmann]

Modify the line below to.

If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Record Not Found",vbExclamation,"Validation Error!"
End If

is that what you are looking for?

 

[Crowley16]

lol, well, there u go...

--------------------
Procrastinate Now!

 

[dominicdunmow]

Ah,

I've just realised the Else lines that you have both suggested are the same as what I ofirignally used, but it gives me the mesgbox everytime, even if the record does exist.

Any clues?

 

[RoyVidar]

The code presented here, should either move to correct record or provide the message box. If it does both, then I'd guess the routine runs twice, for some reason. Try using the after update event of the control, in stead of the on click event.

Roy-Vidar

 

[Crowley16]

ah, yes, forgot...

you are cycling through all the records, so of course it's get to .eof...

there's many ways around this, but simplest for you would probably to set a variable to record if it's found or not:

dim fnd as Boolean
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Opp_ID_Search], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
fnd = true
else
fnd = false
end if

if fnd = true then
msgbox "record not found"
endif

of course, there's easier ways of doing this but would involve changing more of your original code

--------------------
Procrastinate Now!

 

[RoyVidar]

Ouch - scratch the after update, I thougth you where doing this from a combo, where the after update vs click could be an explanation of being run twice vs once. Don't know, similar works as it should on my setup.

Roy-Vidar

 

[dominicdunmow]

I think this may be caused by an On Active event.

 

[Crowley16]

fyi, personally, I use something like:

dim rs as ...
set rs ...("select blah from blah where blah = blah")

this way, I can use

if rs.bof and rs.eof then
'not found
else
'found
endif

of course you are creating an entire recordset this way...

--------------------
Procrastinate Now!

 

[PHV]

And this ?
Private Sub Find_Click()
' Find the record that matches the control.
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[ID] = " & Nz(Me![Opp_ID_Search], 0)
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "No ID found"
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244