Solved

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

Posted on 2002-04-26
12
994 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
ID: 6972201
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
ID: 6972259
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
ID: 6972356
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
ID: 6972433
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
ID: 6973088
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
ID: 6982503
Sorry.  I've been caught up in other stuff.  I'll try the latest suggestion very soon.

Thanks,
Bret
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:badreece
ID: 6994137
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
ID: 6994140
I'm deleting the question because no suggestions worked.  Thanks for trying.
0
 
LVL 1

Expert Comment

by:lyners
ID: 6994212
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
ID: 6994253
Once with the lookup behind the scenes, the next on the form itself....
0
 
LVL 1

Expert Comment

by:lyners
ID: 6994255
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
ID: 7186585

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

896 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

11 Experts available now in Live!

Get 1:1 Help Now