Paste the functions below into a standard module. They demonstrate how to
insert a field into an existing table, and set properties of the fields.
If you understand this code, it will let you modify your table as you wish.
Sub ModifyTableDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("Table1")
'Add a field to the table.
tdf.Fields.Append tdf.CreateField("TestField", dbText, 80)
Debug.Print "Field added."
'Delete a field from the table.
tdf.Fields.Delete "TestField"
Debug.Print "Field deleted."
'Clean up
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub
Sub StandardProperties(strTableName As String)
'Purpose: Properties you always want set by default:
' TableDef: Subdatasheets off.
' Numeric fields: Remove Default Value.
' Currency fields: Format as currency.
' Yes/No fields: Display as check box. Default to No.
' Text/memo/hyperlink: AllowZeroLength off,
' UnicodeCompression on.
' All fields: Add a caption if mixed case.
'Argument: Name of the table.
'Note: Requires: SetPropertyDAO()
Dim db As DAO.Database 'Current database.
Dim tdf As DAO.TableDef 'Table nominated in argument.
Dim fld As DAO.Field 'Each field.
Dim strCaption As String 'Field caption.
Dim strErrMsg As String 'Responses and error messages.
'Initalize.
Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
'Set the table's SubdatasheetName.
Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
strErrMsg)
For Each fld In tdf.Fields
'Handle the defaults for the different field types.
Select Case fld.Type
Case dbText, dbMemo 'Includes hyperlinks.
fld.AllowZeroLength = False
Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean, _
True, strErrMsg)
Case dbCurrency
Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
strErrMsg)
Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
fld.DefaultValue = vbNullString
Case dbBoolean
fld.DefaultValue = 0
Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
CInt(acCheckBox))
End Select
'Set a caption if needed.
strCaption = ConvertMixedCase(fld.Name)
If strCaption <> fld.Name Then
Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
End If
'Set the field's Description.
Call SetFieldDescription(tdf, fld, , strErrMsg)
Next
'Clean up.
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
If Len(strErrMsg) > 0 Then
Debug.Print strErrMsg
Else
Debug.Print "Properties set for table " & strTableName
End If
End Sub
Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As
Integer, varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.
If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True
ExitHandler:
Exit Function
ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Staceytech said:
Help please...
I'm trying to set Field properties in Access 97 using Visual Basic...
Problem - I need to create 2 fields in tableX with the following
properties: (Note, tableX already exist).
Field #1. "Cost_AMT": Data Type = Number; Field Size = Double; Format
= Standard; Decimal Places = 2; Default Value = 2
Field #2. "Sold_YN": Data Type = Boolean; Format = Yes/No; Display
Control = CheckBox, Default Value = Yes; Caption = "Sold ?"
