troubleshooting Question

Hide empty fields in Access 2010 form

Avatar of mrroonie
mrroonieFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft AccessVB Script
18 Comments1 Solution4850 ViewsLast Modified:

Hello all

I’ll start by saying I know next to nothing about Access; I know it’s a database system and pretty much everything else I know about it I have learned while trying to sort this issue out. If any suggestions are to input code anywhere, please tell me exactly where to put it, step by step.

Now the problem:

I am trying to help a friend with his database (I work in IT, network and infrastructure mostly, but as per most layman’s views of people in IT I MUST know how to do everything on every conceivable program known to man).

This database has a table view and a form view. In the form view we have some fields that may not always contain data for every record. For example if it was a database on employees there would be fields on ‘Spouse’ and ‘Kids’, etc etc. Not all employees are going to be married or have kids.

We would like the empty fields to disappear altogether from the form view for these particular records, but obviously be there for records where those fields are populated.

I have googled for a fair few hours but not hit the jackpot yet;

Many suggestions on forums say to use the ‘can shrink’ and ‘can grow’ attributes on the field – this doesn’t work. Some people have suggested combining ‘can shrink’ with ‘visible:no’ – using ‘visible:no’ hides the field altogether, populated or not.

Also nowhere does it mention if the field name can have a space or not – I have tried the following code in the ‘control source’ attribute, both with the field name with a space, and an under_score where the space would be, giving different results

=IIf([City] Is Null, Null, "City:")  - obviously changed ‘city’ to my field name. with a space in the field name it gives an error: You Have entered an Operand without an operator…?
With an underscore instead of a space it merely enters the field name into the box for all records.

I got the code from here -

I have also seen a few suggestions to input vb code – I have found where to open the database in vbs, but can’t see how to enter code for it

Any pointers would be greatly appreciated.

please let me know if that's as clear as mud and you need anything else explaining

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 18 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros