Re: Setting field properties in code

The idea is:
SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty").Fields("StartDate"),_
"Format", dbText, "dd/mm/yyyy")

Or you might use "General Date" in place of "dd/mm/yyyy", which will use the
format that the user has defined in the Windows Control Panel, under
Regional Options.

BTW, if you are having difficulties with the Australian date format in
Access, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
It deals with the 3 cases that often trip people up.

--
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:431e5f64@xxxxxxxxxxxxxxxxxxxxxxxxx
> OK, I've copied the code into a module and had a play with it.
>
> Now just say I want to create a format property of d/m/yyyy for a
> date/time field called StartDate, that is in a table called Faculty, what
> is the syntax I need for the event I am going to do this from. I know
> that is what I have to do, but can't work out the arguments and how they
> come together.
>
> dixie
>
> "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
> news:431e4f7d$0$14481$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> 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-----------------
>>
>> "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


.