Hiding label & text control if null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating new forms and have several columns in my associated table that
don't always have data in.

How do I hide the label and text controls on the form if there is no data in
the associated field?
 
If your form is in Form view, you could use the Current event of the form to
loop through the form's Control Collection, and set the Visible property of
any that are Null, using error handling to avoid the controls that don't
have a Value property (such as lines and labels.)

Not sure that would be very useful though: it would make it rather difficult
to enter a value into any Null field, and at the new record you would be
hard pressed to enter anything anywhere.

In a Continuous form or datasheet, you cannot hide the field selectively
(i.e. cannot hide the field on only those rows where is it null.)

We don't know much about your table, but the question hints at a design that
may not be normalized. You might like to run the table analyzer (Tools |
Analyze | Table) to see if it has any suggestions on your structure.
 
You points are sound; however, it is not unreasonable to design such a form.
Admittedly, it would be no good for data entry. It would be good for users
who have read only capability. Certainly not a normal circumstance, but not
usual.

I have seen similar situations where some users should not see certain data.
For example, a Payroll system. In this case, rather than maintain two
similar forms, it is reasonalbe, or actually for me, preferred, to have one
form with the intelligence to hide controls based on who using it.
 
Thanks Allen,

The table is a list of companies, with address, telephone, website etc. The
fields that are often null are things like address3 (not all addresses have a
3rd line) and country (I am UK and if they are UK too it is left blank)

It is form view. Users will not be updating fields in the table via this
form. When you talk about the 'current event', I take it you mean use VBA to
run when the form displays data - and have it looking at every field checking
whether it is null. I do not have any VBA writing experience - can you advise
on what code I should use?

Is there not another way to do this? My form contents seem quite simple (its
essentially a list of names and addresses) so I can't believe other people
have not come across it.
 
Okay, Alison

If this is a read-only display, and you want to combine fields to save
space, you could set the ControlSource of a text box to:
=[Address1] & " " & [Address2] & " " & [Address3] & " " & [Country]

That should work provided you make sure the Name of this text box is not the
same as any field name (e.g. it cannot be called Address1.)

I should be clear that setting the Visible property of the text boxes will
not give you any more space on the form, i.e. they don't shrink like text
boxes on reports do. Since you are producing read-only results anyway, you
might find it easier to show the users a report rather than a form,
especially if you want the typical address panel read out. In the report,
you can set the Can Shrink property of Address2 etc to Yes, and the lower
boxes will close up (provided there is nothing else on the same line
preventing them from shrinking.)

If you want to set the Visibility of the controls in the Current event
procedure of the form, you would write your VBA code like this:
Private Sub Form_Current()
Me.[Address1].Visible = Not IsNull(Me.[Address1].Visible)
Me.[Address2].Visible = Not IsNull(Me.[Address2].Visible)
End Sub
 
Many thanks Allen.

I want to use forms, not a report - because I wanted to allow users to
'filter by form'. I have, however, just realised that if I set the fields on
the form to be non-updateable the users can also not use the filter functions
on them!

Bearing in mind this filter by form is essential, I am thinking a better
option would be to leave fields as updateable so they can filter, but instead
add VB code to the before update event - adding a message saying something
like 'you are unable to update from this screen' and ensuring the update does
not happen. Is this possible?

Thanks!


Allen Browne said:
Okay, Alison

If this is a read-only display, and you want to combine fields to save
space, you could set the ControlSource of a text box to:
=[Address1] & " " & [Address2] & " " & [Address3] & " " & [Country]

That should work provided you make sure the Name of this text box is not the
same as any field name (e.g. it cannot be called Address1.)

I should be clear that setting the Visible property of the text boxes will
not give you any more space on the form, i.e. they don't shrink like text
boxes on reports do. Since you are producing read-only results anyway, you
might find it easier to show the users a report rather than a form,
especially if you want the typical address panel read out. In the report,
you can set the Can Shrink property of Address2 etc to Yes, and the lower
boxes will close up (provided there is nothing else on the same line
preventing them from shrinking.)

If you want to set the Visibility of the controls in the Current event
procedure of the form, you would write your VBA code like this:
Private Sub Form_Current()
Me.[Address1].Visible = Not IsNull(Me.[Address1].Visible)
Me.[Address2].Visible = Not IsNull(Me.[Address2].Visible)
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Alison said:
Thanks Allen,

The table is a list of companies, with address, telephone, website etc.
The
fields that are often null are things like address3 (not all addresses
have a
3rd line) and country (I am UK and if they are UK too it is left blank)

It is form view. Users will not be updating fields in the table via this
form. When you talk about the 'current event', I take it you mean use VBA
to
run when the form displays data - and have it looking at every field
checking
whether it is null. I do not have any VBA writing experience - can you
advise
on what code I should use?

Is there not another way to do this? My form contents seem quite simple
(its
essentially a list of names and addresses) so I can't believe other people
have not come across it.
Click to expand...
Click to expand...
 
You should be able to get the filter working if you set the Locked property
of the controls to Yes (leaving Enabled as yes), or set the AllowEdits of
the form to No.
 
Hi Allen,

My table and form are called 'organisation'. The field I want to hide if
null is called 'country'. I put the following code in:

Private Sub Form_Current()
Organisation.[Country].Visible = Not IsNull(Organisation.[Country].Visible)
End Sub

but I get a runtime error 438 - object doesn't support this property or
method. Is it obvious what I am doing wrong?
 
Try:

Private Sub Form_Current()
Me.[Country].Visible = Not IsNull(Me.[Country])
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Alison said:
Hi Allen,

My table and form are called 'organisation'. The field I want to hide if
null is called 'country'. I put the following code in:

Private Sub Form_Current()
Organisation.[Country].Visible = Not
IsNull(Organisation.[Country].Visible)
End Sub

but I get a runtime error 438 - object doesn't support this property or
method. Is it obvious what I am doing wrong?

Allen Browne said:
You should be able to get the filter working if you set the Locked
property
of the controls to Yes (leaving Enabled as yes), or set the AllowEdits of
the form to No.
Click to expand...
Click to expand...
 
Back
Top