How do I know when DoCmd.FindRecord or DoCmd.FindNext didn't find anything?

Hi,

I'm using DoCmd.FindRecord... and DoCmd.FindNext from a command button in my DB.  It's working fine, except that when no data is found, nothing happens.  I'd like to be able to know when nothing is found so that I can display a message telling the user.

Thanks much,

Bret
badreeceAsked:
Who is Participating?
 
lynersConnect With a Mentor Commented:
Once with the lookup behind the scenes, the next on the form itself....
0
 
lynersCommented:
Try this after the search... (If you are searchuing the recordsource of the form!)

if recordsetclone.eof then
   msgbox "Nothing Found!"
end if

Lyners
0
 
badreeceAuthor Commented:
Hi,

I'm actually searching the recordset of another form.  Basically, I have a form down below called frmControl.  It's got some buttons, including a Find button.  I'm changing the caption on the Find button to "Find Next" once the user has searched once (and invoking FindNext instead of FindRecord.)  That way he/she doesn't have to use two different buttons.  Anyway, that part works.

I added "Forms![frmMain]." in front of "RecordsetClone.EOF" from your suggestion to look at the correct form, but still no go.  I'm not sure if I'm doing something wrong.  Here is the code for my Find button (w/ my revised version of your suggestion.)

===================================
Private Sub cmdSearch_Click()
Dim SearchString As String
   Forms![frmcontrol].txtSearchString.SetFocus
   SearchString = txtSearchString.Text
   If SearchString <> "" Then
      If Forms![frmcontrol].[cmdSearch].Caption = "Find It" Then
         Forms![frmcontrol].[cmdSearch].Caption = "Find Next"
         Forms![frmMain].SetFocus
         DoCmd.FindRecord SearchString, acAnywhere, , acSearchAll, True, acAll
         If Forms![frmMain].RecordsetClone.EOF Then
            MsgBox "No matching records were found."
         End If
      ElseIf Forms![frmcontrol].[cmdSearch].Caption = "Find Next" Then
         Forms![frmMain].SetFocus
         DoCmd.FindNext
         If Forms![frmMain].RecordsetClone.EOF Then
            MsgBox "No matching records were found."
         End If
      End If
   End If
End Sub

===================================

Let me know what you think.
Thanks,
Bret
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
lynersCommented:
Try this. I'm throwing out ideas here.... There is a way.

   If Forms![frmMain].NoMmatch Then
        MsgBox "No matching records were found."
   End If

Lyners
0
 
badreeceAuthor Commented:
Hi,

No luck.  The NoMatch property refers back to FindFirst, FindNext, etc, rather than DoCmd.FindRecord like I'm using.  When I try to use it, I get "Run-time error '2465':  Application-defined or object-defined error."

I think it would work if I used FindFirst and FindNext instead of the DoCmd stuff, but then I think (tell me if I'm wrong) that I'd have to programatically search each individual field for the user's search string.  The reason I'm using the DoCmd.FindRecord statement is that I can tell it to search through ALL of the fields in the DB (acSearchAll.)  Maybe there's a way to tell FindFirst and FindNext to look at all fields, but if there is, I can't find it.

Bummer...

Thanks,
Bret
0
 
Syed Irtaza AliLead Software ArchitectCommented:
try this

a=Forms![frmMain].form.recordset.absoluteposition
DoCmd.FindRecord SearchString, acAnywhere, , acSearchAll, True, acAll
If a=
(Forms![frmMain].form.recordset.absoluteposition+1) Then
           MsgBox "No matching records were found."
End If


but there is a catch here. This works for continous form. If the frmMain is a continous it will work fine as it compares the recordset position. However you can use

Forms![frmMain].form.recordset.EOF and
Forms![frmMain].form.recordset.BOF to verify if you have reached the End of file or the beginnning of File.


Nomi.

0
 
badreeceAuthor Commented:
Sorry.  I've been caught up in other stuff.  I'll try the latest suggestion very soon.

Thanks,
Bret
0
 
badreeceAuthor Commented:
Still no luck.  I ended up using the FindFirst and FindNext commands and programatically looping through each field.  Thanks for the comments.
- Bret
0
 
badreeceAuthor Commented:
I'm deleting the question because no suggestions worked.  Thanks for trying.
0
 
lynersCommented:
Badreece,
Before you delete the question. This is how I have handled it in the past. it is more coding but will work. On the On_Click Evnt of the find button, first have it do a lookup in the table. If found, then fo the find first. Like this....

dim db as database
dim rs as recordset

set db = currentdb
set rs = db.openrecordset("RECORDSOURCE")

rs.findfirst ("[Field] = '" & Criteria & "'")
if rs.nomatch then
    msgbox "Data not found"
else
     DoCmd.FindRecord SearchString, acAnywhere, , acSearchAll, True, acAll
end if

I simplified this, you will have to add your code from above. This will work, It is just more cumbersome.

Let me know if you want me to code it to a T (if you are having problems with it). in short you do the same lookup twice.

Lyners
0
 
lynersCommented:
Hate to have you delete this question when there is an answer. Sorry I didn't put this solution at first. I was trying to get it to work with a form, but no, there is no form.nomatch.
0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.