form no records message box.

[jenny_gurl]

Hello,

Am having a bit of a problem here.

i have a form which contains three subforms. the subforms are based on queries which get their parameter value from an option group. all this was working fine until i had the bright idea of displaying a msgbox when no records are found in the subforms.

i had entered the following code that i had found on one of the posts in this forum:

Private Sub Form_Open(Cancel As Integer)

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are zero records in the data source!", vbInformation, "No Records Found"
DoCmd.Close acForm, Me.name
End If

End Sub

the trouble is that if the user makes a selection in the option group whereby there are no records in two or more subforms, it throws up two different msgboxes stating the same.

I want the form to be such that everytime the subforms are requeried, there shud be only one msgbox stating tht no records were found for the relevant subforms. (this msgbox doesnt appear when there are records). for eg the msgbox shud say that "no records found for subfrm1 and sbfrm2 etc". if subfrm 1 and two don't have records but subfrm3 does.

is their a way to do this?

thanks in advance!

 

[Oldsoftboss]

Try playing around with the On Open event of the main form rather than the subforms.

Try

If Forms!MainFormName.Form!SubForm1Name.Form.RecordsetClone.RecordCount = 0 Then

and variations of it

eg

If Forms!MainFormName.Form!SubForm1Name.Form.RecordsetClone.RecordCount = 0 and Forms!MainFormName.Form!SubForm2Name.Form.RecordsetClone.RecordCount = 0 Then

etc

 

[databasedonr]

I know this is an old post, but I was just trying to do the same thing, and thought I'd share my solution.

I place a label on the main form above the subform instead of using the subform label.

Then, in the Form_Current event on the main form, I place the following code:

If Form_Subform.RecordsetClone.RecordCount = 0 Then
Me.Label.Caption = "No Subform Records"
Else
me.Label.Cation = "Subform Name"
End if

It seems to work really well for me....

If you stumble back across this, I hope it helps.