Creating options menu for MS Access 2007

[TheArda]

Hi i want to create some type of options menu for my program. My Program collects bunch of data from an automated system , records it to an access database and trends it. I want to create an option form to activate or deactivate trend entries so that way my user can change what they want to see on their chart.Also if i can give them the option to change the tag from the legend that would be perfect for that part i think i can use smart tags .For the first par what i planed was creating checkboxes and using those with if statements to add/remove columns from the pivot table. So my question is there any VB code to add remove column fields on a form.

[Stewart Ross]

Hi TheArda, and Welcome to Bytes.

Generally, controls are not added or taken away from forms under VBA control - such changes can only be made in the form's design view, and setting the position of the controls in relation to all others would be somewhat of a challenge.

Anyway, the much simpler way to do it is to design the form so that all necessary controls are already present, then set each control's visible property to true to show the control or false to hide it as necessary from VBA code, typically placed in the On Load or On Current events of the form concerned (depending on whether it is a change only when the form loads, or potentially for each record shown on the form).

You can also control from VBA the labelling associated with each control so that a single control can present different 'What I do' messages to users according to the circumstances you want to show. This approach was used by Access switchboards in older versions of Access (I don't use 2007 - it may or may not still be used) where the labels for the switchboard controls were the menu options presented to users, defined and stored in a table and presented according to which switchboard was being shown.

-Stewart

[ChipR]

It is possible to hide/show columns in a datasheet view of a table through vba with the ColumnHidden property, but I haven't used a pivot table, so I'm not sure that will work.