Programatically alter fields properties (2000/XP)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

Thanks Hans, I’ll try that out first thing tomorrow – for now its time for me to go home

Reply | Quote

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

Reply | Quote

Thanks both of you.

Hans – That worked perfectly I had no idea you could do that – although with any luck I wont need to use it again!

Reply | Quote

Thanks both of you.

Hans – That worked perfectly I had no idea you could do that – although with any luck I wont need to use it again!

Reply | Quote

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

Reply | Quote

Thanks Hans, I’ll try that out first thing tomorrow – for now its time for me to go home

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote