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