×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

msgbox if query has no results

msgbox if query has no results

msgbox if query has no results

(OP)
I am stuck on this...

I have a query("Marketing Partner Query") that pulls its results into a form("Invoices_Search"). All I want to do is have a msgbox appear if there are no results.

I don't think I have the syntax right,

        DoCmd.OpenForm "Invoices_Search"
        Forms!Invoices_Search.RecordSource = "Marketing Partner Query"
        
        If "Marketing Partner Query" < 1 Then
        MsgBox "No Results Found! Please Try Again.", vbOKOnly, "NO SEARCH RESULTS"
        End If


THANKS IN ADVANCE FOR ANY HELP!

RE: msgbox if query has no results

Put the following at the top of the on open event for the form you wish to test for any records available.

Dim rs as DAO.recordset

Set rs = me.recordsetclone
Rs.movefirst
If rs.recordcount < 1 then
    Msgbox     “NO DATA. FORM CANCELLED”
    Cancel = true
    Exit sub
Endif

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

RE: msgbox if query has no results

(OP)
I tried using that code but i get an error saying:

"You entered an expression that has an invaild reference to the RecordsetClone property"

any suggestions?

also i had to change the code slightly here is the new code im using.

thanks in advance!

Dim rs As ADODB.Recordset

Set rs = Me.RecordsetClone
rs.MoveFirst
If rs.RecordCount < 1 Then
    MsgBox "No results found! Please try again."
        
    Cancel = True
    Exit Sub
End If

RE: msgbox if query has no results

I would suggest to you that before you change the underlying assumption of an allocated object such as a DAO recorset to an ADODB recordset, you might check how the extenion is defined in terms of class. you will find that recordsetclone is a member of type forms class and  therefore cannot be defined as as a member of the ADODB Set.

This is a very common misunderstanding and will crash programs very quickly. Be careful that when you change from DAO to ADODB you know what classes you are truly using.

The only reason I used DAO.recordset in my example is to alert you that you might not want to use adodb.

If you just say dim rs as recordset it will work just fine.
Dim rs as recordset
HTH.


Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

RE: msgbox if query has no results

thornmaster, would you say that putting a test like yours  above into the "open" event is the best way to keep a report from running when there are no records for it to display?  I run a long macro that tests to see if customers have ordered any of a series of catering menus and prints a report displaying each order . I've tried putting a dcount function in the condition column for each report in the macro to test if there is a record to display before opening the report.  This calls for lots of maintenace.  Recently I switched to putting a cancel macro into the OnNoData event of each report in the series.  This seems a little slower.  How do the pros do it?

RE: msgbox if query has no results

My only suggestion to you is to begin getting away from macros. I’m sure you’ve read a number of posts detailing why so I certainly will not elaborate.

For reports the onnodata event is the way to test for data.

In forms, it is your preference and with what you are comfortable doing.  I have always tried to use the smallest record set possible so I tend to test in the on open event. Depending on what the form is for, sometimes, if there is no data, I will open the form for adding records, but most often if the record set is empty, I will simply cancel the open event.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

RE: msgbox if query has no results

(OP)
Robert,

I want to thank you for being patient with me, however I am still getting the same error message. I have been playing around with the code for two days and I can't seem to understand what is going wrong. This is the last thing on my "to do" list for this project. If you can help me through this it would be fantastic!

The error message seems to be that the recordset clone has nothing to reference(see above error msg).

I have the code on the open event for the "view" form which holds the recordset. But the code that calls the recordset is on the "search" form. Is this the source of the problem, I have tried the code on both forms. To no avail, I still get errors.

The code is pasted below. Thanks again!

THIS IS THE CODE FOR THE "VIEW" FORM:
Private Sub Form_Open(Cancel As Integer)

    Dim rs As Recordset
    Set rs = Me.RecordsetClone
    
    rs.MoveFirst
    If rs.RecordCount < 1 Then
        MsgBox "No results found! Please try again."
        Cancel = True
        Exit Sub
    End If
    
End Sub

THIS IS THE CODE FOR THE "SEARCH" FORM:
Private Sub cmdEnter_Click()
Select Case Me!Frame36
        Case 1
        'Open the "View" form
             DoCmd.OpenForm "s_OTHERInvoices_Search"
        'Pull recordset from query into the "View" form
             Forms!s_OTHERInvoices_Search.RecordSource  
             = "x_Marketing Partner Query"
        

RE: msgbox if query has no results

Lexis,

I apologize for not responding sooner. I was at a client site, but am now back in my office sharing left over pizza with dog for lunch.

If I can help you through your last step of this project I will be happy to do so. The reason there is no reference to your recordset clone, is you are trying to set something as an ADODB recordset (rs) and then use rs as a recordsetclone which is a form class object, and VBA is having a hissy fit and throwing you a data type mismatch error.

Have you tried using this as a DAO recordset? Like,

Dim rs as DAO.recordset, and then

Set rs = me.recordsetclone

If rs.recordcount < 1 then
    Msgbox
    Cancel = true
    etc
Else
    Continue processing
Endif

You can certainly have this on the forms open event.

If that does not, or will not work for you, post back. There are other ways to test for this besides testing the forms clone.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

RE: msgbox if query has no results

(OP)
ok-
It was my mistake, I had the wrong reference checked off...but now after all that, nothing happens. I don't get any errors but I don't get a msgbox either. aghhh! any suggestions?
Thanks
alexis

Private Sub Form_Open(Cancel As Integer)

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.MoveFirst
    If rs.RecordCount < 1 Then
        MsgBox "No results found! Please try again."
        Cancel = True
    End If
 
End Sub

RE: msgbox if query has no results

Lexis,

We forgot something.

set rs = me.recordsetclone
rs.movefirst
if rs.recordcnt < 1, etc.

Also, use your debugger and set a break point on the set rs=me.recordsetclone.

At that point begin stepping one instruction at a time. when you get to the if rs.recordcount, do a
?rs.recordcount which should give you the recordcount in the debug window. That will tell you if it is working.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

RE: msgbox if query has no results

Argggg.

Stupid suggestion. Forget it. If there are no records, doing a movefirst will throw an error condition. Use debugger. Set break point on set rs = me. recordsetclone. Step that until if rs.recordcount, then use debugger to display recirdset value so we will know if it is working or not.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

RE: msgbox if query has no results

(OP)
Robert,
 After a week long battle with this and after your helpful suggestions about debugging, I found the solution to this problem. When I debugged I found that it's recordset value was all my records and not the results of the query.

 All I changed was having the code written under
Private Sub Form_Current().
 Works great!

 Thanks for all your help.

Alexis

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close