Show / Hide Datasheet Columns in a Subform

[DeveloperSteve]

I've got a main form with two lists boxes. I want to show the visible columns in my subform (which is a datasheet) in one listbox and show the hidden columns in the other. Also I want to allow the user to hide / show columns using right or left arrow buttons between the list boxes. My subform is bound to a stored procedure using ADO.

Has anyone seen something similar I could start with?

 

[vbaInet]

I don't fully understand what you're trying to accomplish here Steve. What do you mean by show hidden columns? You mean the names of the fields that are hidden? Please elaborate.

 

[DeveloperSteve]

The goal is to allow the user to hide or unhide columns of a subform in datasheet view. The datasheet is a subform and bound to a stored procedure. I'd like the main form to have two list boxes: one showing the user the list of currently visible columns, and the other listbox showing currently hidden columns. Then allow the user the hide or unhide the datasheet columns as needed.

First I need to populate each list box by reading the ColumnHidden property for each column in the datasheet. Then I need to update the ColumnHidden property if the user updates a listbox (by selecting an item and sending it to the other listbox with a button).

I'm having trouble populating the list boxes as I cannot successfully reference the columns in the datasheet subform. I'm hoping someone can point me in the right direction here.

I realize this function is sort of built-in by right clicking a column header of a datasheet and then selecting Unhide Fields, then checking and unchecking columns as desired. I've been tasked with this as my boss is of the opinion that the built-in feature is not user friendly enough. (He objects to the name of the dialog box as it could be used simply to hide columns rather than unhide them.)

 

[vbaInet]

Well, have you told your boss that if the user decides to right-click the field and hide it through the context menu, it will not show up in your list of hidden fields? There's no event for this activity. Unless you "disable" the right-click completely.

 

[DeveloperSteve]

Hey - that's a great argument. I'll give it a shot. Thank you.

 

[vbaInet]

Let us know if you get a payrise from that too

On a serious note, if you still want to go ahead despite the setbacks, let us know.