Hide Datasheet Columns in Split Forms

I have heard many complaints about using a Split Form since its introduction in Access 2007. The main purpose of a Split Form is to display the current record in a Single Form View while also displaying the form's record source in a Datasheet View. As the user navigates the records on the form, both the single and datasheet forms will reflect the current record. Figure 1 shows an example of a typical Split Form.

The biggest complaint I've heard about Split Forms is the apparent inability to hide the datasheet columns using VBA.
Before Split Forms, developers had to use a subform in datasheet view and executing either of the following code will easily hide a datasheet column:

Me.SubformContainerName.Form!ColumnName.ColumnWidth = 0

or

Me.SubformContainerName.Form!ColumnName.ColumnHidden = True

However, executing the above codes in a Split Form does not seem to work.

Actually, the above codes can also work on a Split Form, but the change is not reflected immediately. The Split Form must be saved, closed, and reopened before the change can be seen. And in my experiments, simply using Me.ColumnWidth or Me.ColumnHidden seems to work inconsistently depending on certain factors. As such, the above approach is not a very good solution.

Another approach for hiding the datasheet columns is by using the following code:

DoCmd.RunCommand acCmdUnhideColumns

The above command will open the dialog window shown in Figure 2.

To hide a column, uncheck the checkbox; and to unhide a column, check the checkbox. The problem with the above solution is that it requires user interaction. If your application needs a functionality to allow the user to select which columns to hide, then this approach will be appropriate for you. But if you don't want the user to have to choose which columns to hide, then you need a solution like the ColumnWidth or ColumnHidden approach above but with the ability to immediately reflect the changes. Luckily, that is still possible using VBA code.

The trick is to approach the problem from a different angle. We know that simply referring to the Split Form using Me.ColumnWidth or Me.ColumnHidden does not always work, but the datasheet object on the Split Form is also accessible through the Screen object. Therefore, to hide a column in a Split Form, all we need to do is execute the following code:

Screen.ActiveDatasheet.ColumnName.ColumnHidden = True

And voila! The above code should allow you to hide any column in the Datasheet View of the Split Form.

Caveat: Please remember that the topic of this article is focused on hiding a column in the datasheet portion of the Split Form. Thus, none of the above code affects the Textbox control on the Single View Form. If you also want to hide the corresponding control on the Single View Form when you hide its matching column in the Datasheet View Form, then you can still use its Visible property. However, hiding controls on the Single View Form will not automatically rearrange the other controls to take its place like it does in the Datasheet Form. If you want the other controls to take the place of the hidden control, that's another story.

Cheers!

Original post date: September 19, 2015