Set the Format Property of Table in VBA

[Guest]

I've been able to create a Table within a module, and access some of the
Properties... but can't find anywhere any info on how to change the Format
Property specifically of a Boolean Field to display Yes/No rather than 0/-1
such as code would look like...

Dim dbs As Database, tdf As TableDef

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("Aircraft")

tdf.Fields.Append tdf.CreateField("Current_Review_Date", dbText, 50)
tdf.Fields("Current_Review_Date").AllowZeroLength = True
'Rest of Fields....

dbs.TableDefs.Append tdf

In an ideal world there would be...

tdf.Fields("Some Boolean Field").Format = "Yes/No"

 

[Allen Browne]

The Format property does not exist if it has not been used, so you have to
CreateProperty().

We use the function below to set the property. It creates it if it does not
exist.

Example usage:
Call SetPropertyDAO(CurrentDb.TableDefs("MyTable").Fields("MyField"),
"Format", dbText, "Yes/No")


Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As
Integer, varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function