Re: Adding Fields in Table with VBA

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Roger Carlson (NO-Rog3erc-SPAM_at_hotmail.com)
Date: 05/19/04 

Date: Wed, 19 May 2004 13:44:26 -0400

With the two procedures below, you can modify the format property of your
boolean field:

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

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

'You could call the preceding function with a procedure such as the
following:
(Note: in this example "FieldFormatTable" is the actual table name and
"BooleanField" is the actual field name.)

Sub CallPropertySet()
    Dim dbs As Database, tdf As TableDef, fld As Field
    Dim blnReturn As Boolean

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Employees table.
    Set tdf = dbs.TableDefs!FieldFormatTable
    Set fld = tdf.Fields!BooleanField
    ' Call SetAccessProperty function.
    blnReturn = SetAccessProperty(fld, _
        "Format", dbText, "True/False")
    ' Evaluate return value.
    If blnReturn = True Then
        Debug.Print "Property set successfully."
    Else
        Debug.Print "Property not set successfully."

End If
End Sub

Put these in a general module and execute the second procedure after
modifying the table and field name in the procedure to match yours. 

-- 
--Roger Carlson
  www.rogersaccesslibrary.com
  Reply to: Roger dot Carlson at Spectrum-Health dot Org
"Clint W" <anonymous@discussions.microsoft.com> wrote in message
news:f40b01c43dc5$30979a70$a101280a@phx.gbl...
> I have the code to add new fields to a table, but my
> problem starts with I try to add a Boolean field.  The
> field gets created but the Format is blank.  My question
> is how do I create the field with VBA so the format is
> Yes/No??
>
> Clint

Relevant Pages

  • RE: adding colored cells only
    ... here is some code you could modify: ... Dim Total As Integer ... For Each cell In R ... A1:F13 which had the same color format. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Setting field properties in code
    ... format that the user has defined in the Windows Control Panel, ... >> Sub StandardProperties(strTableName As String) ... >> Dim tdf As DAO.TableDef 'Table nominated in argument. ... >> Dim ind As DAO.Index ...
    (comp.databases.ms-access)
  • Re: Conditional format problem
    ... an Excel 5.0/95 format file, ... Anyone know how to save in a later Excel format from Access? ... Dim oXL As Object ... Dim strCondition1 As String ...
    (microsoft.public.excel.programming)
  • Re: Mail Merge HTML Format Word 2002 XP SP3
    ... HTML format as you have no control over how the recipient reads them. ... 'Creates a new e-mail item and modifies its properties. ... Dim olApp As Outlook.Application ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Generating a column based on import file name
    ... is d/m/yy as opposed to the format shown. ... Dim lReturn As Long ... .TextFilePlatform = xlWindows ... importing. ...
    (microsoft.public.excel.programming)