Display message on subform when underlying query is empty

[Erik Pen]

My main form has several subforms which display the results of queries.

I want to display a message in a subform when it's query is empty indicating that there are no records meeting the form's criteria.

wondering how this can be done.

Thanks

Erik

[Erik Pen]

Anyone? It really can't be that hard?

[mshmyob]

Probably. IS the subform a datasheet view or continuous view.

cheers,

[Erik Pen]

Hi mshmyob

The subforms are continuous forms with headers.

Any help is appreaciated!

[mshmyob]

What is the query of one of your subforms.

cheers,

[Rabbit]

There are multiple ways of doing this.

If the subforms are not linked to the recordset on the parent form, then when the parent form loads, you check the record count of the queries and either point the subform to another form with a message or hide the subform and unhide a textbox with the message.

Or you could, in the subforms, in their load event, check the record count of their queries and then hide the detail and unhide a message.

If the recordsets are linked, then you would move the code to the on current event of the parent form.

[mshmyob]

I did it with a simple check in the On Current event of the main form.

I created a label in the header of the subform call lblNoRecords and set the visible property to No.

If IsNull(Forms!frmYourMainFormName.YourSubformName.Form.ServerID) Then
    Forms!frmYourMainFormName.YourSubformName.Form.lblNoRecords.Visible = True
Else
    Forms!frmYourMainFormName.YourSubformName.Form.lblNoRecords.Visible = False   
End If

Now as you scroll through the main form the subform will check for a null value in the primary key of the subform data and if it is null the label in the header will become visible or else it will be hidden.

Remember to change all form names and control names.

cheers,

[Erik Pen]

Thanks. I was trying exactly that (label in the header of the subform - except that I was trying the on open of the sub form. The IsNull never worked, i.e. it displayed whether null or not. I will try on the parent form.

thanks for your help. It is a bit more complicated because when my main form opens, it displays some sub forms while hiding others based on the tag property of the subform. I guess I can try to work this in as well!

I appreciate the help.

[Erik Pen]

Actually, I am afraid it wont work as I hoped. The way the form is set up is to look like a tabbed folder. The tabs are labels are in the form header and depending on which you click, displays different subforms. Therefore, I can't set the "empty query" label to visible if I am setting the form no not visible. I suppose I may be able to work something into the label's click event.

Here is the parent form On Open event -

Private Sub Form_Open(Cance l As Integer)

Dim ctlCurr As Control

For Each ctlCurr In Me.Controls
If ctlCurr.Tag = "GrpExposur e" Or ctlCurr.Tag = "GrpTabs" Or ctlCurr.Tag = "GrpDefault " Or ctlCurr.Tag = "Tabs" Then
ctlCurr.Visible = True
Else: ctlCurr.Visible = False
End If
Next ctlCurr



Me.lblExposure. BackColor = 16771305
Me.lblDelinquen cy.BackColor = 15377561
Me.lblRatings.B ackColor = 15377561
Me.lblCoverage. BackColor = 15377561

DoCmd.Maximize

End Sub