Set Caption for fields

[SAC]

I would like to loop through the fields in a table and set the caption to
"".

Could you please help me with the code for this?

Thanks.

 

[Dirk Goldgar]

I would like to loop through the fields in a table and set the
caption to "".

Could you please help me with the code for this?

By setting the caption to "", I take it you want to remove the caption.
This means that the field name itself will be used as the caption if you
open a datasheet on the table. This routine will remove the Caption
property from each field in the specified table:

'----- start of code -----
Sub ClearFieldCaptions(TableName As String)

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(TableName)

For Each fld In tdf.Fields
fld.Properties.Delete "Caption"
Next fld

Exit_Point:
On Error Resume Next
Set tdf = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
If Err.Number = 3265 Then
' The property doesn't exist. That's fine with us.
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub

'----- end of code -----

You would call it as in this example:

ClearFieldCaptions "Table1"

 

[SAC]

Excellent. Thanks!

By setting the caption to "", I take it you want to remove the caption.
This means that the field name itself will be used as the caption if you
open a datasheet on the table. This routine will remove the Caption
property from each field in the specified table:

'----- start of code -----
Sub ClearFieldCaptions(TableName As String)

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs(TableName)

For Each fld In tdf.Fields
fld.Properties.Delete "Caption"
Next fld

Exit_Point:
On Error Resume Next
Set tdf = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
If Err.Number = 3265 Then
' The property doesn't exist. That's fine with us.
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub

'----- end of code -----

You would call it as in this example:

ClearFieldCaptions "Table1"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)