Subreport Visibility

[spartansFC]

Hi

I have a report with a subreport and what i need is for the subreport not to be visible when i user selects a check box. The way everything is set up is:

Letter 1 : Allocation of Funding - based on qryLetter1Allocation of Funding
rptLetter1AllocationofFundingSub - based on qryLetter1AllocationofFundingSub

the report and subreport are linked child/master by lngChildID

so when the user selects the report a piece of code kicks in on the OnOpen event of the report

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmLetter1SelectionOptions", , , , , acDialog, "Letters: Standard"
    If Not IsLoaded("frmLetter1SelectionOptions") Then
        Cancel = True
    End If
End Sub

on this frmLetter1SelectionOptions there is a chkbox

FundingBreakdownTableYesNo

so what is supposed to happen is even if the subreport has data in, we might not want it to be shown, so the user would tick this check box.

On the format event on the detail section of the main report i have:

If [Forms]![frmLetter1SelectionOptions]![FundingBreakdownTableYesNo] = Yes Then
        Me.rptLetter1AllocationofFundingSub.Visible = True
   End If
        If [Forms]![frmLetter1SelectionOptions]![FundingBreakdownTableYesNo] = No Then
            Me.rptLetter1AllocationofFundingSub.Visible = False
        End If

I do reset all the chkboxes on the onclose event of the report via:

Private Sub Report_Close()
[Forms]![frmLetter1SelectionOptions]![cmbChildSelect] = Null
[Forms]![frmLetter1SelectionOptions]![cmbMeetingDate] = Null
[Forms]![frmLetter1SelectionOptions]![Option1_chkbox] = Null
[Forms]![frmLetter1SelectionOptions]![Option1a_chkbox] = Null
[Forms]![frmLetter1SelectionOptions]![AllocatedAmount_txtbox] = Null
[Forms]![frmLetter1SelectionOptions]![AllocatedSupportto_txtbox] = Null
[Forms]![frmLetter1SelectionOptions]![OptionFurther_chkbox] = Null
[Forms]![frmLetter1SelectionOptions]![AllocatedFurther_txtbox] = Null
[Forms]![frmLetter1SelectionOptions]![AllocatedFurtherTo_txtbox] = Null
[Forms]![frmLetter1SelectionOptions]![Option2_chkbox] = Null
[Forms]![frmLetter1SelectionOptions]![OneOffPayment_txtbox] = Null
[Forms]![frmLetter1SelectionOptions]![Option3_chkbox] = Null
[Forms]![frmLetter1SelectionOptions]![NoSessions_txtbox] = Null
[Forms]![frmLetter1SelectionOptions]![Option4_chkbox] = Null
[Forms]![frmLetter1SelectionOptions]![SplitSession1_txtbox] = Null
[Forms]![frmLetter1SelectionOptions]![Option5_chkbox] = Null
[Forms]![frmLetter1SelectionOptions]![cmbTermSelection] = Null
[Forms]![frmLetter1SelectionOptions]![ApplicationSubmittedDate_txtbox] = Null
[Forms]![frmLetter1SelectionOptions]![Variations_chkbox] = Null
[Forms]![frmLetter1SelectionOptions]![FundingBreakdownTableYesNo] = Null
End Sub

No matter if i check the box or not, the subreport is still displayed. Please someone put me out of my misery and tell me what i'm doing wrong.

Thanks

Michael

 

[dhookom]

You should be able to simplify your code to:

Me.rptLetter1AllocationofFundingSub.Visible = _
    ([Forms]![frmLetter1SelectionOptions]![FundingBreakdownTableYesNo] = Yes)
Debug.Print "Box: " & [Forms]![frmLetter1SelectionOptions]![FundingBreakdownTableYesNo]

This assumes the code is running in the section containing the subreport and the form remains open.

Duane
Hook'D on Access
MS Access MVP

 

[Rudi89]

I stick that code in Report_Open for the master report. Something like...

Me.ReportHeader.Visible = False

If Form_Example!Option = -1 Then
Me.ReportHeader.Visible = True
End If

 

[spartansFC]

So i've tried implementing your coding Duane and i get a:

Run-time error '13'
Type mismatch

when i click into Debug it highlights the following:

Me.rptLetter1AllocationofFundingSub.Visible = _
    ([Forms]![frmLetter1SelectionOptions]![FundingBreakdownTableYesNo] = Yes)

although the little pointer/arrow is pointing the 2nd line of the above code, so i'm not sure why there's a mismatch

Michael

 

[dhookom]

Try use -1 rather in place of Yes.

Duane
Hook'D on Access
MS Access MVP

 

[spartansFC]

So i changed the code to

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 Me.rptLetter1AllocationofFundingSub.Visible = _
   ([Forms]![frmLetter1SelectionOptions]![FundingBreakdownTableYesNo] = -1)
    Debug.Print "Box: " & [Forms]![frmLetter1SelectionOptions]![FundingBreakdownTableYesNo]

End Sub

the subreport is displayed when the tick box is ticked but when i leave the tick box unticked i get the run-time time error '13' type mismatch again.

Do i need another line to catch the unticked option?

Michael

 

[spartansFC]

Ok, so i tried Rudi89's solution and it worked, the code is

Me.rptLetter1AllocationofFundingSub.Visible = False

    If ([Forms]![frmLetter1SelectionOptions]![FundingBreakdownTableYesNo] = -1) Then
        Me.rptLetter1AllocationofFundingSub.Visible = True
    End If

so thanks for all your help Duane and Rudi89, you've put a really bad access programmer out of his misery

Michael