Adding Fields in Table with VBA

[Clint W]

I have the code to add new fields to a table, but my
problem starts with I try to add a Boolean field. The
field gets created but the Format is blank. My question
is how do I create the field with VBA so the format is
Yes/No??

Clint

 

[Roger Carlson]

With the two procedures below, you can modify the format property of your
boolean field:

Function SetAccessProperty(obj As Object, strName As String, _
intType As Integer, varSetting As Variant) As Boolean
Dim prp As Property
Const conPropNotFound As Integer = 3270

On Error GoTo ErrorSetAccessProperty
' Explicitly refer to Properties collection.
obj.Properties(strName) = varSetting
obj.Properties.Refresh
SetAccessProperty = True

ExitSetAccessProperty:
Exit Function

ErrorSetAccessProperty:
If Err = conPropNotFound Then

' Create property, denote type, and set initial value.
Set prp = obj.CreateProperty(strName, intType, varSetting)
' Append Property object to Properties collection.
obj.Properties.Append prp
obj.Properties.Refresh
SetAccessProperty = True
Resume ExitSetAccessProperty
Else
MsgBox Err & ": " & vbCrLf & Err.Description
SetAccessProperty = False
Resume ExitSetAccessProperty
End If
End Function

'You could call the preceding function with a procedure such as the
following:
(Note: in this example "FieldFormatTable" is the actual table name and
"BooleanField" is the actual field name.)

Sub CallPropertySet()
Dim dbs As Database, tdf As TableDef, fld As Field
Dim blnReturn As Boolean

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to Employees table.
Set tdf = dbs.TableDefs!FieldFormatTable
Set fld = tdf.Fields!BooleanField
' Call SetAccessProperty function.
blnReturn = SetAccessProperty(fld, _
"Format", dbText, "True/False")
' Evaluate return value.
If blnReturn = True Then
Debug.Print "Property set successfully."
Else
Debug.Print "Property not set successfully."

End If
End Sub

Put these in a general module and execute the second procedure after
modifying the table and field name in the procedure to match yours.