change DisplayControl property with DAO

[Rev]

I often use DDL in my code to creaet new tables, usually to hold all of the
table and field names or the SQL property from all of the queries, or all of
the Lable and Control Source properties from forms and reports.
This works well for me, however one little glitch that I can't figure out.
When I create a table that includes Yes/No fields, the fields do not display
as CheckBoxes as they do when I create Yes/No fields within Design View. Does
anyone know how I can create this property in code? I have code that should
create this property if it does not exist, and in the debug window when I
check to see if the property was created, it has. a check using Microsoft's
ADE Property Wizard also shows that the property exists after I run my code.
But when I open the table or use the field in a query, form, or report, it
displays as a textbox with values of -1 and 0. I don't want to have to go in
and manually change each one... I could create a lot of cool forms and
reports automatically except for this checkbox thing... ???

 

[Allen Browne]

If you are successfullly creating the property but Access is not using it,
you may have created the wrong data type.

The DisplayControl is a dbInteger type, not a dbLong, so you create it like
this:
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

 

[Rev]

Thanks Allen!
You pegged it. I didn't know what data type the property uses and had tried
several different ways. the code I have been using guesses based on the value
passed to the function. I had passed it "106", "TextBox" and 106. The code
you provided both forces the integer datatype and uses the acCheckBox
constant. Works perfect! Now I can move on to bigger and better things. This
problem has baked my noodle and has been bugging me for the past year! (pun
intended

Thanks again!
--
Everything in EXCESS!!! If you want to taste the flavor of LIFE, Take BIG
Bytes. Moderation is for monks…

If you are successfullly creating the property but Access is not using it,
you may have created the wrong data type.

The DisplayControl is a dbInteger type, not a dbLong, so you create it like
this:
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