Insert new field "yesNo"

[Guest]

Hi,
I need to add a new field type "yesNo", but which Display Control be "check Box"

I used the following code to add:
DoCmd.RunSQL "alter table tblAssociado add name yesNo;"

But what happen is the Display Control appear like "Text Box" and not like I need "Check Box"!How can I add the new field with this type!

I hope you understand my question!
TKS
Miguel Neto

 

[Allen Browne]

The only way to set the DisplayControl to check box is with code that uses
DAO.

Open the TableDef, and choose the yes/no Field.
Call CreateProperty() to create a property named "DisplayControl" of type
dbInteger, with the value CInt(acCheckBox).

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

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

I need to add a new field type "yesNo", but which Display Control be "check Box"

I used the following code to add:
DoCmd.RunSQL "alter table tblAssociado add name yesNo;"

But what happen is the Display Control appear like "Text Box" and not like

I need "Check Box"!How can I add the new field with this type!

 

[Guest]

Miguel

You want to add your field at the table level and in your form or report design view.

Go to the design view of your table table, set the following criteria for your field:
- Data Type = Yes/No
-on the General tab, Format = Yes/No
- on the Lookup tab, Display Control = Check Box

In the form and/or report design view, set up the following:
- from the toolbar, select the checkbox control and place it in the location you want on your form/report
- go to the checkbox control's Properties. On the Data tab for the Control Source select the name of your Yes/No field from the dropdown menu. - this assumes that you've already set your form property's control source to the table that your field exists in and selected all the fields that you want displayed in that form.

 

[Allen Browne]

From a code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library.

More info:
http://allenbrowne.com/ser-38.html

 

[Guest]

Thanks
Well, gave me another error!

Line: "Set p = f2.CreateProperty("DisplayControl", dbInteger, 106)"

ERROR:
"Compile error:
Method or data member not found"

Can you help me on more time?
Tks
MN

 

[Allen Browne]

Below is part of the code we use to programmatically set the desired
properties for a table and its fields. It includes setting the
DisplayControl for Yes/No fields.

---------------code begins------------------------
Sub StandardProperties(strTableName As String)
'Purpose: Properties you always want set by default:
' TableDef: Subdatasheets off.
' Numeric fields: Remove Default Value.
' Currency fields: Format as currency.
' Yes/No fields: Display as check box. Default to No.
' Text/memo/hyperlink: AllowZeroLength off,
' UnicodeCompression on.
'Argument: Name of the table.
'Note: Requires: SetPropertyDAO()
Dim db As DAO.Database 'Current database.
Dim tdf As DAO.TableDef 'Table nominated in argument.
Dim fld As DAO.Field 'Each field.
Dim strCaption As String 'Field caption.
Dim strErrMsg As String 'Responses and error messages.

'Initalize.
Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)

'Set the table's SubdatasheetName.
Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
strErrMsg)

For Each fld In tdf.Fields
'Handle the defaults for the different field types.
Select Case fld.Type
Case dbText, dbMemo 'Includes hyperlinks.
fld.AllowZeroLength = False
Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean, _
True, strErrMsg)
Case dbCurrency
Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
strErrMsg)
Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
fld.DefaultValue = vbNullString
Case dbBoolean
fld.DefaultValue = 0
Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
CInt(acCheckBox))
End Select
Next

'Clean up.
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
If Len(strErrMsg) > 0 Then
Debug.Print strErrMsg
Else
Debug.Print "Properties set for table " & strTableName
End If
End Sub

Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
'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
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
----------------code ends-------------------------