Solved

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

Posted on 2002-04-26
12
1,007 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
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.

 

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
 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

830 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