vba not working for subreport until i click on it

[tagteam]

I have a field that I only want to show if it is anything other than two values so I have this in the on load event of the subreport



If ownedleased.Value = "Owned" Or ownedleased.Value = "Leased" Then
Me.ownedleased.Visible = False
Else
Me.ownedleased.Visible = True
End If

the problem is that it only works if i open the report in report view AND then click on the subreport section.

Is there any way to get this to work on load without clicking on it and if it is in print preview mode.
Thanks

[Micron]

I think you will have to use conditional formatting and format the control to make it look invisible (font/border/background same colour as page) because
a) there is no visible option for CF
b) you have to do this (code) in the detail format event, not open or load
c) all instances of the control will be visible (or not) because there is only one of them in reality

EDIT - re: c), I think all will be invisible if the first record satisfies the test (result is true) but perhaps the last instance of the control will be invisible if the last record satisfies the test. No idea what happens if any records in between satisfy the test. Likely all that follow a true result in the middle of the records will be invisible. You might get this to work if you explicitly make visible and invisible.

EDIT 2 - sorry, I forgot that you are already explicitly setting visible property. I tested and it works for me, so try your code in the detail format event and open in print preview

[Gicu]

As suggested you might try to move the code to the format or print events of the section containing the control (of the subreport itself) or even easier remove all the code and add a calculated field to the subreport's record source:

Owned_Leased:IIf([ownedleased] IN ("Owned","Leased"),"",[ownedleased])

Now simply use this as the control source instead of the original "ownedleased".

Cheers,

[Micron]

As long as I open the report in print preview, it works, even when records that satisfy the test are interspersed. One could condense the code as:

Me.ownedleased.Visible = Not (ownedleased = "Owned" Or ownedleased = "Leased") if you want invisible when those values are present, or omit Not for the reverse.
.Value property is the default so it's not needed, nor is it wrong to include it I guess.

[tagteam]

Cant do it in the on paint event. You get the error "32521 - You cant change the value of this property in the OnPaint event

If i do it in the detail on Format event it shows the field no matter what
Me.ownedleased.Visible = Not (ownedleased = "Owned" Or ownedleased = "Leased")

OR

If Me.ownedleased = "Owned" Or Me.ownedleased = "Leased" Then
Me.ownedleased.Visible = False
Else
Me.ownedleased = True
End If


Micron, your idea with the conditional formatting seems to be the only one that works consistently without some kind of intervention. I set two conditional formatting rules one for Owned and one for Leased and set the background to white and text to white. Then the field itself is red bg with white text. This has the added benefit of showing a blank red box if that field value has not been filled in because it should have a value.

[Gicu]

You should have tried the Print event not the Paint event.

Cheers,

[Micron]

Well, in truth I lost something in my translation. I actually had Me.myControlName but I swapped that for your names and forgot the Me. So try this in the detail format event:
Me.ownedleased.Visible = Not (Me.ownedleased = "Owned" Or Me.ownedleased = "Leased")

Sorry about that. As I said, this did work for me regardless of what the value was in the record. Of course, I had to use my own values as the test parameter but that should not matter.

[tagteam]

This may be a quirky Access thing because I was using code similar to yours Micron and it was working. Then after some time and a few changes to the form, though not in that section, it stopped working. The conditional formatting method seems to be the most stable and works well for me so I will stick with it.

Thanks

[Micron]

You're welcome.