Hiding a text box on a report conditionally

[errolf]

I am using a report that creates a Price List. Under certain categories I dont want a second price to appear.
I am using the following code in report to try and make this happen. the code works for the first condition but will not operate on the "And" section of the line marked in red

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If [TxtDesc] = "Fertilisers Controlled Release Nutricote" Then
LblDisc.Visible = True
LblDisc.Caption = "Nutricote Quantity Discounts Buy 5 - 9 bags list less 5% Buy 10 - 19 bags list less 7.5% Buy 20 bags plus list less 10%"
LblDisc.Width = 3990
LblDisc.Height = 1450
LblDisc.Left = 3957
[CodeLbl].Top = 1815
[SizeLbl].Top = 1815
[PriceLbl].Top = 1815
[GstLbl].Top = 1815
[OuterLbl].Top = 1815
GroupHeader0.Height = 1450
ElseIf [TxtDesc] = "Fertiliser - Commercial" Then
txtPRICE.Visible = False
LblDisc.Visible = True
LblDisc.Height = 1500
LblDisc.Width = 3990
LblDisc.Left = 3957
[CodeLbl].Top = 1815
[SizeLbl].Top = 1815
[PriceLbl].Top = 1650
[GstLbl].Top = 1600
[OuterLbl].Top = 1800
GroupHeader0.Height = 1450
LblDisc.Caption = "Osmocote Quantity Discounts Buy 5 - 9 bags list less 5% Buy 10 - 19 bags list less 7.5% Buy 20 bags plus, list less 10%"
ElseIf [TxtDesc] = "Stakes Hardwood, Spiral & Tree Guards" Then
GroupHeader0.Height = 1150
LblDisc.Visible = True
LblDisc.Height = 300
LblDisc.Width = 8000
LblDisc.Left = 1500
LblDisc.Top = 600
LblDisc.Caption = "Ask about our discounts for bulk purchases of hardwood stakes"
[CodeLbl].Top = 850
[SizeLbl].Top = 850
[PriceLbl].Top = 850
[GstLbl].Top = 850
[OuterLbl].Top = 850
'GroupHeader0.Height = 1450
'LblDisc.Height = 1450
'GroupHeader0.Height = 1450
ElseIf [TxtDesc] = "Fertiliser - Commercial Water Soluble" Then
LblDisc.Visible = True
LblDisc.Width = 3990
LblDisc.Height = 1450
LblDisc.Left = 3957
GroupHeader0.Height = 1450
LblDisc.Caption = "PETERS Quantity Discounts Buy 5 - 9 bags list less 5% Buy 10 - 19 bags list less 7.5% Buy 20 bags plus, list less 10%"
LblDisc.Visible = True
LblDisc.Height = 1450
[CodeLbl].Top = 1815
[SizeLbl].Top = 1815
[PriceLbl].Top = 1550
[GstLbl].Top = 1550
[OuterLbl].Top = 1815
GroupHeader0.Height = 1450
LblDisc.Height = 1450
GroupHeader0.Height = 1450

ElseIf [TxtDesc] = "Stakes Bamboo Flower Sticks" Or [TxtDesc] = "Stakes Bamboo" _
Or [TxtDesc] = "Stakes Bamboo Nursery Grade Stakes" Then
LblDisc.Visible = True
LblDisc.Width = 5420
LblDisc.Height = 300
LblDisc.BorderStyle = 0
GroupHeader0.Height = 800
LblDisc.Caption = "Discounts on Bamboo Bulk Purchases apply"
LblDisc.Visible = True
LblDisc.Left = 3000
LblDisc.Top = 600
LblDisc.Height = 300
[CodeLbl].Top = 600
[SizeLbl].Top = 600
[PriceLbl].Top = 600
[GstLbl].Top = 600
[OuterLbl].Top = 600
'GroupHeader0.Height = 5.75
ElseIf [TxtDesc] = "Stakes Bamboo Nursery Grade Stakes" And [DESC] Like"GRO-MAX*" Then
txtPRICE.Visible = False
Else: LblDisc.Visible = False
[CodeLbl].Top = 300
[SizeLbl].Top = 300
[PriceLbl].Top = 300
[GstLbl].Top = 300
[OuterLbl].Top = 300
LblDisc.Height = 0
GroupHeader0.Height = 5.2
txtPRICE.Visible = True
End If
End Sub

Thanks in anticipation

Errol Featherstone

 

[dhookom]

I would abandon the If...ElseIf...Else... in favor of
Select Case TRUE
Case [TxtDesc] = "Fertilisers Controlled Release Nutricote"
'your code
Case [TxtDesc] = "Fertiliser - Commercial"
'your code
Case [TxtDesc] = "Stakes Bamboo Nursery Grade Stakes" _
And Left([DESC],7) = "GRO-MAX"

End Select
Keep in mind the first True statement results in not checking for any additional true statements. If [DESC] is not a control on your report, this code will fail. I believe if you move your red section up in the ElseIfs, it might work. Your 4th ElseIf is stealing its thunder.

Duane
MS Access MVP

 

[errolf]

Dear Duane,
Thanks for your reply and advice. I changed the cade as suggested but I still cannot get the text box display on only the required items. If I use [TxtPrice].visible = False then all ar visible or true non are visible.
This is now the code
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Select Case True
Case [TxtDesc] = "Fertilisers Controlled Release Nutricote"
LblDisc.Visible = True
LblDisc.Caption = "Nutricote Quantity Discounts Buy 5 - 9 bags list less 5% Buy 10 - 19 bags list less 7.5% Buy 20 bags plus list less 10%"
LblDisc.Width = 3990
LblDisc.Height = 1450
LblDisc.Left = 3957
[CodeLbl].Top = 1815
[SizeLbl].Top = 1815
[PriceLbl].Top = 1815
[GstLbl].Top = 1815
[OuterLbl].Top = 1815
GroupHeader0.Height = 1450
Case [TxtDesc] = "Fertiliser - Commercial"
[txtPRICE].Visible = False
LblDisc.Visible = True
LblDisc.Height = 1500
LblDisc.Width = 3990
LblDisc.Left = 3957
[CodeLbl].Top = 1815
[SizeLbl].Top = 1815
[PriceLbl].Top = 1500
[GstLbl].Top = 1600
[OuterLbl].Top = 1800
GroupHeader0.Height = 1450
LblDisc.Caption = "Osmocote Quantity Discounts Buy 5 - 9 bags list less 5% Buy 10 - 19 bags list less 7.5% Buy 20 bags plus, list less 10%"
Case [TxtDesc] = "Stakes - Hardwood"
GroupHeader0.Height = 1050
[txtPRICE].Visible = True
LblDisc.Visible = True
LblDisc.Height = 600
LblDisc.Width = 3990
LblDisc.Left = 3957
LblDisc.Top = 600
LblDisc.Caption = "Discounts apply for bulk purchases of hardwood stakes"
[CodeLbl].Top = 600
[SizeLbl].Top = 600
[PriceLbl].Top = 600
[GstLbl].Top = 600
[OuterLbl].Top = 600
'GroupHeader0.Height = 1450
'LblDisc.Height = 1450
'GroupHeader0.Height = 1450
Case [TxtDesc] = "STAKES - CANES - PRICING PER 100, RETAIL PACKS, SPIRALS & HOOPS" And Left([DESC], 7) = "GRO-MAX"
[txtPRICE].Visible = True
LblDisc.Visible = False
Case [TxtDesc] = "Fertiliser - Commercial Water Soluble"
LblDisc.Visible = True
LblDisc.Width = 3990
LblDisc.Height = 1450
LblDisc.Left = 3957
GroupHeader0.Height = 1450
LblDisc.Caption = "PETERS Quantity Discounts Buy 5 - 9 bags list less 5% Buy 10 - 19 bags list less 7.5% Buy 20 bags plus, list less 10%"
LblDisc.Visible = True
LblDisc.Height = 1450
[CodeLbl].Top = 1815
[SizeLbl].Top = 1815
[PriceLbl].Top = 1550
[GstLbl].Top = 1550
[OuterLbl].Top = 1815
GroupHeader0.Height = 1450
LblDisc.Height = 1450
GroupHeader0.Height = 1450
Case [TxtDesc] = "Stakes - Cane Bales" Or [TxtDesc] = "Stakes Bamboo" _
Or [TxtDesc] = "Stakes Bamboo Nursery Grade Stakes"
txtPRICE.Visible = False
LblDisc.Visible = True
GroupHeader0.Height = 600
LblDisc.Width = 3990
LblDisc.Height = 1450
LblDisc.BorderStyle = 1
LblDisc.Caption = "Discounts on Bulk Purchases of canes apply"
LblDisc.Visible = True
LblDisc.Left = 3000
LblDisc.Top = 600
LblDisc.Height = 600
[CodeLbl].Top = 600
[SizeLbl].Top = 600
[PriceLbl].Top = 600
[GstLbl].Top = 600
[OuterLbl].Top = 600
GroupHeader0.Height = 5.75
Case Else: LblDisc.Visible = False
[CodeLbl].Top = 300
[SizeLbl].Top = 300
[PriceLbl].Top = 300
[GstLbl].Top = 300
[OuterLbl].Top = 300
LblDisc.Height = 0
GroupHeader0.Height = 5.2
txtPRICE.Visible = True
End Select
End Sub

 

[dhookom]

Once you set some control property, it stays that way until you change it to some other value. I would set a Break Point in the code and step through the code to see the actual values of controls etc.
Have you tried to create a query with a where clause of WHERE txtDesc =
"STAKES - CANES - PRICING PER 100, RETAIL PACKS, SPIRALS & HOOPS" And Left([DESC], 7) = "GRO-MAX"
Substitute the field that is bound to txtDesc.


Duane
MS Access MVP