Invisible Field in Report

[state90]

Reference https://www.accessforums.net/access/...ort-10309.html



Hi Paul, That thread is a few years old but I am hoping you can help me with a similar issue regarding making text boxes visible or invisible on a report depending upon criteria selected on a criteria form. Can you take a look and see where I went wrong here?

Back ground:
* I have a form [Frm_REPORT_Parameter_01] which has is a criteria selection menu related to a report. Among other criteria, is a combo box [CboGrade] which has the following options to select from "All", Excellent", "Good", "Fair", and "Poor"
* I have a report [FacilityIdentityReportDR]
* On this report, I have a trext field [TxtConditionHdr] that I want to make visible or invisible depending on what is selected in the form as criteria.
* I have a query [QryRPTFacilityIdentityRating01d]

The form criteria feeds the query that is the source for the report and the VBA coding behind the command button of the form is below. I have bolded my vba coding attempt to make the text field visible or invisible.:

Private Sub Command22_Click()
Dim strCrit1 As String
Dim strCrit2 As String
Dim strCrit3 As String
Dim strCrit4 As String
Dim strCrit5 As String
Dim strCrit6 As String
Dim strCrit7 As String
Dim strCrit8 As String
Dim strCrit9 As String
Dim strCrit10 As String
Dim strCrit11 As String
Dim strCrit12 As String
Dim strCrit13 As String
Dim strCrit14 As String
Dim strCrit15 As String
Dim strCrit16 As String
Dim strCrit17 As String
Dim strCrit18 As String

strCrit1 = "[SalesVolume] "
If IsNull(Me!CboSVOperator) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboSVOperator.SetFocus
Exit Sub
Else
strCrit1 = strCrit1 & Me!CboSVOperator
End If
If IsNull(Me!TxtSalesVolume) Then
MsgBox "Please put a value in the textbox"
Me!TxtSalesVolume.SetFocus
Exit Sub
Else
strCrit1 = strCrit1 & " " & Me!TxtSalesVolume
End If

strCrit2 = "[OperatingProfit] "
If IsNull(Me!CboOPOperator) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboOPOperator.SetFocus
Exit Sub
Else
strCrit2 = strCrit2 & Me!CboOPOperator
End If
If IsNull(Me!TxtOpProfit) Then
MsgBox "Please put a value in the textbox"
Me!TxtOpProfit.SetFocus
Exit Sub
Else
strCrit2 = strCrit2 & " " & Me!TxtOpProfit
End If
strCrit3 = "[CapitalNeed] "
If IsNull(Me!CboOperator) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboOperator.SetFocus
Exit Sub
Else
strCrit3 = strCrit3 & Me!CboOperator
End If
If IsNull(Me!TxtAmount) Then
MsgBox "Please put a value in the textbox"
Me!TxtAmount.SetFocus
Exit Sub
Else
strCrit3 = strCrit3 & " " & Me!TxtAmount
End If
strCrit4 = "[State] "
If Me!CboState <> "All" Then
strCrit4 = strCrit4 & "= '" & Me!CboState & "'"
End If
strCrit5 = "[Division] "
If Me!CboDivision <> "All" Then
strCrit5 = strCrit5 & "= " & Me!CboDivision
End If
strCrit6 = "[Identity Rating] "
If IsNull(Me!CboFROperator) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboFROperator.SetFocus
Exit Sub
Else
strCrit6 = strCrit6 & Me!CboFROperator
End If
If IsNull(Me!TxtFRating) Then
MsgBox "Please put a value in the textbox"
Me!TxtFRating.SetFocus
Exit Sub
Else
strCrit6 = strCrit6 & " " & Me!TxtFRating
End If
strCrit7 = "[Exterior Rating] "
If IsNull(Me!CboExtROperator) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboExtROperator.SetFocus
Exit Sub
Else
strCrit7 = strCrit7 & Me!CboExtROperator
End If
If IsNull(Me!TxtExtRating) Then
MsgBox "Please put a value in the textbox"
Me!TxtExtRating.SetFocus
Exit Sub
Else
strCrit7 = strCrit7 & " " & Me!TxtExtRating
End If
strCrit8 = "[Interior Rating] "
If IsNull(Me!CboIntROperator) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboIntROperator.SetFocus
Exit Sub
Else
strCrit8 = strCrit8 & Me!CboIntROperator
End If
If IsNull(Me!TxtIntRating) Then
MsgBox "Please put a value in the textbox"
Me!TxtIntRating.SetFocus
Exit Sub
Else
strCrit8 = strCrit8 & " " & Me!TxtIntRating
End If
strCrit9 = "[Region] "
If Me!CboRegion <> "All" Then
strCrit9 = strCrit9 & "= " & Me!CboRegion
End If
strCrit10 = "[District] "
If Me!CboDistrict <> "All" Then
strCrit10 = strCrit10 & "= " & Me!CboDistrict
End If
strCrit11 = "[OP%] "
If IsNull(Me!CboOPPercOperator) Then
MsgBox "Please select =, >, <, >=, <= etc."
Me.CboOPPercOperator.SetFocus
Exit Sub
Else
strCrit11 = strCrit11 & Me!CboOPPercOperator
End If
If IsNull(Me!TxtOpProfitPerc) Then
MsgBox "Please put a value in the textbox"
Me!TxtOpProfitPerc.SetFocus
Exit Sub
Else
strCrit11 = strCrit11 & " " & Me!TxtOpProfitPerc
End If
strCrit12 = "[Grade] "
If Me!CboGrade <> "All" Then
strCrit12 = strCrit12 & "= '" & Me!CboGrade & "'"
End If
strCrit13 = "[SVRankGrp] "
If Me!CboSVRGrp <> "All" Then
strCrit13 = strCrit13 & "= '" & Me!CboSVRGrp & "'"
End If
strCrit14 = "[OPRankGrp] "
If Me!CboOPRGrp <> "All" Then
strCrit14 = strCrit14 & "= '" & Me!CboOPRGrp & "'"
End If
strCrit15 = "[OPPerRankGrp] "
If Me!CboOPPRGrp <> "All" Then
strCrit15 = strCrit15 & "= '" & Me!CboOPPRGrp & "'"
End If
strCrit16 = "[RONARankGrp] "
If Me!CboRONARGrp <> "All" Then
strCrit16 = strCrit16 & "= '" & Me!CboRONARGrp & "'"
End If
strCrit17 = "[Store] "
If Me!CboStore <> "All" Then
strCrit17 = strCrit17 & "= " & Me!CboStore
End If
strCrit18 = "[Status] "
If Me!CboStoreStatus <> "All" Then
strCrit18 = strCrit18 & "= '" & Me!CboStoreStatus & "'"
End If
If Me!CboGrade = "All" Then
Me!TxtConditionHdr.Visible = False
End If

DoCmd.OpenReport " FacilityIdentifyReportDR", acViewReport, WhereCondition:=strCrit1 & " And " & strCrit2 & " And " & strCrit3 & " And " & strCrit4 & " And " & strCrit5 & " And " & strCrit6 & " And " & strCrit7 & " And " & strCrit8 & " And " & strCrit9 & " And " & strCrit10 & " And " & strCrit11 & " And " & strCrit12 & " And " & strCrit13 & " And " & strCrit14 & " And " & strCrit15 & " And " & strCrit16 & " And " & strCrit17 & " And " & strCrit18
Forms("Frm_REPORT_Parameter_01").Visible = False
End Sub

[John_G]

Because your bolded code is in your form, Me! refers to the form, not the report. The code should fail if you have no control called TxtConditionHdr on your form.


Does this code run correctly, other than the formatting issue, and if it does, is it giving you the correct results? I have some issues with the WhereCondition.

Just to use a part of it, suppose Me!CboOPPRGrp, Me!CboRONARGrp, Me!CboStore , and Me!CboStoreStatus each have the value "All" (strCrit15 - strCrit18)

Then the last part of the where condition will be "[OPPerRankGrp] And [RONARankGrp] And [Store] And [Status]". Is this what you intended?

[state90]

The code does run correctly with the exception of the formatting. When "All" is selected it will show all grades and when "Excellent" or "Good", etc is selected the results fall in line with the criteria....I just cannot get it to be visible or invisible as desired (i.e., When "All" is selected). If it helps, the combo box [CboGrade] has two columns with the first listing the selection options the user sees and the second column is hidden and is the source for the query. So, when I say "All" the second collumn...and hence the piece used as criteria for the query is actually "" or null.

[state90]

To answer your question regarding the WHERE technically, every strCrit are criteria in the query. If all of those you listed are left blank on the criteria selection form then there really is no criteria selected....it is as though I left the criteria in the query for each of those blank and therefore it will list all data. However, if the OPPerRankGrp (which stands for Operating Profit % Rank Grouping) is chosen for "Top Third" (which is one of four options availabel for that with the options being "All", "Top Third", "Middle Third" and "Lower Third") then only those records with "Top Third" in the field will be returned.

To give a little more background on that.....OP % is source data that I ranked using SQL in another query and populated a OPPErRank field. Another query then used an IIF function to group the rankings into thirds and assign "Top Third", "Middle Third" or "Lower Third".

[state90]

Is there something I should use instead of "Me!"?

Something like?

If Me!CboGrade = "All" Then
RPT!TxtConditionHdr.Visible = False
End If

[June7]

I think code to format a report should be in the OnFormat event of whatever report section you want to apply formats to. Be aware that OnFormat event only runs for PrintPreview or direct to printer.

However, in the form procedure try:

Reports!FacilityIdentifyReportDR.TxtConditionHdr.V isible = False

Why the code to make report not visible?

[John_G]

June7's code suggestion will work, but you will have to move that bit of code after the doCmd.OpenReport.... statement. Leaving it where it is will cause an error because the form is not open yet.

The other option you have is to put the formatting in an On Format event of the report, something like this:

If Forms![Frm_REPORT_Parameter_01]!CboGrade = "All" Then Me!TxtConditionHdr.Visible = False

In this case, the Me! does refer to the report, because that is where the code is.

As June7 says, which On Format event you use depends on where the control to be hidden is.

[state90]

Sorry for my delay in responding.

The reason for the visible/invisible need is that depending on criteria I want the report header to list that criteria. There are a lot of fields related to the report but I could not fit them all in without the report being too wide but still needed them as criteria. For fields not listed in the body of the report but selected as criteria I wanted it to be visible in the report header. For example, I do not have the [Grade] of a store condition listed in the body of a report but if a specific [Grade] rating (i.e., Excellent, Good, Fair, etc...) is selected I wanted the the user to see that as selected criteria in the report header. If no specific [Grade] is selected then the text field in the header will be invisible as it is not specified criteria. In addition, there are a couple "nice to have" fields that are not listed in the body of the report such as [SVP]. If a specific Division is selected as criteria it is nice for the user to have the SVP in charge of that division's name to be listed. If a specific division is not selected as criteria then that SVP text field will be invisible.

[June7]

An option may be instead of trying to set Visible property, have IIf expression in report textbox ControlSource to be Null if condition met, otherwise show value. The question is how to pass the parameters to report.