Re: Setting field properties in code

If the Field in the TableDef does not have the property, you need to
CreateProperty(). The example below shows how to create the property if it
does not exist, and set it.

To make a yes/no field display as s check box, create and set the
DisplayControl property.

To create an index on a field, CreateIndex on the table.

Note that if you are working on an attached table, you will need to
OpenDatabase and work directly on the back end.

The example below illustrates how to set what you might consider standard
properties:
- setting the table's SubDatasheetName to [None].
- setting AllowZeroLength to No for all text fields, memos, and hyperlinks.
- removing that darn zero as Default Value for numeric fields.
- setting the Format property for Currency types (and illustrating how to
set the Default Value if you wish.)
- setting Yes/No fields to display as a check box.
- setting a Caption with spaces on fields that have a mixed-case name (e.g.
OrderDate.)
- setting a Description of each field

Finally, the last example shows how to create a primary key index, a
single-field index, and a multi-field index.

Paste the code into a module, and see how it works.
----------------------code starts-----------------
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
fld.DefaultValue = 0
Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
strErrMsg)
Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
fld.DefaultValue = vbNullString
Case dbBoolean
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
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

Sub CreateIndexesDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index

'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("tblDaoContractor")

'1. Primary key index.
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ContractorID")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind

'2. Single-field index.
Set ind = tdf.CreateIndex("Inactive")
ind.Fields.Append ind.CreateField("Inactive")
tdf.Indexes.Append ind

'3. Multi-field index.
Set ind = tdf.CreateIndex("FullName")
With ind
.Fields.Append .CreateField("Surname")
.Fields.Append .CreateField("FirstName")
End With
tdf.Indexes.Append ind

'Refresh the display of this collection.
tdf.Indexes.Refresh

'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
Debug.Print "tblDaoContractor indexes created."
End Sub
----------------------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@xxxxxxxxxxx> wrote in message
news:431e4763$1@xxxxxxxxxxxxxxxxxxxxxxxxx
> Can I set the Format property in a date/time field in code?
>
> Can I set the Input Mask in a date/time field in code?
>
> Can I set the Format of a Yes/No field to Checkbox in code?
>
> I am working on a remote update of tables and fields and can't find enough
> information on these things.
>
> Also, how do you index a field in code?
>
> TIA
> dixie


.

Relevant Pages

  • Re: Setting field properties in code
    ... Now just say I want to create a format property of d/m/yyyy for a date/time ... > Sub StandardProperties(strTableName As String) ... > Dim tdf As DAO.TableDef 'Table nominated in argument. ... > Dim ind As DAO.Index ...
    (comp.databases.ms-access)
  • 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)
  • Split database oops
    ... Dim i As Integer, strDBPath As String, strTbl As String ... Dim dbCurr As Database, dbLink As Database ... Set tdf = dbRemote.TableDefs ...
    (microsoft.public.access.formscoding)
  • Re: create index for
    ... CreateTableDAO creates a pair of tables, ... Dim tdf As DAO.TableDef ... Set tdf = db.CreateTableDef ... Dim ind As DAO.Index ...
    (microsoft.public.access.modulesdaovba)
  • Re: Date/Time format
    ... Sub StandardProperties(strTableName As String) ... Dim tdf As DAO.TableDef 'Table nominated in argument. ... Dim strErrMsg As String 'Responses and error messages. ... Set tdf = db.TableDefs ...
    (microsoft.public.access.formscoding)