using ColumnHidden properties within Datasheet objects

Question

I have a MS Access form with a Datasheet subform.
Using code, I change the ColumnHidden property of various of its columns. But, when I close the form, I'm asked whether to save the table layout of the Datasheet's table.

• How can I stop the form from asking the user to same the table layout continually?
• Do I have no choice but to change the Datasheet to a regular subform?

Answer 1

If you are always hiding the same columns - create a query that only has the columns you want displayed and use that as the source of your subform.

If you still need some hidden fields (child keys, etc.) you will have to create a regular form. It's not too bad:

• just base it on your table,
• drag your columns onto the form (formatting, and placement doesn't matter)
• set it to datasheet view
• edit labels (these become column headings)
• change the tab order (this controls the order the columns are displayed)
• view the datasheet and hide the columns you want
• save the form
• add this form as a subform to the main form

Answer 2

I'm not sure there's an easy way to do this because Access saves a lot of properties with forms. If you were not using Datasheet view, you could hide the CLOSE button on the form and replace it with a command button to close the form with this code:

  DoCmd.Close acForm, Me.Name, acSaveNo

But since it's a datasheet, you can't do that.

You might try embedding your datasheet as a subform in an unbound parent form, but I don't know if using the code above behind a command button on the parent form will or will not save the column widths to the embedded subform.

One solution would be to re-initialize the column widths in the OnLoad event of your datasheet. You could then open the form with acHidden, and in the OnLoad event, set the column widths to their correct values (you'll want to multiply inches times 1440 to get the twips value to assign to the column width properties), and at the end of the OnLoad, set Me.Hidden to False.

But that is, indeed, something of a pain in the butt.

I'm afraid it's one of the downsides of using datasheets, since there's no way to define a close command button that won't save end-user UI data.