Setting Yes/No Field format in VBA Code

[Guest]

Hi All

I am creating a Yes/No field using VBA/SQL :-

db.Execute "ALTER TABLE [MyTableName] ADD [MyFieldName] YESNO"

which works fine. However, although the field functions correctly as a
Boolean field,
in the table it displays as a Text field (showing 0 or -1) instead of the
normal Check Box.
If I look at the properties for the field (on the Lookup tab) the Display
Control field shows Text Box.
If I change this to Check Box the field then displays as a Check Box.

Is there any way to set this property using VBA code (Yes I know the end
user would never see the table data
but it is more 'user friendly' to see a Check Box when I am testing). I have
tried changing the Format property
to Yes/No but this has no effect on how the field is displayed.

 

[Allen Browne]

The only way to do this is to use DAO code to CreateProperty() on the field.
The property is named "DisplayControl", and is of type dbInteger, and has
the value acCheckBox (though that contstant is a long.)

If the property already exists, you have to set it. If it does not exist,
you have to create it. The function below will create-and-set or just-set
the propety as needed. Call it like this:

Dim fld As DAO.Field
Set fld = dbEngine(0)(0).TableDefs("MyTableName").Fields("MyFieldName")
Call SetPropertyDAO(fld, "DisplayControl", dbInteger, CInt(acCheckBox))


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

 

[Guest]

Hi Allen

Code works perfectly, thanks very much.

--
Peter Hibbs

The only way to do this is to use DAO code to CreateProperty() on the field.
The property is named "DisplayControl", and is of type dbInteger, and has
the value acCheckBox (though that contstant is a long.)

If the property already exists, you have to set it. If it does not exist,
you have to create it. The function below will create-and-set or just-set
the propety as needed. Call it like this:

Dim fld As DAO.Field
Set fld = dbEngine(0)(0).TableDefs("MyTableName").Fields("MyFieldName")
Call SetPropertyDAO(fld, "DisplayControl", dbInteger, CInt(acCheckBox))


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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi All

I am creating a Yes/No field using VBA/SQL :-

db.Execute "ALTER TABLE [MyTableName] ADD [MyFieldName] YESNO"

which works fine. However, although the field functions correctly as a
Boolean field,
in the table it displays as a Text field (showing 0 or -1) instead of the
normal Check Box.
If I look at the properties for the field (on the Lookup tab) the Display
Control field shows Text Box.
If I change this to Check Box the field then displays as a Check Box.

Is there any way to set this property using VBA code (Yes I know the end
user would never see the table data
but it is more 'user friendly' to see a Check Box when I am testing). I
have
tried changing the Format property
to Yes/No but this has no effect on how the field is displayed.