Table Fields.
You can use the ColumnHidden property to show or hide a specified column in Datasheet view. For example, you might want to hide a CustomerAddress field thats too wide so you can view the CustomerName and PhoneNumber fields.
Note The ColumnHidden property applies to all fields in Datasheet view and to form controls when the form is in Datasheet view.
The ColumnHidden property uses the following settings.
Setting | Description | Visual Basic |
Hide | The column is hidden. | True (-1) |
Unhide | (Default) The column is visible. | False (0) |
You can set this property by clicking Hide Columns or Unhide Columns on the Format menu in Datasheet view.
You can also set this property in Visual Basic using a Long value.
To set or change this property for a table or query using Visual Basic, you must use a fields Properties collection.
Note The ColumnHidden property is not available in form Design view.
Hiding a column in Datasheet view does not hide fields from the same column in Form view. Similarly, setting a controls Visible property to False in Form view doesnt hide the corresponding column in Datasheet view.
You can display a field in a query even though the column for the field is hidden in table Datasheet view.
You can use values from a hidden column as the criteria for a filter even though the column remains hidden after the filter is applied.
You cant use the Copy, Paste, Find, and Replace commands on the Edit menu to affect hidden columns.
Setting a fields ColumnWidth property to 0, or resizing the field to a zero width in Datasheet view, causes Microsoft Access to set the corresponding ColumnHidden property to True. Unhiding a column restores the ColumnWidth property to the value it had before the field was hidden.
ColumnOrder Property, ColumnWidth Property, RowHeight Property.
The following example hides the ProductID field in Datasheet view of the Products form.
Forms![Products]![ProductID].ColumnHidden = -1
The next example also hides the ProductID field in Datasheet view of the Products table. To set the ColumnHidden property, the example uses the ShowColumn and SetFieldProperty procedures, which are in the databases standard module.
Dim db As Databasedb = CurrentDbdb.TableDefs![Products].Fields![ProductID], False ShowColumn (FieldObj As Field, Show As Integer) ' Set ColumnHidden property. SetFieldProperty FieldObj, "ColumnHidden", dbLong, Not ShowSub SetFieldProperty (fldField As Field, strPropertyName As String, _ intPropertyType As Integer, varPropertyValue As Variant) ' Set field property without producing nonrecoverable run-time ' error. Const conErrPropertyNotFound = 3270 Dim proProperty As Property On Error Resume Next ' Don't trap errors. fldField.Properties(strPropertyName) = varPropertyValue If Err <> 0 Then ' Error occurred when value set. If Err <> conErrPropertyNotFound Then On Error GoTo 0 MsgBox "Couldn't set property '" & strPropertyName _ & "' on field '" & fldField.name & "'", 48, _ "SetFieldProperty" Else On Error GoTo 0 Set proProperty = fldField.CreateProperty(strPropertyName, _ intPropertyType, varPropertyValue) fldField.Properties.Append proProperty End If End IfSub