Hi all.

I have a command button on one form that opens another to view some data. What I am trying to get to happen is that if there is no data in the second form. I want a msg box to display "No data for this record" and keep the user on the first form.

I have put some code to check the recordset on the second form. I have the Msgbox working, but then when I hit OK. I get a "Open form action cancelled" message.

What order do I need to accomplish this task to avoid this message? OR even better, can I check the second form for matching data WITHOUT opening it and displaying the message or continuing with the open?

I can code a little.

Thanks.

Hi there,

I check the recorsource of the second form without opening it like this

If Dcount("*","SourceofSecondForm","[IDofRecord]=" & me.ControlName)= 0 then
msgbox " no records"
else
Docmd.Openform .....

That's if you are opening the form with data connected to the first form. If you just want to openit with all data then
DCount("*","SourceofSecondFrom") retuns the number of records in the record source.

Hope this helps
Marion

Marion:

Thank you so much!! That works like a dream!!

I didn't know I could check properities of another object WITHOUT that object having the focus. What other types of objects and properities can be found outside of their focus?

This is really great!

Hi there,

I couldn't write an application without the Dlookup, Dcount, DMax Functions! They can all be used in VB , addressing Tables or stored Queries.

Marion

Domain Aggregate Functions are are nice but they are notoriously slow. These functions should only be used as last resorts if the table they are being based on contains many records.

This is much faster:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox " No record found", vbOKOnly
Cancel = True
Exit Sub
End If
End Sub

RDH



[This message has been edited by R. Hicks (edited 10-07-2001).]

Rick:

I went that route on my first go-round. I put that code on the OnOpen event of the form I wanted to open with a match. That is when I got the "Open form action canceled" error that I was wanting to avoid.

What can I do?

You can catch the error - I'm not sure of the number - but in your eror routine you can add "if err = 2501 then exit sub"

I use this for reports after the onNoData Event.

Marion

Thanks Marion, I had tried something like that last night. I couldn't figure out how to write that. I haven't learn how to write error handling code yet.

Access looks at the Cancel as an error. so you can use this:

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox " No record found", vbOKOnly
Cancel = True
Exit Sub
End If
End Sub

RDH

Rick:

Do I put that code on the form I'm trying to open or on the command that opens it? If I put it on the form I'm trying to open, I still get the Error 2501.

Since your putting the code on the open event just close it if there are no records
Private Sub Form_Open(Cancel As Integer)
If (RecordsetClone.RecordCount = 0) Then
DoCmd.Close
Beep
MsgBox "There is no information recorded for that period.", vbInformation, "Nothing Recorded"
End If
End Sub

Thank you for all the great information. I will look it over and decide which way is the best way to go in this particular situation. I'm sure I will use the other methods as what is best this time will not be the best last time.

Please join me in my other post.
http://www.access-programmers.co.uk/ubb/Forum2/HTML/001089.html