Dynamically hide or show controls on a form based on user selections

[Sck]

I have a table that has 130 fields (attributes to each specific item) the user wants to be able to select the fields that they need to see at any specific time. to enable this I have a form linked to a table that contains all the attributes and control names (not visible to user) and a y/n field. I have a viewer form that has all the controls on it (displayed as a grid view). when the user opens the viewer form, I want to loop through the list of items they want to see and the list of controls and only show controls they want to see.

trying to do something like this on the form load event (assume all controls start as not visible and rst is a recordset of the control names they do want to see)

dim ctl as control

for each ctl in me.controls



rst.movefirst
do until rst.eof
if clt.name = rst.name then ctl.visable = true


rst.movenext
loop


I cant get to the .visible property for the controls

any help is GREATLY appreciated

[pbaldy]

Is Visible spelled correctly in the actual code (because it isn't here)?

[Sck]

Is Visible spelled correctly in the actual code (because it isn't here)?

yes have to post here from a different computer than my work computer.

when I type ctl. visible is not an option in the popup list???

[pbaldy]

That is sometimes the case. Have you tested the code to see if it works?

[pbaldy]

By the way, it doesn't sound like your table is normalized. Typically there would be a related table with a record for each attribute.

[Sck]

don't disagree but this is a export from a larger system and the data comes in as a single large table.

I have it working if I open the form in form view (control is hidden) but it doesn't hide the field if it is opened in datasheet view. because of the amount of data, I need it in datasheet view. thoughts?

[Sck]

never mind .columnhidden got it working Thanks!

[pbaldy]

Glad you got it sorted. I don't use datasheet much, would probably have suggested continuous.

[Sck]

have it working BUT if the first control is not selected as one to view, i get an error (cant hide control that has focus) when it tries to hide it. have set all controls to TabStop = no how do I check if the control has focus and if it does move focus to the next displayed control

just want to automate just like clicking TAB when the form is displayed (goes to the next displayed control in the Tab Index

I tried just adding a default bogus control but that shows up in the DataSheet view which I don't want

[Bulzie]

Have you tried building the Record Source for the Form(datasheet) in the OnOpen event? Syntax might not be right but general idea.


dim ctl as control

vRS = "Select "

for each ctl in me.controls

rst.movefirst
do until rst.eof

if clt.name = rst.name then

vRS = vRS & ctl.Name & ", "

rst.movenext

loop

vRS = Right(VRS, len(vRS-1)) & " From tblYourTable" '(first part gets rid of last comma)

me.RecordSource = vRS
me.Requery

[pbaldy]

I like Bulzie's idea. If you stick with hiding, you can add an error trap for that specific error and have it skip that field/control.

[Sck]

Have you tried building the Record Source for the Form(datasheet) in the OnOpen event? Syntax might not be right but general idea.


dim ctl as control

vRS = "Select "

for each ctl in me.controls

rst.movefirst
do until rst.eof

if clt.name = rst.name then

vRS = vRS & ctl.Name & ", "

rst.movenext

loop

vRS = Right(VRS, len(vRS-1)) & " From tblYourTable" '(first part gets rid of last comma)

me.RecordSource = vRS
me.Requery

that is basically what I am doing (almost exactly) however when the form (it is a subform) opens it places focus on the first control. so if the user does not want to see that field in their view, it errors. I need to build an error handler to move the focus to a different control (that is visible) then continue hiding the control they don't want to see.

[Bulzie]

Do you have any button on the form header or footer for adding new records or Save etc. You can move the focus to one of those control buttons.