Display "No data" message when subform receives no records from qu

[Joe M.]

Hi. I have a subform which when no records are found by the query I would
like to display a message, possibly a msgbox. I have done this successfully
for another project in the nodata event in a report. However, I have found
for a subform that this event does not exist. Therefore after reading some
previous posts I have tried to put this inside the subform's load event as a
test:
If Me.RecordsetClone.RecordCount = 0 Then Stop
What I have found is that the subform loads when the main form loads so this
doesn't work as the subform load event only occurs once upon the loading of
the main form. BTW I use a cmd button in the main form with DoCmd.Requery
"Lookup_results" to lookup my data. Can someone help with this? Thanks for
your time.
Joe M.

 

[Jeanette Cunningham]

Hi Joe,
you could use code like this-->

If Me.[SubformControlName].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "No data matching your search"
End If

Replace SubformControlName with the name of the subform control ( which may
be different from the name of the subform itself).

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Joe M.]

Does anyone have any ideas? Many thanks!

 

[James A. Fortune]

Hi. I have a subform which when no records are found by the query I would
like to display a message, possibly a msgbox. I have done this successfully
for another project in the nodata event in a report. However, I have found
for a subform that this event does not exist. Therefore after reading some
previous posts I have tried to put this inside the subform's load event as a
test:
If Me.RecordsetClone.RecordCount = 0 Then Stop
What I have found is that the subform loads when the main form loads so this
doesn't work as the subform load event only occurs once upon the loading of
the main form. BTW I use a cmd button in the main form with DoCmd.Requery
"Lookup_results" to lookup my data. Can someone help with this? Thanks for
your time.
Joe M.

Try:

http://groups.google.com/group/microsoft.public.access/msg/ec5a6d8158406dc7

James A. Fortune
(e-mail address removed)

Having Google Group Search working again with older posts is wonderful.

 

[Paul Shapiro]

You're right that the subform loads first, but won't have any records until
after the main form loads. You could add a public CheckRecordCount method to
the subform, and call that method in the main form's OnCurrent event. The
CheckRecordCount method could show or hide a label with your no-record
message depending on the existence of subform records. You would also want
to call that method in the subform's AfterInsert and AfterDelete events, to
keep the display accurate.

You mention using a command button to let the user retrieve the subform
records. If the subform is in a 1:Many relation from the main form data, why
not use the subform control's Master and Child link fields to relate the
subform to the main form, so data retrieval is automatic?

I've always chosen to let the empty subform be a sufficient indicator of no
records, rather than enabling any additional indication.