Hide empty cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1724681%22%20slang%3D%22fr-FR%22%3EHide%20empty%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1724681%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3EI%20have%20created%20a%20form%20that%20lists%20company%20files.%20Each%20company%20has%20its%20own%20form%20and%20each%20form%20contains%20a%20sub-form.%3C%2FP%3E%3CP%3EIn%20the%20sub-form%2C%20I%20created%20several%20lines%20containing%20one%20country%20per%20line%20and%20in%20the%20same%20sub-form%20the%20(around%20ten)%20are%20data.%3C%2FP%3E%3CP%3EHowever%2C%20it%20happens%20that%20for%20some%20companies%20there%20is%20no%20data%20for%20some%20columns%2C%20which%20implies%20that%20column%20remains%20empty.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EThus%2C%20imagine%20a%20sub-form%20for%20a%20company%20can%20have%203%20columns%3A%20one%20with%20the%20name%20of%20the%20country%2C%20one%20with%20the%20country's%20GDP%20and%20one%20with%20the%20country's%20political%20risk.%20There%20are%20countries%20for%20which%20the%20GDP%20information%20is%20available%20but%20for%20others%20it%20is%20not%20the%20case.%20So%2C%20if%20GDP%20cell%20is%20empty%20in%20all%20the%20lines%2C%20I%20would%20like%20to%20hide%20this%20column%20and%20leave%20only%20the%20columns%20that%20contains%20data.%3C%2FP%3E%3CP%3EThe%20question%20is%3A%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20display%20the%20columns%20automatically%20whether%20the%20cell%20is%20empty%3F%3C%2FP%3E%3CP%3EIn%20fact%2C%20I%20want%20the%20form%20columns%20that%20have%20no%20data%20to%20be%20hidden%2C%20but%20I%20can't%20make%20a%20filter%20in%20the%20request%20columns%20because%20otherwise%20it%20applies%20for%20all%20lines%2C%20and%20the%20data%20available%20is%20not%20the%20same%20for%20all%20of%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20it's%20clear%20enough%20and%20you'll%20can%20help%20me!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1724681%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1758802%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20empty%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1758802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813306%22%20target%3D%22_blank%22%3E%40bltsr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20made%20an%20example%20Access-file%20that%20has%20an%20implementation%20of%20the%20functionality%20you%20sketched%2C%20but%20I%20can't%20drop%20this%20file%20as%20an%20attachment%20here.%20Maybe%20you%20could%20provide%20an%20email%20address%20where%20I%20can%20send%20it%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20uses%20a%20small%20piece%20of%20VBA-code%20to%20hide%2Fshow%20the%20GDP%20whether%20being%20empty%20or%20not.%26nbsp%3BMaybe%20you%20can%20elaborate%20on%20the%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%2C%3C%2FP%3E%3CP%3ETieme%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1759152%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20empty%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1759152%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813306%22%20target%3D%22_blank%22%3E%40bltsr%3C%2FA%3E%26nbsp%3BMay%20I%20suggest%20an%20alternative%20that%20wouldn't%20require%20manipulating%20controls%20in%20a%20form%3F%3C%2FP%3E%3CP%3ERather%20than%20trying%20to%20show%20or%20hide%20them%2C%20why%20not%20DISPLAY%20a%20default%20value%20like%20%22N%2FA%22%20for%20those%20records%20where%20the%20value%20is%20not%20available.%20That's%20a%20POSITIVE%20way%20to%20convey%20the%20information%2C%20as%20opposed%20to%20the%20PASSIVE%20approach.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello everyone,

I have created a form that lists company files. Each company has its own form and each form contains a sub-form.

In the sub-form, I created several lines containing one country per line and in the same sub-form the columns ( around ten) are data.

However, it happens that for some companies there is no data for some columns, which implies that column remains empty.  

Thus, imagine a sub-form for a company can have 3 columns: one with the name of the country, one with the country’s GDP and one with the country’s political risk. There are countries for which the GDP information is available but for others it is not the case. So, whether GDP cell is empty in all the lines, I would like to hide this column and leave only the columns which contains data.

The question is:

=> Is it possible to display the columns automatically whether the cell is empty?

In fact, I want the form columns that have no data to be hidden, but I can't make a filter in the request columns because otherwise it applies for all lines, and the data available is not the same for all of them.

 

I hope it’s clear enough and you’ll can help me !

4 Replies
Highlighted

@bltsr 

I made an example Access-file that has an implementation of the functionality you sketched, but I can't drop this file as an attachment here. Maybe you could provide an email address where I can send it to.

 

It uses a small piece of VBA-code to hide/show the GDP whether being empty or not. Maybe you can elaborate on the example.

 

Best wishes,

Tieme

Highlighted

@bltsr May I suggest an alternative that wouldn't require manipulating controls in a form?

Rather than trying to show or hide them, why not DISPLAY a default value like "N/A" for those records where the value is not available. That's a POSITIVE way to convey the information, as opposed to the PASSIVE approach.

Highlighted

@Tieme Woldman Thanks for your answer ! Indeed, it could be very useful ! I'll send you a private message to give you my mail address. 

 

Highlighted

@George Hepworth Thank you for your answer and this alternative ! I'll try it if I can't find something else, but it can be a really good solution !

Have a nice day