Creating new Fields in code

**Allen Browne** · Nov 13 '05, 01:31 PM

Re: Creating new Fields in code

DDL query statements are not powerful enough to to this.

Use DAO if you want to set the field properties.
See help on CreateField(), and CreateProperty( ).

The code below illustrates how to set properties that you might consider
standard, e.g.:
- For text, memo, and hyperlink fields, set AllowZeroLength to No and
UniCode Compression to Yes.
- Remove that annoying zero from the Default Value of number fields.
- Set the Currency format for Currency fields (needed on older versions of
Access only).
- Set yes/no fields to a check box as the Display Control.
- Turn off subdatasheets, to solve the performance problems they create.

-----------------------code starts----------------------
Sub StandardPropert ies(strTableNam e 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,
' UnicodeCompress ion 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(st rTableName)

'Set the table's SubdatasheetNam e.
Call SetPropertyDAO( tdf, "SubdatasheetNa me", 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.AllowZeroLe ngth = False
Call SetPropertyDAO( fld, "UnicodeCompres sion", dbBoolean, _
True, strErrMsg)
Case dbCurrency
fld.DefaultValu e = 0
Call SetPropertyDAO( fld, "Format", dbText, "Currency", _
strErrMsg)
Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
fld.DefaultValu e = vbNullString
Case dbBoolean
Call SetPropertyDAO( fld, "DisplayControl ", dbInteger, _
CInt(acCheckBox ))
End Select

'Set a caption if needed.
strCaption = ConvertMixedCas e(fld.Name)
If strCaption <> fld.Name Then
Call SetPropertyDAO( fld, "Caption", dbText, strCaption)
End If

'Set the field's Description.
Call SetFieldDescrip tion(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.CreatePrope rty(strProperty Name, 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
-----------------------code ends----------------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dixie" <dixie@dogmail. com> wrote in message
news:42ec0d03$1 @duster.adelaid e.on.net...[color=blue]
>I wish to add some fields to an existing table in code.
> I am using the following code from rkc.
> CurrentDb.Execu te ("ALTER TABLE MyTable ADD MyNewField Text 25")
> This works , but I need to also set the Required, Allow Zero Length and
> Indexed attributes. I have tried but keep getting a syntax error.
> Also, can I set the default value of a field in code?
> Has anyone some examples of these.
>
> TIA
>
> dixie[/color]

Creating new Fields in code

Creating new Fields in code

Comment