Solved

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

Posted on 2002-04-26
12
990 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:badreece
12 Comments
 
LVL 1

Expert Comment

by:lyners
Comment Utility
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
 

Author Comment

by:badreece
Comment Utility
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
 
LVL 1

Expert Comment

by:lyners
Comment Utility
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
 

Author Comment

by:badreece
Comment Utility
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
 
LVL 2

Expert Comment

by:Syed Irtaza Ali
Comment Utility
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
 

Author Comment

by:badreece
Comment Utility
Sorry.  I've been caught up in other stuff.  I'll try the latest suggestion very soon.

Thanks,
Bret
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:badreece
Comment Utility
Still no luck.  I ended up using the FindFirst and FindNext commands and programatically looping through each field.  Thanks for the comments.
- Bret
0
 

Author Comment

by:badreece
Comment Utility
I'm deleting the question because no suggestions worked.  Thanks for trying.
0
 
LVL 1

Expert Comment

by:lyners
Comment Utility
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
 
LVL 1

Accepted Solution

by:
lyners earned 100 total points
Comment Utility
Once with the lookup behind the scenes, the next on the form itself....
0
 
LVL 1

Expert Comment

by:lyners
Comment Utility
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
 
LVL 54

Expert Comment

by:nico5038
Comment Utility

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now