Hiding a text box and its label when display of the info is unwanted

[John O'Malvern]

I've successfully used IIF() to hide a label to a textbox when the latter was null. but now i want to hide both textbox and label when the info being displayed is unwanted/inappropriate . This occurs when [Contact Type ID] is 25. (not my choice of field name with spaces!)
There are probably better ways, but my idea was to have a white rectangle cover the label and textbox on the form (which becomes a pdf, incidentally) when required, and be invisible when I want to display the info. (Crude, I know.)
I've tried IIf ([Contact Type ID]=25,[visible]=true,[visible]=false) as the control source for the rectangle, but Access says no:-
As you can probably tell, I'm very inexperienced in writing expressions (and code).
Solutions gratefully received!

 

[theDBguy]

Where are you using this expression? Are you using the form's Current Event?

 

[John O'Malvern]

Where are you using this expression? Are you using the form's Current Event?

as the control source for the rectangle, but Access says no:-

 

[MajP]

Control sources can only provide a value not do an action. I would go to events of the form. And try on the oncurrent event of the form to create a procedure in vba by clicking on the three dots. This should create an event procedure. Inside that something like

Private Sub Form_Current()
YourRectangleName.visible = ([Contact Type ID]=25)
end sub

 

[MajP]

You probably could do away with the rectangle and simply show/hid the correct controls. Before hiding a control you need to make sure it does not have focus. send it somewhere

Private Sub Form_Current()
   someNotInvolvedControl.setfocus
   someControl.visible = ([Contact Type ID]=25)
   someOtherControl.visible = ([Contact Type ID]=25)
   .....
end sub

 

[MajP]

FYI.
SomeControl.visible = ([Contact Type ID]=25)
The right side returns either true or false, and this format is a shorthand to toggle.

This would be the same as

if ([Contact Type ID]=25) then
  someControl.visible = true
else
  someControl.visible = false
end if

 

[John O'Malvern]

Sorry, everyone, this is on a REPORT, that gets turned into a pdf, and emailed.
If that makes any difference (mainly in the choice of events, I suspect.)

 

[John O'Malvern]

and this format is a shorthand to toggle.

Thanks for all the above, but can you explain this bit, please?

 

[MajP]

SomeControl.visible = ([Contact Type ID]=25)
if the the Contact type id = 25 then the code on the right side returns true and resolves to
SomeControl.Visible = True ' and it will be visible
If the contract type id <> 25 then
SomeControl.Visible = False

 

[MajP]

Sorry, everyone, this is on a REPORT, that gets turned into a pdf, and emailed.
If that makes any difference (mainly in the choice of events, I suspect.)

Sorry I missed this. Yes that makes a difference especially if it is on a continuous report.
On a continuous report in print preview you cannot hide controls. Once trick that work well with reports is to use conditional formatting. You make the controls forecolor and backcolor the same color as the report. It appears invisible.

Something like this in conditional formatting if the control you want to hide is Contact Type ID
Value; Equal to 25
if the control you want to hide is not the control with the value then use
Expression is: [contact type ID] = 25

 

[MajP]

I just remembered you cannot do conditional formatting on a label and you asked about a label. This is how I do it in a continuous report.

Private Sub Detail_Paint()
  If Me.Contact_Type_ID = 25 Then
    Me.Contact_Type_ID.BackColor = vbWhite
    Me.Contact_Type_ID.ForeColor = vbWhite
    Me.Label1.BackColor = vbWhite
    Me.Label1.ForeColor = vbWhite
    Me.Label1.BorderStyle = 0
    Me.Contact_Type_ID.BorderStyle = 0
  Else
    Me.Contact_Type_ID.ForeColor = vbBlack
    Me.Label1.ForeColor = vbBlack
    Me.Label1.BorderStyle = 1
    Me.Contact_Type_ID.BorderStyle = 1
  End If
End Sub

 

[John O'Malvern]

Right, good, with you! Thanks.

Sorry I missed this. Yes that makes a difference especially if it is on a continuous report.
On a continuous report in print preview you cannot hide controls. Once trick that work well with reports is to use conditional formatting. You make the controls forecolor and backcolor the same color as the report. It appears invisible.

Something like this in conditional formatting if the control you want to hide is Contact Type ID
Value; Equal to 25
if the control you want to hide is not the control with the value then use
Expression is: [contact type ID] = 25

Thanks. I'll come back to this in a few minutes.

 

[MajP]

a screen shot of the report would be useful. The solution is different for a continuous report vs single page. And would be different based on other formatting on you report.

 

[June7]

Aren't reports always continuous by nature? Sure, can force page break between records but report is still continuous.

Can set textbox and associated label not visible in report Format (maybe Print or Paint but I can't get those to work) event for section control is located in. Will not work if report is opened in ReportView, only PrintPreview or direct to printer or to pdf.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.[Contact Type ID].Visible = Me.[Contact Type ID] = 25
End Sub

If label is not associated, then add similar code.

Tested and works for me.

 

[MajP]

Aren't reports always continuous?

Maybe better so say only once within a section. Could be only seen once on a page.

Will not work if report is opened in ReportView, only PrintPreview or direct to printer.

Thanks, I got that backward.
So to clarify the code can be this simple if only viewing in print preview or to printer

Private Sub Detail_Paint()
  me.someControl.visible = (Me.Contact_Type_ID = 25)
  me.somelabelNoAssociatedToTheControl.visible = (Me.Contact_Type_ID = 25)
end sub

You need the formatting trick if planning to view in report view.

 

[John O'Malvern]

a screen shot of the report would be useful. The solution is different for a continuous report vs single page. And would be different based on other formatting on you report.

As things stand, when I go from design view to report view, it shows as continuous.
Maybe htat is a particular case?
I'm trying to hide the IAM No 12345678 under my signature, but only when Status shows Group Friend [not member]. GF is [Contact Type ID] = 25
the control source for the combobox "Combo49" showing [in my case Member] is

the SQL is SELECT DISTINCTROW [Contact Types].* FROM [Contact Types] ORDER BY [Contact Types].ContactType;

Which leaves me a little confused!! (what is a combo box doing on a Report, which is only used to generate pdfs.?)

 

[June7]

I can't get Print & Paint events to do this properly. Also, Paint will error if opened in ReportView, Format does not.

Do you have combobox (lookup field) set up in table? Did you drag field from Field List when building report or use wizard to build? This will adopt properties set up in table. Advise never to build lookup fields in table.

Also advise to never use spaces in naming convention.

Want to provide your db for analysis? Follow instructions at bottom of my post.

 

[John O'Malvern]

I can't get Print & Paint events to do this properly. Also, Paint will error if opened in ReportView, Format does not.

Do you have combobox (lookup field) set up in table? Did you drag field from Field List when building report or use wizard to build? This will adopt properties set up in table. Advise never to build lookup fields in table.

Also advise to never use spaces in naming convention.

Want to provide your db for analysis? Follow instructions at bottom of my post.

Short answer is I don't konw because I've inherited it from someone much more a user than me. But I haven't had the time (or effort!!) to sort it out. There's a lot which is sub-optimal!
But no, the table is decent:-

example rows [no drop-downs]

 

[John O'Malvern]

--- NB:
I'm trying to hide the IAM No 12345678 under my signature, but only when Status shows Group Friend [not member]. GF is [Contact Type ID] = 25

so i think the code above is the opposite - showing when [Contact Type ID] = 25

do I need to ensure the control is visible the rest of the time?

 

[Gasman]

Yes, should be <> 25