Subforms "a nightmare" (1 Viewer)

[mark curtis]

Dear All,
Another little problem for which I once again call on the help of more advanced access folk than me.

I have a report based on a query which also contains many subforms such as highlights, products, risks, dependancies.

When I run the report all of the subforms may contain data OR only some may contain data OR none may contain data. What I need to do is IF only some of the subforms contain data then I need to hide the subforms which have no data(it may have no data but the labels the subform label still appears). Also if a the subform contain no data then I want to return a label saying NO Data This Month.

Ian suggested if I wanted to check and see if there were any records to view before viewing I suppose you could do something like creating a recordset clone of the subreports data and conditionally viewing/hiding dependent on whether the recordcount is >0

But I do not know how to do this and would appreciate an example.

If there are any other methods I would also appreciate any help.

Thanks again, Happy New Year.

Mark

 

[Fornatian]

Use something like:

If Me.RecordsetClone.RecordCount > 0 Then
MsgBox "Records Exist"
'and other actions
Else
MsgBox "No Records"
'you could make your "no records" text box visible here'
End If

You would have to check this in the main form - you would have to reference the subform control as follows:

If Me![Table1 subform].Form.RecordsetClone.RecordCount > 0 Then
MsgBox "Records Exist"
Else
MsgBox "No Records"
End If

Hope that helps.
Ian

 

[mark curtis]

Ian,

Thanks for the reply.

Two things:
1)Where do I put the code you suggest, as I am very limited in coding.

2)I do not want msgboxes to appear, I just want to show the No Data This Month label if the subform has no records.

Thanks again for all your help.

Mark

 

[Fornatian]

Put yourself a label box on your form with the caption set to "No data for this form" and the visible property set to false.
Basically, what we are going to do is conditionally toggle whether the label or the subform is visible.

For each subform or subreport enter this code in the OnCurrent event of the Main Form

If Me![Table1 subform].Form.RecordsetClone.RecordCount > 0 Then
'show the subform
Me![Table1 subform].visible = true
'hide the no records label
Me![LabelForNoRecords].visible = false
Else
'hide the subform
Me![Table1 subform].visible = false
'show the no records label
Me![LabelForNoRecords].visible = true
End If

Because you have this on the forms current event it will change when you switch between records.

HTH

Ian

[This message has been edited by Fornatian (edited 01-01-2001).]

[This message has been edited by Fornatian (edited 01-01-2001).]

 

[mark curtis]

Thank you Ian,

I will try the above, just one last thing, Is there an OnCurrent event for my main report which incorporates the subforms?

Could I use OnActivate?

Thanks again
Mark

 

[Fornatian]

To use the above with reports, use the OnOpen event.
You could use the OnActivate event but that is inefficient because the code is run every time the window gets the focus, it is much better to use the OnCurrent(forms) and OnOpen(reports) events, that way the code is only run when the record changes.

HTH

Ian

 

[mark curtis]

Ian,

I placed the code below in the OnOpenEvent of my main report but keep getting an error message, can you advise?

Private Sub Report_Open(Cancel As Integer)
If Reports![Main Report]![SubReport].RecordsetClone.RecordCount > 0 Then
'show the subform
Reports![Main Report]![SubReport].Visible = True
'hide the no records label
Reports![Main Report]![LabelForNoRecords].Visible = False
Else
'hide the subform
Reports![Main Report]![SubReport].Visible = False
'hide the no records label
Reports![Main Report]![LabelForNoRecords].Visible = True
End If
End Sub


Thanks

 

[Fornatian]

Looking at your code I notice that your have not referenced the subreport correctly. Because the subreport is an object on the main report you have to reference it explicitly like:

Private Sub Report_Open(Cancel As Integer)
If Reports![Main Report]![SubReport].REPORT.RecordsetClone.RecordCount > 0 Then
'show the subform
Reports![Main Report]![SubReport].Visible = True
'hide the no records label
Reports![Main Report]![LabelForNoRecords].Visible = False
Else
'hide the subform
Reports![Main Report]![SubReport].Visible = False
'hide the no records label
Reports![Main Report]![LabelForNoRecords].Visible = True
End If
End Sub

I think that is your problem, if not please come back - I love a challenge!

Ian

PS - I think the OnNoData event on your other post may be an equal alternative - the way to do that would be to set all the fields invisible for the report and make the 'no info' label visible. The method above I use typically with forms which by definition don't have a OnNoData event.

Ian

 

[mark curtis]

Ian,

I am glad you love a challenge because I still can't get it to work. The error keeps highlighting the record count line.

It is really peeing me off,

Once again in your capable hands

Thanks
Mark

 

[Fornatian]

Will check my program and return...
Would send you mine but it's a bit big.

 

[Fornatian]

My sincerest apologies Mark, I tried my suggestion with the report myself and it does not work(DOH!). I've learnt something new though - you can't clone a subreports recordset.

Instead I would suggest this:
Open the subreports recordset independently and take the appropriate action. Such as:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("ReportSourceName")
If rst.RecordCount > 0 Then
MsgBox "Records Exist"
Else
MsgBox "No Records Exist"
End If
rst.Close
db.Close

Change the Msgboxes for the toggle code and it should work.

Awaiting errors!

ian

 

[mark curtis]

Ian,

I have tried the code on several events such as OnOpen(main report) then OnOpen(main report)but still no joy.

I am beginning to think that it is impossible.

Over to you once again my Access friend.

Thanks
Mark

 

[Richie]

Here's another twopence worth which may help I have two subreports on a report one of which simply doesn't display if there are no records there is no underlying code, acccess just doesn't display it so no problem for me there, I don't know why yours is behaving differently, however the first subreport always has zero if no underlying data since I want it to display the zeros. I found that by refering to a field in that subreport I could hide / display accordingly so you should be able to do the same something like:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If (IsNull(Reports!InvTest![Query9 subreport].Report!ItemCost)) Then
Me.[Query9 subreport].Visible = False
Me.Label32.Visible = True
Else
Me.[Query9 subreport].Visible = True
Me.Label32.Visible = False
End If
End Sub
in the detail on format should do the trick change the names to suit.
HTH

 

[Fornatian]

Something New I've Learnt Today!