[AccessD] Change all Yes/No fields in a database

Arthur, 

This works in A97, DAO
Since I have made the first field "0;Yes", then "Yes"
becomes the default value for the field.

Public Function fnYesNo2CBO()
    Dim db As DAO.Database
    Set db = CurrentDb
    SetAccessProperty db.TableDefs("tblTest").Fields("testCBO"), _
    "DisplayControl", 3, 111, 111
    SetAccessProperty db.TableDefs("tblTest").Fields("testCBO"), _
    "RowSourceType", dbText, "Value List", "Value List"
    SetAccessProperty db.TableDefs("tblTest").Fields("testCBO"), _
    "RowSource", dbText, "0;Yes;1;No", "0;Yes;1;No"
    SetAccessProperty db.TableDefs("tblTest").Fields("testCBO"), _
    "ColumnCount", 3, 2, 2
    SetAccessProperty db.TableDefs("tblTest").Fields("testCBO"), _
    "ColumnWidths", dbText, "0, 720", "0, 720"
    Set db = Nothing
End Function

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

    On Error GoTo ErrorSetAccessProperty
    ' Explicitly refer to Properties collection.
    obj.Properties(strName) = varSetting
    obj.Properties.Refresh
    If SetToWhat <> "" Then
        obj.Properties(strName).Value = SetToWhat
        obj.Properties.Refresh
    End If
    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(strName).Value = SetToWhat
        obj.Properties.Refresh
        SetAccessProperty = True
        Resume ExitSetAccessProperty
    Else
        MsgBox Err & ": " & vbCrLf & Err.Description
        SetAccessProperty = False
        Resume ExitSetAccessProperty
    End If
End Function


Michael R. Mattys
Try MattysMapLib for MapPoint at
www.mattysconsulting.com



----- Original Message ----- 
From: "Arthur Fuller" <artful at rogers.com>
To: "AccessD" <AccessD at databaseadvisors.com>
Sent: Sunday, November 23, 2003 7:38 PM
Subject: [AccessD] Change all Yes/No fields in a database


> I've been working on this database lately, and I made all the Yes/No
> fields CheckBoxes. Turns out the client hates CheckBoxes and wants
> Yes/No combos instead. So I need a routine to walk every table and
> change the properties of every Yes/No field from CheckBox to Combo +
> Value List, "-1","Yes", "0","No", column count = 2, column widths = 0".
> 
> Anyone got such a routine or something close that I can rework to suit
> my needs?
> 
> TIA,
> Arthur
> 
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com