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

To "walk" all, you need something like:

Public Sub allyesno()
Dim d As DAO.Database
Dim t As DAO.TableDef
Dim f As DAO.Field

Set d = CurrentDb
For Each t In d.TableDefs
    
    For Each f In t.Fields
        If f.Type = dbBoolean Then
            ' DO STUFF
            Debug.Print f.Name
        End If
    Next 'f
Next 't
End Sub

- hth,
Steve




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Friday, November 28, 2003 1:53 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Change all Yes/No fields in a database


Thanks for this. It's a start, but what I don't see is how I can apply
so that I can walk all the tables and all the fields, finding the Yes/No
fields without knowing their names beforehand, and then applying the
changes.

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael R
Mattys
Sent: Sunday, November 23, 2003 3:55 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com