msgbox if query has no results 1

[lexis7]

I am stuck on this...

I have a query("Marketing Partner Query&quot that pulls its results into a form("Invoices_Search&quot. 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 &quot;Marketing Partner Query&quot; < 1 Then
MsgBox &quot;No Results Found! Please Try Again.&quot;, vbOKOnly, &quot;NO SEARCH RESULTS&quot;
End If


THANKS IN ADVANCE FOR ANY HELP!

 

[thornmastr]

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

 

[lexis7]

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

&quot;You entered an expression that has an invaild reference to the RecordsetClone property&quot;

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 &quot;No results found! Please try again.&quot;

Cancel = True
Exit Sub
End If

 

[thornmastr]

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

 

[BruceBell]

thornmaster, would you say that putting a test like yours above into the &quot;open&quot; 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?

 

[thornmastr]

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

 

[lexis7]

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 &quot;to do&quot; 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 &quot;view&quot; form which holds the recordset. But the code that calls the recordset is on the &quot;search&quot; 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 &quot;VIEW&quot; FORM:
Private Sub Form_Open(Cancel As Integer)

Dim rs As Recordset
Set rs = Me.RecordsetClone

rs.MoveFirst
If rs.RecordCount < 1 Then
MsgBox &quot;No results found! Please try again.&quot;
Cancel = True
Exit Sub
End If

End Sub

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

 

[thornmastr]

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

 

[lexis7]

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 &quot;No results found! Please try again.&quot;
Cancel = True
End If

End Sub

 

[thornmastr]

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

 

[thornmastr]

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

 

[lexis7]

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