Hide Columns in Datasheet

Make the width of the columns you want hidden 0.

The problem isn't hiding the columns, this option is still available on Access' own pop up menu. The problem is Unhiding them after they are hidden as you can in a datasheet that isn't a subform....thanks for your response though

If the only thing you want to do is prevent users from seeing some of the colums , create a query containing only the columns your want and base your form on this query.

I want them to be able to hide and unhide a specific column as and when they choose. If you look at a datasheet and right click the title bar you will see a menu item "Unhide". This is what I am trying to achieve.

I have not done this, but it should be technically possible.

Have a command button on the main form, that users can click on.

This can list the controls in the subform, along with the columnhidden property. These can be displayed using a pop-up form. Users can then decide which controls should be hidden. On the close event of the pop-up form, you can go thru controls of the subform and change columnhidden property of those controls that user has selected.

While checking the controls collection, you may have to filter out the irrelevant ones. One easy way would be to name all relevant controls beginning with a particular series of letters.

This is more or less what I did originally....the problem comes when you need to make a generic dialog box which is dynamically created. I have about 10 of these datasheet forms and I was trying to avoid writing a specific dialog box for each based on their column heaing names. Also to avoid having to change anything should I need to add or remove fields for some reason. I really don't know why Microsoft chose to disable this particular function in subform datasheets. Thank you all for your responses

Sorry nirmala I just read your comment again. Yes using a list box is a possibility but it's not as satsfactory as the MS dialog somehow.

If you mark more than one column and then dbl-click in the border between any of them, every hidden column in the marked area will become visible again.
OddEH

This is true, but the problem is it requires the user to know where the column is that he is trying to unhide. If you have several hidden columns this becomes very hit and miss., which is why the dialog box is good because it lists all available columns and shows whether they are hidden or not. Clicking on a check box then toggles that state.

I suppose what I'm really after is either the API call to access this dialog box directly or some means of basing a query on a collection without storing the information in a temporary table. Thanks for the response though

You must do something like this:
For each field in form
  field.ColumnHidden=false
next

Regards, Arve

Arve, that will hide/unhide all columns, which isn't exactly what I want to do. Currently the customer has this as a "next best" but he wants to be able to show and hide any column by selecting it from a list as you can from the menu in a table or datasheet when it's not a subform. As I said in my original post, I have found a work round but it means creating a temporay table an then immediately deleting it when my dialog is closed.

Thanks for responding though.

Arve, I just spent most of the morning doing more or less the same thing, with the added refinement of creating an array of column headings which makes the routine generic. Still we were in the same ball park, so have the points if it means that much to you :)