Form columns pre selection using VBA

dim

Registered User.
Local time
Today, 18:42
Joined
Jul 20, 2012
Messages
54
Hi,

I have a table "Preference" where I can select (Yes or No) the desired columns to be display in a form "Presentation" where are A, B, C, D and E columns.
What I need:
If for example in the table Preference the column selected are A, C and D, then the form will show only those three columns A, C and D. The others columns B and E have to be hifdden.
I found a way using columnHidden property, but is working in datasheet form only.
I need to apply this preferred selection on the regular form where I have others more functions.

Thank You!
 
Last edited:
In the form , ONLoad event
TxtBoxA.visible = ColASetting.visible
TxtBoxB.visible = colBSetting.visible

(However you configs are stored)
 
The basic code would require the use of a loop and Dlookup()'s to identify which FIELDS (columns are used in spreadsheets!) to display. If the field is to be hidden use the Me.ControlName/FieldName.Visible = False.

Where to place the code is dependent on when the diplayed fields could change. Every record? Only on open? only when refreshed?????
 
To simplify let say that I want to hide column B when I open the form which normally show all 5 columns (A,B,C,D,E)
So for the column B, I have the label_B and field_B and to hide this column I tried this on load event:

[label_B].Visible = False
[Field_B].Visible = False

This will hide the content of the both (label and field), but the free space will remain on the form.
I need something which will completely eliminate this empty space.
To do this I tried:

[Label_B].Width = 0
[Field_B].Width = 0

This could be ok, but I’m not sure if this is the right way to do it…

Thanks
 
The way to tidy this up is to use the left property of the visible boxes. A little more code (and a lot of working out first). If all 5 field controls (lets just call them fields!) are the same width it is simply a case of noting the left property of each:eek:. If the fields are differing widths you will need to note their widths also.:banghead:

Then in your code when you hide a field (lets say B), set the left properties of the remaining fields (C,D & E) to their new values - C=B, D=C & E=D. Repeat...

If you have different field widths you will need to work out the left position as (left property of previous field + width of previous field + gap). Now to throw another spanner in the mix, measurements are done in 'twips' (look it up for conversion).:banghead::banghead::banghead:

It takes a little bit of working out, but once you have it mastered the asthetics of your form will be professional-ish :D
 
Thank You Isskint,

Using a layout, I don't need to resize the others fields, because all the remeaning active fields will automatically align, keaping their original size.
 

Users who are viewing this thread

Back
Top Bottom