On NoData event for linked forms?

[NachoMan]

Friends,
I have a main form [frmEvents] with several cmd buttons that open linked forms. If there is criteria that matches the linked field in the linked form the corresponding records are displayed. If not the form opens with a blank detail area. There is no On NoData event for forms, so how can I cancel the openform command if there is no linking data? Its not a huge hassle, but I would like to avoid opening the target form if there is no data. Any help would be great.

 

[Mile-O]

Check the number of records the form will have before you open it.

If DCount("MyField", "MyQuery", "MyOtherField = " & Me.txtMyTextbox) Then
    MsgBox "No records to view", vbInformation
    Exit Sub
Else
    DoCmd.OpenForm...........................
End If

 

[Rich]

Or on the LoadEvent of the opening form

If RecordsetClone.RecordCount = 0 Then
MsgBox "No records"
DoCmd.Close
End If

 

[NachoMan]

Strange problem

Thanks for the replies, fellas. I tried out Rich's solution since it was super quick and easy. Everything worked fine at my home computer (running Access 2002 on XP), but at work (Access 2K on Windows NT) I keep getting the runtime error '2501 when I close the linked record form. Also, on my work computer the form actually does open up with an empty recordset, and then displays the msg box "no records." This is strange. On my home computer, only the msgbox would dislplay for an empty recordset. Any ideas why this is happening?

Incidentally, I did try Mile's Dcount method, but I must have been doing something wrong, because I could not get it to work correctly.

 

[Mile-O]

When I use Rich's method I usually find myself having to "populate" the RecordSet first but I'd do it in DAO which you won't be set up for on Access 2002 without making a "tweak".

With mine, you'll find that the problem was probably to do with the delimiters.

They are different for numbers, text, and dates.

Numbers:

If DCount("MyField", "MyQuery", "MyOtherField = " & Me.txtMyTextbox) Then
    MsgBox "No records to view", vbInformation
    Exit Sub
Else
    DoCmd.OpenForm "MyForm"
End If

Text:

If DCount("MyField", "MyQuery", "MyOtherField = """ & Me.txtMyTextbox & """") Then
    MsgBox "No records to view", vbInformation
    Exit Sub
Else
    DoCmd.OpenForm "MyForm"
End If

Dates:

If DCount("MyField", "MyQuery", "MyOtherField = #" & Me.txtMyTextbox & "#") Then
    MsgBox "No records to view", vbInformation
    Exit Sub
Else
    DoCmd.OpenForm "MyForm"
End If

These delimters, of course, refer to the criteria argument of the DCOunt() domain aggregate function.

 

[NachoMan]

Got it.

Thanks again, Mile. I tried it again your way and got it to work the second time around. I was aware of the different delimiter syntax for the where clause, but that wasn't the problem in this case. What I realized was that the OpenForm and Msgbox statements were reversed. If there is a match, the form will open not vice-versa. Thanks again for your help. As far as Rich's approach, again, it worked great on my home computer, but I'm still not tracking why I didn't work at work. Thanks again both.

 

[Mile-O]

I forgot to make the DCount expressions say:

If Dcount(bla bla bla) = 0 Then

That'll be why you had to reverse them.

 

[NachoMan]

Roger that.

Yup, that makes sense. It would work that way too. Thanks for all of your help. My conscience is telling me that I should probably have cut you a check by this point. Take care.

-Lou