textBox Value making label visible

[barkly]

Stumped on this – appreciate some help.



I have a text box (txtPartSaleSum) in the footer of a form with a control =Sum(IIf([Partsale]="Yes",1,0))

which simply sums the occurrences of the text value “Yes” in field PartSale on the form.

I also have a label, (PartBaltxt2) also in the footer of the same form, which I wish to make visible if the sum value in the text box is >= 1.

I just cannot get this sum value across to some simple code that would fire the label visible = true process.

Dim LPST2 As Long
 
     LPST2 = Me.txtPartSaleSum
 
 
MsgBox "LPST2 Yes = " & LPST2
 
    If LPST2 >= 1 Then
 
       Me.PartBaltxt2.Visible = True
       
    End If

(I can see the value of the sum in the text box but I cannot see a value other than 0 in the MsgBox debug aid).

Don’t know whether I should be trying to execute this in On Current, On Open, On Load or where else - having tried many of the events unsuccessfully.

Thanks in advance

[June7]

Simpler code structure:

Me.PartBaltxt2.Visible = Me.txtPartSaleSum > 0

I tried this. If I simply open the form, the label doesn't show. If I set a breakpoint on the code then open form, the label will show after stepping through code.

Seems the code executes before all the data loads and stepping through code delays the code long enough for data to load.

Why do you need this? Maybe we can come up with something else to accomplish. Maybe another textbox instead of label with an expression to display text or return Null (or empty string). Set the textbox as transparent BorderStyle and BackStyle and when returns Null, it won't be seen.

=IIf([txtPartSaleSum]>0, "Show this text", Null)

[SteveH2508]

Why not use a calculated text box to display (or not) the caption that you want? Lock it, remove TabStop and format it like your label. Set the controlsource to something like

=IIf(Sum(IIf([Partsale]="Yes",1,0))>=1,"YourTextHere","")

[barkly]

Hi June7 and SteveH2508,
Thanks for your quick responses.

June7, as you say the code executes before all the data loads. Why do I need this? When I have a value 'yes' in the [PartSale] field I conditionally format the row to a different colour. The label then when Visible was to describe briefly what the row meant when it was a different colour.

Steve, yes I tried your suggestion and it works but it only turns the caption text on or off. The label colour formatings are always present which I was trying to avoid. ie if there are no 'yes's then the label would be visible = false. Can a text box self control itself? ie if its value < 1 then it is not visible, if it is > 1 then it is visible.

Thanks again

[June7]

Did you try my suggestion to set the BackStyle and BorderStyle properties to Transparent?

Conditional Formatting does not allow setting Visible property.

[barkly]

Hi June7,

No I hadn't tried your suggestion at the time but I have now.

I have ended up with this as the control of a text box -

=IIf(Sum(IIf([Partsale]="Yes",1,0))>=1,"Part Sale of Initial Holding",Null)

I thus have the best of yours and that of SteveH's in the one compilation.

I was actually hoping to have the text box background colour the same as the colour of the line that I had conditionally formatted but I have made this transparent and so now the text has the same colour. This has turned out to be satisfactory.

Thankyou to yourself and Steve for your interest - appreciated.

barkly