Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Programatically alter fields properties (2000/XP)

    I need to change some table field properties in code and managed to change the Required and Validation Rule properties using the code below.

    I also need to set the Format and Input Mask Properties for some date fields but can't figure out how to as these properties are not supported by the technique below.

    Sub DAOChangeSomeProperties()
    Dim tdf As DAO.TableDef
    Dim tfld As DAO.Field

    For Each tdf In CurrentDb.TableDefs

    If tdf.Name = "tblSurveyData" Then

    For Each tfld In tdf.Fields

    tfld.Required = False
    tfld.AllowZeroLength = False
    tfld.ValidationRule = ""

    Next

    End If

    Next

    End Sub

    The reason I need to do this is there is a bug causing the error "The Property Value Is Too Large" when I try to change to properties manually in table design view. I know this is due to the vast number of fields (168!) in the table but unfortunately I cannot change this at present as the database was inherited and most of the fields seem to form part of a complicated calculation.

    I've just read another post where someone had the same error and Charlotte suggested the record limit could come into play with this number of text fields - any suggestions on how I could check this too would gratefully received as I've no doubt that will propably be the next problem!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Programatically alter fields properties (2000/XP)

    You must use the DAO Properties collection of the Field object. A property such as Format does not exist a priori, if it has never been set before, you must first create it, and append it to the Properties collection. You can use the following function for this purpose:

    Sub SetFieldProperty(fld As DAO.Field, strName As String, intType As DAO.DataTypeEnum, varValue As Variant)
    On Error GoTo ErrHandler

    fld.Properties(strName) = varValue
    Exit Sub

    ErrHandler:
    If Err = 3270 Then
    ' Property does not exist
    fld.Properties.Append fld.CreateProperty(strName, intType, varValue)
    Resume Next
    Else
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    Apply it like this in your code (in the For Each tfld In tdf.Fields loop):

    SetFieldProperty tfld, "Format", dbText, ">"
    SetFieldProperty tfld, "InputMask", dbText, ">CCCC;0;_"

    A record has a size limit of 2000 bytes. Each text field contributes the actual number of characters plus 1. If you have a text field that is defined as 20 character long in design view, it does not necessarily take up 21 bytes. What counts is the length of the contents of the field; this will vary from record to record. So even if you have 168 fields each defined as length 100, the actual records may still fit in 2000 bytes.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programatically alter fields properties (2000/XP)

    Thanks Hans, I'll try that out first thing tomorrow - for now its time for me to go home <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15>

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programatically alter fields properties (2000/XP)

    Darsha

    I had same problem. In addition to info provided by Hans, I was able to add/change fields by eliminating description verbage in design view. Don't know if this goes into the size calculation.

    HTH John

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programatically alter fields properties (2000/XP)

    Thanks both of you.

    Hans - That worked perfectly <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> I had no idea you could do that - although with any luck I wont need to use it again!

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programatically alter fields properties (2000/XP)

    I used this successfully to set some properties - is it possible to also clear them?

    If I use either

    SetFieldProperty tfld, "Description", dbText, Empty

    or

    SetFieldProperty tfld, "Description", dbText, Null

    or

    SetFieldProperty tfld, "Description", dbText, ""

    I get the error ' User-defined properties do not support a Null Value."

    Is there any way to get round this.

    Regards

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Programatically alter fields properties (2000/XP)

    You might try putting a single space in the Description property, i.e.
    SetFieldProperty tfld, "Description", dbText, " "

    Access will typically truncate the trailing spaces, but in this case it may not, so give it a try.
    Wendell

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Programatically alter fields properties (2000/XP)

    As you found out, you can't assign a zero-length string or Null value to a user-defined Field property like Description. As Wendell suggested, you can get around this by assigning a single space as property value. Another option, if you want the Field Description to be totally blank, is to delete the property. User-defined properties can be deleted from the Field's Properties Collection, while built-in properties cannot. This is an example of a generic procedure that sets a user-defined Field property:

    Public Sub SetFieldProperty(ByRef TblName As String, _
    ByRef FldName As String, _
    ByRef PropName As String, _
    ByVal PropType As DAO.DataTypeEnum, _
    ByRef PropVal As Variant)
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    Dim strMsg As String

    ' Set user-defined Field Properties:
    Set db = CurrentDb
    Set tbl = db.TableDefs(TblName)
    Set fld = tbl.Fields(FldName)

    fld.Properties(PropName) = PropVal

    Exit_Sub:
    Set db = Nothing
    Set tbl = Nothing
    Set fld = Nothing
    Exit Sub
    Err_Handler:
    Select Case Err.Number
    Case 3270 ' Property not found
    fld.Properties.Append fld.CreateProperty(PropName, PropType, PropVal)
    Resume
    Case 3385 'User-defined properties do not support a Null value
    fld.Properties.Delete PropName
    Resume Exit_Sub
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "SET FIELD DESCRIPTION ERROR"
    Debug.Print strMsg
    Resume Exit_Sub
    End Select
    End Sub

    To set Description property use statement like this:

    SetFieldProperty "Table1", "Field1", "Description", dbText, "Test New Field Description"

    To clear Description:

    SetFieldProperty "Table1", "Field1", "Description", dbText, ""
    - or -
    SetFieldProperty "Table1", "Field1", "Description", dbText, Null

    These statements successfully set/cleared Field description in specified table. The same procedure can be used in similar fashion to set other user-defined properties for a specified field.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •