Hide Controls in report based on subreport value

[Gman]

I have a report and in that report I have a continuous subreport.
they are linked by roomnumber (master/child)

The subreport can have anywhere from 0 to 10 records (each record has 5 textboxes with diffeent values).
In the main report, I also have ten textboxes.

If the subreport does not have any records, I want to hide all textboxes,
If the subreport has one record, I want to show Textbox1 and hide textbox2 through textbox10.

If the subreport has two records then show textbox1 & 2, and so on.
Also, if say I have one record, then I only show textbox1, I would like to have this textbox equal to one of the textboxes in the subreport record.


I have tried using this code to loop through the all controls in subreport, but it does not work,

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

For Each ctl In Me.Controls
if not isnull(ctl) then
do not hide
else: hide
End If
Next ctl

End Sub

When i loop through this code, it seems to repeat the same record.

Any direction would be great

 

[Magster]

Hi,

I have a similar situation with the subreport. When I put the subreport on the report, I always shrink the subreport into a single line on the report. By that I mean I reduce the size of the form into a single line so when you're in design mode in the report, all you see of the subreport is just a line and the not entire subreport.

If you have no children rows, nothing is printed on the report.

Then, go to the subreport and use the format event of the detail line in vba to check the values of the parent fields in the report and then you can use the fields on the subreport and set its visible attriubte to either yes or no depending upon the value of the parent report..

If I didn't explain this very well - get back to me.

 

[Gman]

I may need to further explain,

If there is a one record in the subreport, then I only want to show one textbox in the report, if there are two records in subreport, then show two textboxes, etc.

Also, if you have a record in the subreport, and you set the visibillty of that textbox to true, I also need to get the value from one of the five textboxes from the subreport record, and set the texbox in the report to that value.

Also, if there are not records, then all of the textboxes in the report should be hidden.

How can I count the number of records that are linked to the main report,
If I could, then if say the subreport only has three records that are linked to the report, I would run some code to show textbox1 through textbox3.

Thanks for your reply,

 

[Magster]

You asked: How can I count the number of records that are linked to the main report, To count the rows in the subform, you can check the recordsetclone of the sub report or sub form.

Here is an example of what I do in that situation.
on the set rstClone line below, you should be able to use a more direct route to the form, but I have
them on tabs and so I went down the full path to the needed child form.
The same can be done in reports.

Dim rstClone As DAO.Recordset
Set rstClone = Forms![frmProjects]!frmProjectDelays].Form.RecordsetClone ' delays
'
If rstClone.RecordCount = 0 ' here is your number of related rows to the sub (form in this case, but same for report)
GoTo CloseClone
End If
'
With rstClone ' traverse thru and see if the delay row(s) are open or closed
.MoveFirst
Do Until .EOF
If IsNull(.Fields("EndDate")) Then ' delay is open
MsgBox "There are open delay(s) for this step. Please close the delay first.", vbCritical, _
"Open Delays exist for this step..." ' Define title.
StopUpdateBecauseOfOpenDelays = True
Exit Do
End If
.MoveNext
Loop
End With
'
CloseClone:
rstClone.Close
Set rstClone = Nothing

 

[Gman]

Ok,
I found a way to count the records in the sub form, I put a textbox in the footer of the subreport, set the control source to =count(*), then on the main report, I have another textbox, and have the controlsource equal to the textbox on the subreport footer.

This gets me the total records in the subreport.

Although I still have not gotten to my end result.

I am able to hide and show the correct textboxes, i do this this in the onformat event for the main report based on the total records.

After the main report onformat in triggered, the subreport onformat then fires, this is where i loop through all of the controls on the subreport, set the values to an array.

After the subreport, the onformat event triggers again, and I also have code to set the textboxes to the stored values in the array.

The array works great, but when the main report onformat event triggers after the subreport, the textboxes are unable to take the new values.

The textboxes on the main report will only accept the values on the first trigger for the main report, but I do not have the values until I run the subreport.

Not sure if I am even on the right track,

Any help,

 

[smig]

make the subreport very thin and use it's CanGrow property to make it show only the number of records.
put the textboxes on another subreport, based on the same data.
both subreports will grow together.