Setting field properties in code

[Dixie]

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

[Allen Browne]

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 SubDatasheetNam e 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 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
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

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

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

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

'2. Single-field index.
Set ind = tdf.CreateIndex ("Inactive")
ind.Fields.Appe nd ind.CreateField ("Inactive")
tdf.Indexes.App end ind

'3. Multi-field index.
Set ind = tdf.CreateIndex ("FullName")
With ind
.Fields.Append .CreateField("S urname")
.Fields.Append .CreateField("F irstName")
End With
tdf.Indexes.App end ind

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

'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
Debug.Print "tblDaoContract or 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@dogmail. com> wrote in message
news:431e4763$1 @duster.adelaid e.on.net...[color=blue]
> 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[/color]

[Dixie]

Re: Setting field properties in code

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@Se eSig.Invalid> wrote in message
news:431e4f7d$0 $14481$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
> 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 SubDatasheetNam e 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 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
> 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
>
> Sub CreateIndexesDA O()
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> Dim ind As DAO.Index
>
> 'Initialize
> Set db = CurrentDb()
> Set tdf = db.TableDefs("t blDaoContractor ")
>
> '1. Primary key index.
> Set ind = tdf.CreateIndex ("PrimaryKey ")
> With ind
> .Fields.Append .CreateField("C ontractorID")
> .Unique = False
> .Primary = True
> End With
> tdf.Indexes.App end ind
>
> '2. Single-field index.
> Set ind = tdf.CreateIndex ("Inactive")
> ind.Fields.Appe nd ind.CreateField ("Inactive")
> tdf.Indexes.App end ind
>
> '3. Multi-field index.
> Set ind = tdf.CreateIndex ("FullName")
> With ind
> .Fields.Append .CreateField("S urname")
> .Fields.Append .CreateField("F irstName")
> End With
> tdf.Indexes.App end ind
>
> 'Refresh the display of this collection.
> tdf.Indexes.Ref resh
>
> 'Clean up
> Set ind = Nothing
> Set tdf = Nothing
> Set db = Nothing
> Debug.Print "tblDaoContract or 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@dogmail. com> wrote in message
> news:431e4763$1 @duster.adelaid e.on.net...[color=green]
>> 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[/color]
>
>[/color]

[Albert D. Kallal]

Re: Setting field properties in code

You got some answer here already.

I should point out that modifying the format in a table does NOT change the
format in your existing application.

So, that format in the table design mode is just there for a default. And,
since a good developer will NEVER allow users to see, or edit, or use the
tables direct, then you really don't have to worry about this problem. Just
make sure that while you design and build new forms, that you set the format
for the control on the form.

So, I not sure if changing the format in a table is what you want, and
worse, after you change it, the controls on reports, and forms will NOT
change when you do this. (new controls you place on the forms, or reports
will use this new format as a default, but not existing).

So, as to how you update users software? Well, the solution is to split
your database, and then you can safely update the users code, forms etc, and
NOT have to worry about overwriting the data part. You can read about how
this works here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

http://www.members.shaw.ca/AlbertKallal

[Allen Browne]

Re: Setting field properties in code

The idea is:
SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),_
"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@dogmail. com> wrote in message
news:431e5f64@d uster.adelaide. on.net...[color=blue]
> 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@Se eSig.Invalid> wrote in message
> news:431e4f7d$0 $14481$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=green]
>> 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 SubDatasheetNam e 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 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
>> 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
>>
>> Sub CreateIndexesDA O()
>> Dim db As DAO.Database
>> Dim tdf As DAO.TableDef
>> Dim ind As DAO.Index
>>
>> 'Initialize
>> Set db = CurrentDb()
>> Set tdf = db.TableDefs("t blDaoContractor ")
>>
>> '1. Primary key index.
>> Set ind = tdf.CreateIndex ("PrimaryKey ")
>> With ind
>> .Fields.Append .CreateField("C ontractorID")
>> .Unique = False
>> .Primary = True
>> End With
>> tdf.Indexes.App end ind
>>
>> '2. Single-field index.
>> Set ind = tdf.CreateIndex ("Inactive")
>> ind.Fields.Appe nd ind.CreateField ("Inactive")
>> tdf.Indexes.App end ind
>>
>> '3. Multi-field index.
>> Set ind = tdf.CreateIndex ("FullName")
>> With ind
>> .Fields.Append .CreateField("S urname")
>> .Fields.Append .CreateField("F irstName")
>> End With
>> tdf.Indexes.App end ind
>>
>> 'Refresh the display of this collection.
>> tdf.Indexes.Ref resh
>>
>> 'Clean up
>> Set ind = Nothing
>> Set tdf = Nothing
>> Set db = Nothing
>> Debug.Print "tblDaoContract or indexes created."
>> End Sub
>> ----------------------code ends-----------------
>>
>> "Dixie" <dixie@dogmail. com> wrote in message
>> news:431e4763$1 @duster.adelaid e.on.net...[color=darkred]
>>> 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[/color][/color][/color]

[Dixie]

Re: Setting field properties in code

I am having the same problem with that that I was having before I gave up
trying to get it right. When I put that into a button module behind a form
(where I am testing it from), it comes up with an error as soon as I put it
there and fix up the _ for the broken line. Microsoft Visual Basic Compile
error: Expected: =
There is no point in looking at the help file for this error as it is very
generic.

I tried changing the dbText to dbDate and it the same thing happened.

Just to make sure I haven't misspelled anything here is the line removed out
of the form module.

SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),"For mat",
dbText, "dd/mm/yyyy")

Is there something obviously wrong?

dixie

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:431e6d53$0 $14487$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
> The idea is:
> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),_
> "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@dogmail. com> wrote in message
> news:431e5f64@d uster.adelaide. on.net...[color=green]
>> 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@Se eSig.Invalid> wrote in message
>> news:431e4f7d$0 $14481$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=darkred]
>>> 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 SubDatasheetNam e 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 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
>>> 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
>>>
>>> Sub CreateIndexesDA O()
>>> Dim db As DAO.Database
>>> Dim tdf As DAO.TableDef
>>> Dim ind As DAO.Index
>>>
>>> 'Initialize
>>> Set db = CurrentDb()
>>> Set tdf = db.TableDefs("t blDaoContractor ")
>>>
>>> '1. Primary key index.
>>> Set ind = tdf.CreateIndex ("PrimaryKey ")
>>> With ind
>>> .Fields.Append .CreateField("C ontractorID")
>>> .Unique = False
>>> .Primary = True
>>> End With
>>> tdf.Indexes.App end ind
>>>
>>> '2. Single-field index.
>>> Set ind = tdf.CreateIndex ("Inactive")
>>> ind.Fields.Appe nd ind.CreateField ("Inactive")
>>> tdf.Indexes.App end ind
>>>
>>> '3. Multi-field index.
>>> Set ind = tdf.CreateIndex ("FullName")
>>> With ind
>>> .Fields.Append .CreateField("S urname")
>>> .Fields.Append .CreateField("F irstName")
>>> End With
>>> tdf.Indexes.App end ind
>>>
>>> 'Refresh the display of this collection.
>>> tdf.Indexes.Ref resh
>>>
>>> 'Clean up
>>> Set ind = Nothing
>>> Set tdf = Nothing
>>> Set db = Nothing
>>> Debug.Print "tblDaoContract or indexes created."
>>> End Sub
>>> ----------------------code ends-----------------
>>>
>>> "Dixie" <dixie@dogmail. com> wrote in message
>>> news:431e4763$1 @duster.adelaid e.on.net...
>>>> 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[/color][/color]
>
>[/color]

[Allen Browne]

Re: Setting field properties in code

Try adding the word Call, i.e.:
Call SetPropertyDAO( ...

If it won't compile, and you are using Access 2000 or 2002, then choose
References on the Tools menu (from the code window), and check the box
beside:
Microsoft DAO 3.6 Library
More on references:

http://allenbrowne.com/ser-38.html

(BTW, the Format property is a Text type, regardless of the type of field.)

--
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:431e7074@d uster.adelaide. on.net...[color=blue]
>I am having the same problem with that that I was having before I gave up
>trying to get it right. When I put that into a button module behind a form
>(where I am testing it from), it comes up with an error as soon as I put it
>there and fix up the _ for the broken line. Microsoft Visual Basic Compile
>error: Expected: =
> There is no point in looking at the help file for this error as it is very
> generic.
>
> I tried changing the dbText to dbDate and it the same thing happened.
>
> Just to make sure I haven't misspelled anything here is the line removed
> out of the form module.
>
> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),"For mat",
> dbText, "dd/mm/yyyy")
>
> Is there something obviously wrong?
>
> dixie
>
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> news:431e6d53$0 $14487$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=green]
>> The idea is:
>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),_
>> "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@dogmail. com> wrote in message
>> news:431e5f64@d uster.adelaide. on.net...[color=darkred]
>>> 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@Se eSig.Invalid> wrote in message
>>> news:431e4f7d$0 $14481$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>> 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 SubDatasheetNam e 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 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
>>>> 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
>>>>
>>>> Sub CreateIndexesDA O()
>>>> Dim db As DAO.Database
>>>> Dim tdf As DAO.TableDef
>>>> Dim ind As DAO.Index
>>>>
>>>> 'Initialize
>>>> Set db = CurrentDb()
>>>> Set tdf = db.TableDefs("t blDaoContractor ")
>>>>
>>>> '1. Primary key index.
>>>> Set ind = tdf.CreateIndex ("PrimaryKey ")
>>>> With ind
>>>> .Fields.Append .CreateField("C ontractorID")
>>>> .Unique = False
>>>> .Primary = True
>>>> End With
>>>> tdf.Indexes.App end ind
>>>>
>>>> '2. Single-field index.
>>>> Set ind = tdf.CreateIndex ("Inactive")
>>>> ind.Fields.Appe nd ind.CreateField ("Inactive")
>>>> tdf.Indexes.App end ind
>>>>
>>>> '3. Multi-field index.
>>>> Set ind = tdf.CreateIndex ("FullName")
>>>> With ind
>>>> .Fields.Append .CreateField("S urname")
>>>> .Fields.Append .CreateField("F irstName")
>>>> End With
>>>> tdf.Indexes.App end ind
>>>>
>>>> 'Refresh the display of this collection.
>>>> tdf.Indexes.Ref resh
>>>>
>>>> 'Clean up
>>>> Set ind = Nothing
>>>> Set tdf = Nothing
>>>> Set db = Nothing
>>>> Debug.Print "tblDaoContract or indexes created."
>>>> End Sub
>>>> ----------------------code ends-----------------
>>>>
>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>> news:431e4763$1 @duster.adelaid e.on.net...
>>>>> 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[/color]
>>
>>[/color]
>
>[/color]

[Dixie]

Re: Setting field properties in code

Thanks Allen, that was all it needed - to use 'Call' in front of it. That
part is now working and I have extended that bit of code to include
InputMask and Description as well as Format.

Now, to finish this off, I am not too sure what you meant by "To make a
yes/no field display as a check box, create and set the DisplayControl
property." I have done some looking at the help file for the DisplayControl
property, but it does not give any example code, just instructions on how to
do it manually in design view. I will need to be able to set this to
TextBox for Yes/No fields - I have noticed it is not the default value.
Could you possibly give me the context for the code required to do this.

The other thing is setting an index - I have again read your post and still
can't quite make it work. I want to be able to set the index property to
Yes (Duplicates OK) and Yes (No Duplicates). Can you possibly give me an
example of code to do it, again using the Faculty Table and say a Field ID
that could be set to Indexed (Yes No Duplicates) or Indesed (Yes Duplicates
OK)

I know I am asking for a lot, but the pressure of time is getting to me. I
normally do a lot of internet 'googling' to try to find help for doing
things, but that has let me down a bit lately and I have spent many hours
fiddling with variations of what might work.

BTW, I have already read your excellent article on dealing with non american
date formats and it helped me out on a previous problem.

Thanks for your perseverence.
dixie

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:431e99d1$0 $14492$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
> Try adding the word Call, i.e.:
> Call SetPropertyDAO( ...
>
> If it won't compile, and you are using Access 2000 or 2002, then choose
> References on the Tools menu (from the code window), and check the box
> beside:
> Microsoft DAO 3.6 Library
> More on references:
> http://allenbrowne.com/ser-38.html
>
> (BTW, the Format property is a Text type, regardless of the type of
> field.)
>
> --
> 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:431e7074@d uster.adelaide. on.net...[color=green]
>>I am having the same problem with that that I was having before I gave up
>>trying to get it right. When I put that into a button module behind a
>>form (where I am testing it from), it comes up with an error as soon as I
>>put it there and fix up the _ for the broken line. Microsoft Visual Basic
>>Compile error: Expected: =
>> There is no point in looking at the help file for this error as it is
>> very generic.
>>
>> I tried changing the dbText to dbDate and it the same thing happened.
>>
>> Just to make sure I haven't misspelled anything here is the line removed
>> out of the form module.
>>
>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),"For mat",
>> dbText, "dd/mm/yyyy")
>>
>> Is there something obviously wrong?
>>
>> dixie
>>
>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>> news:431e6d53$0 $14487$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=darkred]
>>> The idea is:
>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),_
>>> "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@dogmail. com> wrote in message
>>> news:431e5f64@d uster.adelaide. on.net...
>>>> 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@Se eSig.Invalid> wrote in message
>>>> news:431e4f7d$0 $14481$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>> 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 SubDatasheetNam e 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 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
>>>>> 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
>>>>>
>>>>> Sub CreateIndexesDA O()
>>>>> Dim db As DAO.Database
>>>>> Dim tdf As DAO.TableDef
>>>>> Dim ind As DAO.Index
>>>>>
>>>>> 'Initialize
>>>>> Set db = CurrentDb()
>>>>> Set tdf = db.TableDefs("t blDaoContractor ")
>>>>>
>>>>> '1. Primary key index.
>>>>> Set ind = tdf.CreateIndex ("PrimaryKey ")
>>>>> With ind
>>>>> .Fields.Append .CreateField("C ontractorID")
>>>>> .Unique = False
>>>>> .Primary = True
>>>>> End With
>>>>> tdf.Indexes.App end ind
>>>>>
>>>>> '2. Single-field index.
>>>>> Set ind = tdf.CreateIndex ("Inactive")
>>>>> ind.Fields.Appe nd ind.CreateField ("Inactive")
>>>>> tdf.Indexes.App end ind
>>>>>
>>>>> '3. Multi-field index.
>>>>> Set ind = tdf.CreateIndex ("FullName")
>>>>> With ind
>>>>> .Fields.Append .CreateField("S urname")
>>>>> .Fields.Append .CreateField("F irstName")
>>>>> End With
>>>>> tdf.Indexes.App end ind
>>>>>
>>>>> 'Refresh the display of this collection.
>>>>> tdf.Indexes.Ref resh
>>>>>
>>>>> 'Clean up
>>>>> Set ind = Nothing
>>>>> Set tdf = Nothing
>>>>> Set db = Nothing
>>>>> Debug.Print "tblDaoContract or indexes created."
>>>>> End Sub
>>>>> ----------------------code ends-----------------
>>>>>
>>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>>> news:431e4763$1 @duster.adelaid e.on.net...
>>>>>> 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
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

[Dixie]

Re: Setting field properties in code

Great, I have just worked out how to programmaticall y display the Yes/No
field as a checkbox. Lots of googling and fiddling with things that were
close has finally rewarded with a postive result. I guess that means I am
hopefully down to the indexing as per my previous post? Any help?

dixie

"Dixie" <dixie@dogmail. com> wrote in message
news:431eb849@d uster.adelaide. on.net...[color=blue]
> Thanks Allen, that was all it needed - to use 'Call' in front of it. That
> part is now working and I have extended that bit of code to include
> InputMask and Description as well as Format.
>
> Now, to finish this off, I am not too sure what you meant by "To make a
> yes/no field display as a check box, create and set the DisplayControl
> property." I have done some looking at the help file for the
> DisplayControl property, but it does not give any example code, just
> instructions on how to do it manually in design view. I will need to be
> able to set this to TextBox for Yes/No fields - I have noticed it is not
> the default value. Could you possibly give me the context for the code
> required to do this.
>
> The other thing is setting an index - I have again read your post and
> still can't quite make it work. I want to be able to set the index
> property to Yes (Duplicates OK) and Yes (No Duplicates). Can you possibly
> give me an example of code to do it, again using the Faculty Table and say
> a Field ID that could be set to Indexed (Yes No Duplicates) or Indesed
> (Yes Duplicates OK)
>
> I know I am asking for a lot, but the pressure of time is getting to me.
> I normally do a lot of internet 'googling' to try to find help for doing
> things, but that has let me down a bit lately and I have spent many hours
> fiddling with variations of what might work.
>
> BTW, I have already read your excellent article on dealing with non
> american date formats and it helped me out on a previous problem.
>
> Thanks for your perseverence.
> dixie
>
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> news:431e99d1$0 $14492$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=green]
>> Try adding the word Call, i.e.:
>> Call SetPropertyDAO( ...
>>
>> If it won't compile, and you are using Access 2000 or 2002, then choose
>> References on the Tools menu (from the code window), and check the box
>> beside:
>> Microsoft DAO 3.6 Library
>> More on references:
>> http://allenbrowne.com/ser-38.html
>>
>> (BTW, the Format property is a Text type, regardless of the type of
>> field.)
>>
>> --
>> 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:431e7074@d uster.adelaide. on.net...[color=darkred]
>>>I am having the same problem with that that I was having before I gave up
>>>trying to get it right. When I put that into a button module behind a
>>>form (where I am testing it from), it comes up with an error as soon as I
>>>put it there and fix up the _ for the broken line. Microsoft Visual
>>>Basic Compile error: Expected: =
>>> There is no point in looking at the help file for this error as it is
>>> very generic.
>>>
>>> I tried changing the dbText to dbDate and it the same thing happened.
>>>
>>> Just to make sure I haven't misspelled anything here is the line removed
>>> out of the form module.
>>>
>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),"For mat",
>>> dbText, "dd/mm/yyyy")
>>>
>>> Is there something obviously wrong?
>>>
>>> dixie
>>>
>>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>>> news:431e6d53$0 $14487$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>> The idea is:
>>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),_
>>>> "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@dogmail. com> wrote in message
>>>> news:431e5f64@d uster.adelaide. on.net...
>>>>> 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@Se eSig.Invalid> wrote in message
>>>>> news:431e4f7d$0 $14481$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>>> 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 SubDatasheetNam e 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 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
>>>>>> 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
>>>>>>
>>>>>> Sub CreateIndexesDA O()
>>>>>> Dim db As DAO.Database
>>>>>> Dim tdf As DAO.TableDef
>>>>>> Dim ind As DAO.Index
>>>>>>
>>>>>> 'Initialize
>>>>>> Set db = CurrentDb()
>>>>>> Set tdf = db.TableDefs("t blDaoContractor ")
>>>>>>
>>>>>> '1. Primary key index.
>>>>>> Set ind = tdf.CreateIndex ("PrimaryKey ")
>>>>>> With ind
>>>>>> .Fields.Append .CreateField("C ontractorID")
>>>>>> .Unique = False
>>>>>> .Primary = True
>>>>>> End With
>>>>>> tdf.Indexes.App end ind
>>>>>>
>>>>>> '2. Single-field index.
>>>>>> Set ind = tdf.CreateIndex ("Inactive")
>>>>>> ind.Fields.Appe nd ind.CreateField ("Inactive")
>>>>>> tdf.Indexes.App end ind
>>>>>>
>>>>>> '3. Multi-field index.
>>>>>> Set ind = tdf.CreateIndex ("FullName")
>>>>>> With ind
>>>>>> .Fields.Append .CreateField("S urname")
>>>>>> .Fields.Append .CreateField("F irstName")
>>>>>> End With
>>>>>> tdf.Indexes.App end ind
>>>>>>
>>>>>> 'Refresh the display of this collection.
>>>>>> tdf.Indexes.Ref resh
>>>>>>
>>>>>> 'Clean up
>>>>>> Set ind = Nothing
>>>>>> Set tdf = Nothing
>>>>>> Set db = Nothing
>>>>>> Debug.Print "tblDaoContract or indexes created."
>>>>>> End Sub
>>>>>> ----------------------code ends-----------------
>>>>>>
>>>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>>>> news:431e4763$1 @duster.adelaid e.on.net...
>>>>>>> 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
>>>>
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

[Allen Browne]

Re: Setting field properties in code

Late in that code, there was an example of how to create an index on the
*table* for the field(s) you want to index.

(There was also an example of setting the DisplayControl to checkbox.)

--
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:431eccf1@d uster.adelaide. on.net...[color=blue]
> Great, I have just worked out how to programmaticall y display the Yes/No
> field as a checkbox. Lots of googling and fiddling with things that were
> close has finally rewarded with a postive result. I guess that means I am
> hopefully down to the indexing as per my previous post? Any help?
>
> dixie
>
> "Dixie" <dixie@dogmail. com> wrote in message
> news:431eb849@d uster.adelaide. on.net...[color=green]
>> Thanks Allen, that was all it needed - to use 'Call' in front of it.
>> That part is now working and I have extended that bit of code to include
>> InputMask and Description as well as Format.
>>
>> Now, to finish this off, I am not too sure what you meant by "To make a
>> yes/no field display as a check box, create and set the DisplayControl
>> property." I have done some looking at the help file for the
>> DisplayControl property, but it does not give any example code, just
>> instructions on how to do it manually in design view. I will need to be
>> able to set this to TextBox for Yes/No fields - I have noticed it is not
>> the default value. Could you possibly give me the context for the code
>> required to do this.
>>
>> The other thing is setting an index - I have again read your post and
>> still can't quite make it work. I want to be able to set the index
>> property to Yes (Duplicates OK) and Yes (No Duplicates). Can you
>> possibly give me an example of code to do it, again using the Faculty
>> Table and say a Field ID that could be set to Indexed (Yes No Duplicates)
>> or Indesed (Yes Duplicates OK)
>>
>> I know I am asking for a lot, but the pressure of time is getting to me.
>> I normally do a lot of internet 'googling' to try to find help for doing
>> things, but that has let me down a bit lately and I have spent many hours
>> fiddling with variations of what might work.
>>
>> BTW, I have already read your excellent article on dealing with non
>> american date formats and it helped me out on a previous problem.
>>
>> Thanks for your perseverence.
>> dixie
>>
>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>> news:431e99d1$0 $14492$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=darkred]
>>> Try adding the word Call, i.e.:
>>> Call SetPropertyDAO( ...
>>>
>>> If it won't compile, and you are using Access 2000 or 2002, then choose
>>> References on the Tools menu (from the code window), and check the box
>>> beside:
>>> Microsoft DAO 3.6 Library
>>> More on references:
>>> http://allenbrowne.com/ser-38.html
>>>
>>> (BTW, the Format property is a Text type, regardless of the type of
>>> field.)
>>>
>>> --
>>> 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:431e7074@d uster.adelaide. on.net...
>>>>I am having the same problem with that that I was having before I gave
>>>>up trying to get it right. When I put that into a button module behind
>>>>a form (where I am testing it from), it comes up with an error as soon
>>>>as I put it there and fix up the _ for the broken line. Microsoft
>>>>Visual Basic Compile error: Expected: =
>>>> There is no point in looking at the help file for this error as it is
>>>> very generic.
>>>>
>>>> I tried changing the dbText to dbDate and it the same thing happened.
>>>>
>>>> Just to make sure I haven't misspelled anything here is the line
>>>> removed out of the form module.
>>>>
>>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),"For mat",
>>>> dbText, "dd/mm/yyyy")
>>>>
>>>> Is there something obviously wrong?
>>>>
>>>> dixie
>>>>
>>>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>>>> news:431e6d53$0 $14487$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>> The idea is:
>>>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),_
>>>>> "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@dogmail. com> wrote in message
>>>>> news:431e5f64@d uster.adelaide. on.net...
>>>>>> 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@Se eSig.Invalid> wrote in message
>>>>>> news:431e4f7d$0 $14481$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>>>> 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 SubDatasheetNam e 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 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
>>>>>>> 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
>>>>>>>
>>>>>>> Sub CreateIndexesDA O()
>>>>>>> Dim db As DAO.Database
>>>>>>> Dim tdf As DAO.TableDef
>>>>>>> Dim ind As DAO.Index
>>>>>>>
>>>>>>> 'Initialize
>>>>>>> Set db = CurrentDb()
>>>>>>> Set tdf = db.TableDefs("t blDaoContractor ")
>>>>>>>
>>>>>>> '1. Primary key index.
>>>>>>> Set ind = tdf.CreateIndex ("PrimaryKey ")
>>>>>>> With ind
>>>>>>> .Fields.Append .CreateField("C ontractorID")
>>>>>>> .Unique = False
>>>>>>> .Primary = True
>>>>>>> End With
>>>>>>> tdf.Indexes.App end ind
>>>>>>>
>>>>>>> '2. Single-field index.
>>>>>>> Set ind = tdf.CreateIndex ("Inactive")
>>>>>>> ind.Fields.Appe nd ind.CreateField ("Inactive")
>>>>>>> tdf.Indexes.App end ind
>>>>>>>
>>>>>>> '3. Multi-field index.
>>>>>>> Set ind = tdf.CreateIndex ("FullName")
>>>>>>> With ind
>>>>>>> .Fields.Append .CreateField("S urname")
>>>>>>> .Fields.Append .CreateField("F irstName")
>>>>>>> End With
>>>>>>> tdf.Indexes.App end ind
>>>>>>>
>>>>>>> 'Refresh the display of this collection.
>>>>>>> tdf.Indexes.Ref resh
>>>>>>>
>>>>>>> 'Clean up
>>>>>>> Set ind = Nothing
>>>>>>> Set tdf = Nothing
>>>>>>> Set db = Nothing
>>>>>>> Debug.Print "tblDaoContract or indexes created."
>>>>>>> End Sub
>>>>>>> ----------------------code ends-----------------
>>>>>>>
>>>>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>>>>> news:431e4763$1 @duster.adelaid e.on.net...
>>>>>>>> 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
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

[David W. Fenton]

Re: Setting field properties in code

"Albert D. Kallal" <kallal@msn.com > wrote in
news:w4uTe.4185 84$s54.174586@p d7tw2no:
[color=blue]
> I should point out that modifying the format in a table does NOT
> change the format in your existing application.[/color]

Well, it won't override an explicitly set format in, say, a form
control, but if there's no format set in the control, the control
will inherit the underlying field's format, including the new one
after a change to the table.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Dixie]

Re: Setting field properties in code

Sorry, I didn't understand the first time and got involved with the other
parts. I have just been experimenting with the code for creating indexes
and now realise that it is the .unique = true/false that sets the No
Duplicates and Duplicates OK parts.

Just a question for my understanding. Does it matter what you call the
index? Like in the line:
Set ind = tdf.CreateIndex ("MyNewIndex ") From my experimentation it doesn't
seem to matter. I used the exact same name as the field and it seemed to
work OK, then I did it again and used "MyNewIndex " and again it seemed to
work. When you create an index in design view, what is the index naming
convention that Access uses?

I tried to modify the code to use Delete instead of Append to delete an
index, but it didn't work. How would I modify this code to delete an
existing index?.

Set ind = tdf.CreateIndex ("fldAditional" )
ind.Fields.Appe nd ind.CreateField ("fldAdditional ")
tdf.Indexes.App end ind

Allen thanks heaps for the help. I really appreciate it. This is an area
that I have never been into before and found it a little daunting. But to
update an application that has lots of new fields on existing tables and
changed field properties for others, it is essential.

dixie

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:431efbf1$0 $14453$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
> Late in that code, there was an example of how to create an index on the
> *table* for the field(s) you want to index.
>
> (There was also an example of setting the DisplayControl to checkbox.)
>
> --
> 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:431eccf1@d uster.adelaide. on.net...[color=green]
>> Great, I have just worked out how to programmaticall y display the Yes/No
>> field as a checkbox. Lots of googling and fiddling with things that were
>> close has finally rewarded with a postive result. I guess that means I
>> am hopefully down to the indexing as per my previous post? Any help?
>>
>> dixie
>>
>> "Dixie" <dixie@dogmail. com> wrote in message
>> news:431eb849@d uster.adelaide. on.net...[color=darkred]
>>> Thanks Allen, that was all it needed - to use 'Call' in front of it.
>>> That part is now working and I have extended that bit of code to include
>>> InputMask and Description as well as Format.
>>>
>>> Now, to finish this off, I am not too sure what you meant by "To make a
>>> yes/no field display as a check box, create and set the DisplayControl
>>> property." I have done some looking at the help file for the
>>> DisplayControl property, but it does not give any example code, just
>>> instructions on how to do it manually in design view. I will need to be
>>> able to set this to TextBox for Yes/No fields - I have noticed it is not
>>> the default value. Could you possibly give me the context for the code
>>> required to do this.
>>>
>>> The other thing is setting an index - I have again read your post and
>>> still can't quite make it work. I want to be able to set the index
>>> property to Yes (Duplicates OK) and Yes (No Duplicates). Can you
>>> possibly give me an example of code to do it, again using the Faculty
>>> Table and say a Field ID that could be set to Indexed (Yes No
>>> Duplicates) or Indesed (Yes Duplicates OK)
>>>
>>> I know I am asking for a lot, but the pressure of time is getting to me.
>>> I normally do a lot of internet 'googling' to try to find help for doing
>>> things, but that has let me down a bit lately and I have spent many
>>> hours fiddling with variations of what might work.
>>>
>>> BTW, I have already read your excellent article on dealing with non
>>> american date formats and it helped me out on a previous problem.
>>>
>>> Thanks for your perseverence.
>>> dixie
>>>
>>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>>> news:431e99d1$0 $14492$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>> Try adding the word Call, i.e.:
>>>> Call SetPropertyDAO( ...
>>>>
>>>> If it won't compile, and you are using Access 2000 or 2002, then choose
>>>> References on the Tools menu (from the code window), and check the box
>>>> beside:
>>>> Microsoft DAO 3.6 Library
>>>> More on references:
>>>> http://allenbrowne.com/ser-38.html
>>>>
>>>> (BTW, the Format property is a Text type, regardless of the type of
>>>> field.)
>>>>
>>>> --
>>>> 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:431e7074@d uster.adelaide. on.net...
>>>>>I am having the same problem with that that I was having before I gave
>>>>>up trying to get it right. When I put that into a button module behind
>>>>>a form (where I am testing it from), it comes up with an error as soon
>>>>>as I put it there and fix up the _ for the broken line. Microsoft
>>>>>Visual Basic Compile error: Expected: =
>>>>> There is no point in looking at the help file for this error as it is
>>>>> very generic.
>>>>>
>>>>> I tried changing the dbText to dbDate and it the same thing happened.
>>>>>
>>>>> Just to make sure I haven't misspelled anything here is the line
>>>>> removed out of the form module.
>>>>>
>>>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),"For mat",
>>>>> dbText, "dd/mm/yyyy")
>>>>>
>>>>> Is there something obviously wrong?
>>>>>
>>>>> dixie
>>>>>
>>>>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>>>>> news:431e6d53$0 $14487$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>>> The idea is:
>>>>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),_
>>>>>> "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@dogmail. com> wrote in message
>>>>>> news:431e5f64@d uster.adelaide. on.net...
>>>>>>> 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@Se eSig.Invalid> wrote in message
>>>>>>> news:431e4f7d$0 $14481$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>>>>> 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 SubDatasheetNam e 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 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
>>>>>>>> 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
>>>>>>>>
>>>>>>>> Sub CreateIndexesDA O()
>>>>>>>> Dim db As DAO.Database
>>>>>>>> Dim tdf As DAO.TableDef
>>>>>>>> Dim ind As DAO.Index
>>>>>>>>
>>>>>>>> 'Initialize
>>>>>>>> Set db = CurrentDb()
>>>>>>>> Set tdf = db.TableDefs("t blDaoContractor ")
>>>>>>>>
>>>>>>>> '1. Primary key index.
>>>>>>>> Set ind = tdf.CreateIndex ("PrimaryKey ")
>>>>>>>> With ind
>>>>>>>> .Fields.Append .CreateField("C ontractorID")
>>>>>>>> .Unique = False
>>>>>>>> .Primary = True
>>>>>>>> End With
>>>>>>>> tdf.Indexes.App end ind
>>>>>>>>
>>>>>>>> '2. Single-field index.
>>>>>>>> Set ind = tdf.CreateIndex ("Inactive")
>>>>>>>> ind.Fields.Appe nd ind.CreateField ("Inactive")
>>>>>>>> tdf.Indexes.App end ind
>>>>>>>>
>>>>>>>> '3. Multi-field index.
>>>>>>>> Set ind = tdf.CreateIndex ("FullName")
>>>>>>>> With ind
>>>>>>>> .Fields.Append .CreateField("S urname")
>>>>>>>> .Fields.Append .CreateField("F irstName")
>>>>>>>> End With
>>>>>>>> tdf.Indexes.App end ind
>>>>>>>>
>>>>>>>> 'Refresh the display of this collection.
>>>>>>>> tdf.Indexes.Ref resh
>>>>>>>>
>>>>>>>> 'Clean up
>>>>>>>> Set ind = Nothing
>>>>>>>> Set tdf = Nothing
>>>>>>>> Set db = Nothing
>>>>>>>> Debug.Print "tblDaoContract or indexes created."
>>>>>>>> End Sub
>>>>>>>> ----------------------code ends-----------------
>>>>>>>>
>>>>>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>>>>>> news:431e4763$1 @duster.adelaid e.on.net...
>>>>>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]

[Allen Browne]

Re: Setting field properties in code

The name of the index does not matter.

By default, Access names the primary key index PrimaryKey (without the
space), but you can call it anything you like. By default, it names an index
on a field with the same name as the field if that is available, but if not
it uses a GUID as the name. Any valid name will do, but naturally a
descriptive one is preferred as it is self-documenting.

You should also be aware that Access automatically creates a hidden index on
the foreign key field when you create a relation with referential integrity
enforced. That means you don't want to manually index your foreign key
fields when designing your database, and also that the number of indexes you
can see programmaticall y is probabably greater than the number shown in the
Indexes box in table design view.

You can delete an index programmaticall y by using the Delete method on the
Indexes collection of the TableDef:
dbEngine(0)(0). TableDefs("MyTa ble").Indexes.D elete "MyIndex"

--
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:431f5f01@d uster.adelaide. on.net...[color=blue]
> Sorry, I didn't understand the first time and got involved with the other
> parts. I have just been experimenting with the code for creating indexes
> and now realise that it is the .unique = true/false that sets the No
> Duplicates and Duplicates OK parts.
>
> Just a question for my understanding. Does it matter what you call the
> index? Like in the line:
> Set ind = tdf.CreateIndex ("MyNewIndex ") From my experimentation it
> doesn't seem to matter. I used the exact same name as the field and it
> seemed to work OK, then I did it again and used "MyNewIndex " and again it
> seemed to work. When you create an index in design view, what is the
> index naming convention that Access uses?
>
> I tried to modify the code to use Delete instead of Append to delete an
> index, but it didn't work. How would I modify this code to delete an
> existing index?.
>
> Set ind = tdf.CreateIndex ("fldAditional" )
> ind.Fields.Appe nd ind.CreateField ("fldAdditional ")
> tdf.Indexes.App end ind
>
> Allen thanks heaps for the help. I really appreciate it. This is an area
> that I have never been into before and found it a little daunting. But to
> update an application that has lots of new fields on existing tables and
> changed field properties for others, it is essential.
>
> dixie
>
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
> news:431efbf1$0 $14453$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=green]
>> Late in that code, there was an example of how to create an index on the
>> *table* for the field(s) you want to index.
>>
>> (There was also an example of setting the DisplayControl to checkbox.)
>>
>> "Dixie" <dixie@dogmail. com> wrote in message
>> news:431eccf1@d uster.adelaide. on.net...[color=darkred]
>>> Great, I have just worked out how to programmaticall y display the Yes/No
>>> field as a checkbox. Lots of googling and fiddling with things that
>>> were close has finally rewarded with a postive result. I guess that
>>> means I am hopefully down to the indexing as per my previous post? Any
>>> help?
>>>
>>> dixie
>>>
>>> "Dixie" <dixie@dogmail. com> wrote in message
>>> news:431eb849@d uster.adelaide. on.net...
>>>> Thanks Allen, that was all it needed - to use 'Call' in front of it.
>>>> That part is now working and I have extended that bit of code to
>>>> include InputMask and Description as well as Format.
>>>>
>>>> Now, to finish this off, I am not too sure what you meant by "To make a
>>>> yes/no field display as a check box, create and set the DisplayControl
>>>> property." I have done some looking at the help file for the
>>>> DisplayControl property, but it does not give any example code, just
>>>> instructions on how to do it manually in design view. I will need to
>>>> be able to set this to TextBox for Yes/No fields - I have noticed it is
>>>> not the default value. Could you possibly give me the context for the
>>>> code required to do this.
>>>>
>>>> The other thing is setting an index - I have again read your post and
>>>> still can't quite make it work. I want to be able to set the index
>>>> property to Yes (Duplicates OK) and Yes (No Duplicates). Can you
>>>> possibly give me an example of code to do it, again using the Faculty
>>>> Table and say a Field ID that could be set to Indexed (Yes No
>>>> Duplicates) or Indesed (Yes Duplicates OK)
>>>>
>>>> I know I am asking for a lot, but the pressure of time is getting to
>>>> me. I normally do a lot of internet 'googling' to try to find help for
>>>> doing things, but that has let me down a bit lately and I have spent
>>>> many hours fiddling with variations of what might work.
>>>>
>>>> BTW, I have already read your excellent article on dealing with non
>>>> american date formats and it helped me out on a previous problem.
>>>>
>>>> Thanks for your perseverence.
>>>> dixie
>>>>
>>>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>>>> news:431e99d1$0 $14492$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>> Try adding the word Call, i.e.:
>>>>> Call SetPropertyDAO( ...
>>>>>
>>>>> If it won't compile, and you are using Access 2000 or 2002, then
>>>>> choose References on the Tools menu (from the code window), and check
>>>>> the box beside:
>>>>> Microsoft DAO 3.6 Library
>>>>> More on references:
>>>>> http://allenbrowne.com/ser-38.html
>>>>>
>>>>> (BTW, the Format property is a Text type, regardless of the type of
>>>>> field.)
>>>>>
>>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>>> news:431e7074@d uster.adelaide. on.net...
>>>>>>I am having the same problem with that that I was having before I gave
>>>>>>up trying to get it right. When I put that into a button module
>>>>>>behind a form (where I am testing it from), it comes up with an error
>>>>>>as soon as I put it there and fix up the _ for the broken line.
>>>>>>Microso ft Visual Basic Compile error: Expected: =
>>>>>> There is no point in looking at the help file for this error as it is
>>>>>> very generic.
>>>>>>
>>>>>> I tried changing the dbText to dbDate and it the same thing happened.
>>>>>>
>>>>>> Just to make sure I haven't misspelled anything here is the line
>>>>>> removed out of the form module.
>>>>>>
>>>>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),"For mat",
>>>>>> dbText, "dd/mm/yyyy")
>>>>>>
>>>>>> Is there something obviously wrong?
>>>>>>
>>>>>> dixie
>>>>>>
>>>>>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>>>>>> news:431e6d53$0 $14487$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>>>> The idea is:
>>>>>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),_
>>>>>>> "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.
>>>>>>>
>>>>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>>>>> news:431e5f64@d uster.adelaide. on.net...
>>>>>>>> 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@Se eSig.Invalid> wrote in message
>>>>>>>> news:431e4f7d$0 $14481$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>>>>>> 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 SubDatasheetNam e 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 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
>>>>>>>>> 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
>>>>>>>>>
>>>>>>>>> Sub CreateIndexesDA O()
>>>>>>>>> Dim db As DAO.Database
>>>>>>>>> Dim tdf As DAO.TableDef
>>>>>>>>> Dim ind As DAO.Index
>>>>>>>>>
>>>>>>>>> 'Initialize
>>>>>>>>> Set db = CurrentDb()
>>>>>>>>> Set tdf = db.TableDefs("t blDaoContractor ")
>>>>>>>>>
>>>>>>>>> '1. Primary key index.
>>>>>>>>> Set ind = tdf.CreateIndex ("PrimaryKey ")
>>>>>>>>> With ind
>>>>>>>>> .Fields.Append .CreateField("C ontractorID")
>>>>>>>>> .Unique = False
>>>>>>>>> .Primary = True
>>>>>>>>> End With
>>>>>>>>> tdf.Indexes.App end ind
>>>>>>>>>
>>>>>>>>> '2. Single-field index.
>>>>>>>>> Set ind = tdf.CreateIndex ("Inactive")
>>>>>>>>> ind.Fields.Appe nd ind.CreateField ("Inactive")
>>>>>>>>> tdf.Indexes.App end ind
>>>>>>>>>
>>>>>>>>> '3. Multi-field index.
>>>>>>>>> Set ind = tdf.CreateIndex ("FullName")
>>>>>>>>> With ind
>>>>>>>>> .Fields.Append .CreateField("S urname")
>>>>>>>>> .Fields.Append .CreateField("F irstName")
>>>>>>>>> End With
>>>>>>>>> tdf.Indexes.App end ind
>>>>>>>>>
>>>>>>>>> 'Refresh the display of this collection.
>>>>>>>>> tdf.Indexes.Ref resh
>>>>>>>>>
>>>>>>>>> 'Clean up
>>>>>>>>> Set ind = Nothing
>>>>>>>>> Set tdf = Nothing
>>>>>>>>> Set db = Nothing
>>>>>>>>> Debug.Print "tblDaoContract or indexes created."
>>>>>>>>> End Sub
>>>>>>>>> ----------------------code ends-----------------
>>>>>>>>>
>>>>>>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>>>>>>> news:431e4763$1 @duster.adelaid e.on.net...
>>>>>>>>>> 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?[/color][/color][/color]

[Dixie]

Re: Setting field properties in code

I tried to delete an index that I had just created. When I run the code to
delete it, I get an error '3265' Item not found in this collection. I have
typed in the correct name for the index, which in this test instance is
MyNewIndex. The table is tblFaculty. The code is being run temporarily
from an on click event on a button. The exact line of code I used was.

DBEngine(0)(0). TableDefs("tblF aculty").Indexe s.Delete "MyNewIndex "

I have viewed the indexes for this field and it is indeed showing as
MyNewIndex. There is only one table in this test database, therefore no
relationships to get in the way.

What have I done wrong now? Sorry.

I also found a reference to DROP Index in the Help file. I created a SQL
query using
DROP INDEX MyNewIndex ON tblFaculty;
and it worked. Is there a downside to that method?

dixie

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
news:431f930f$0 $14493$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=blue]
> The name of the index does not matter.
>
> By default, Access names the primary key index PrimaryKey (without the
> space), but you can call it anything you like. By default, it names an
> index on a field with the same name as the field if that is available, but
> if not it uses a GUID as the name. Any valid name will do, but naturally a
> descriptive one is preferred as it is self-documenting.
>
> You should also be aware that Access automatically creates a hidden index
> on the foreign key field when you create a relation with referential
> integrity enforced. That means you don't want to manually index your
> foreign key fields when designing your database, and also that the number
> of indexes you can see programmaticall y is probabably greater than the
> number shown in the Indexes box in table design view.
>
> You can delete an index programmaticall y by using the Delete method on the
> Indexes collection of the TableDef:
> dbEngine(0)(0). TableDefs("MyTa ble").Indexes.D elete "MyIndex"
>
> --
> 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:431f5f01@d uster.adelaide. on.net...[color=green]
>> Sorry, I didn't understand the first time and got involved with the other
>> parts. I have just been experimenting with the code for creating indexes
>> and now realise that it is the .unique = true/false that sets the No
>> Duplicates and Duplicates OK parts.
>>
>> Just a question for my understanding. Does it matter what you call the
>> index? Like in the line:
>> Set ind = tdf.CreateIndex ("MyNewIndex ") From my experimentation it
>> doesn't seem to matter. I used the exact same name as the field and it
>> seemed to work OK, then I did it again and used "MyNewIndex " and again it
>> seemed to work. When you create an index in design view, what is the
>> index naming convention that Access uses?
>>
>> I tried to modify the code to use Delete instead of Append to delete an
>> index, but it didn't work. How would I modify this code to delete an
>> existing index?.
>>
>> Set ind = tdf.CreateIndex ("fldAditional" )
>> ind.Fields.Appe nd ind.CreateField ("fldAdditional ")
>> tdf.Indexes.App end ind
>>
>> Allen thanks heaps for the help. I really appreciate it. This is an
>> area that I have never been into before and found it a little daunting.
>> But to update an application that has lots of new fields on existing
>> tables and changed field properties for others, it is essential.
>>
>> dixie
>>
>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>> news:431efbf1$0 $14453$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...[color=darkred]
>>> Late in that code, there was an example of how to create an index on the
>>> *table* for the field(s) you want to index.
>>>
>>> (There was also an example of setting the DisplayControl to checkbox.)
>>>
>>> "Dixie" <dixie@dogmail. com> wrote in message
>>> news:431eccf1@d uster.adelaide. on.net...
>>>> Great, I have just worked out how to programmaticall y display the
>>>> Yes/No field as a checkbox. Lots of googling and fiddling with things
>>>> that were close has finally rewarded with a postive result. I guess
>>>> that means I am hopefully down to the indexing as per my previous post?
>>>> Any help?
>>>>
>>>> dixie
>>>>
>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>> news:431eb849@d uster.adelaide. on.net...
>>>>> Thanks Allen, that was all it needed - to use 'Call' in front of it.
>>>>> That part is now working and I have extended that bit of code to
>>>>> include InputMask and Description as well as Format.
>>>>>
>>>>> Now, to finish this off, I am not too sure what you meant by "To make
>>>>> a yes/no field display as a check box, create and set the
>>>>> DisplayControl property." I have done some looking at the help file
>>>>> for the DisplayControl property, but it does not give any example
>>>>> code, just instructions on how to do it manually in design view. I
>>>>> will need to be able to set this to TextBox for Yes/No fields - I have
>>>>> noticed it is not the default value. Could you possibly give me the
>>>>> context for the code required to do this.
>>>>>
>>>>> The other thing is setting an index - I have again read your post and
>>>>> still can't quite make it work. I want to be able to set the index
>>>>> property to Yes (Duplicates OK) and Yes (No Duplicates). Can you
>>>>> possibly give me an example of code to do it, again using the Faculty
>>>>> Table and say a Field ID that could be set to Indexed (Yes No
>>>>> Duplicates) or Indesed (Yes Duplicates OK)
>>>>>
>>>>> I know I am asking for a lot, but the pressure of time is getting to
>>>>> me. I normally do a lot of internet 'googling' to try to find help for
>>>>> doing things, but that has let me down a bit lately and I have spent
>>>>> many hours fiddling with variations of what might work.
>>>>>
>>>>> BTW, I have already read your excellent article on dealing with non
>>>>> american date formats and it helped me out on a previous problem.
>>>>>
>>>>> Thanks for your perseverence.
>>>>> dixie
>>>>>
>>>>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>>>>> news:431e99d1$0 $14492$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>>> Try adding the word Call, i.e.:
>>>>>> Call SetPropertyDAO( ...
>>>>>>
>>>>>> If it won't compile, and you are using Access 2000 or 2002, then
>>>>>> choose References on the Tools menu (from the code window), and check
>>>>>> the box beside:
>>>>>> Microsoft DAO 3.6 Library
>>>>>> More on references:
>>>>>> http://allenbrowne.com/ser-38.html
>>>>>>
>>>>>> (BTW, the Format property is a Text type, regardless of the type of
>>>>>> field.)
>>>>>>
>>>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>>>> news:431e7074@d uster.adelaide. on.net...
>>>>>>>I am having the same problem with that that I was having before I
>>>>>>>gave up trying to get it right. When I put that into a button module
>>>>>>>behind a form (where I am testing it from), it comes up with an error
>>>>>>>as soon as I put it there and fix up the _ for the broken line.
>>>>>>>Microsof t Visual Basic Compile error: Expected: =
>>>>>>> There is no point in looking at the help file for this error as it
>>>>>>> is very generic.
>>>>>>>
>>>>>>> I tried changing the dbText to dbDate and it the same thing
>>>>>>> happened.
>>>>>>>
>>>>>>> Just to make sure I haven't misspelled anything here is the line
>>>>>>> removed out of the form module.
>>>>>>>
>>>>>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),"For mat",
>>>>>>> dbText, "dd/mm/yyyy")
>>>>>>>
>>>>>>> Is there something obviously wrong?
>>>>>>>
>>>>>>> dixie
>>>>>>>
>>>>>>> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message
>>>>>>> news:431e6d53$0 $14487$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>>>>> The idea is:
>>>>>>>> SetPropertyDAO( dbEngine(0)(0). TableDefs("Facu lty").Fields("S tartDate"),_
>>>>>>>> "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.
>>>>>>>>
>>>>>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>>>>>> news:431e5f64@d uster.adelaide. on.net...
>>>>>>>>> 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@Se eSig.Invalid> wrote in message
>>>>>>>>> news:431e4f7d$0 $14481$5a62ac22 @per-qv1-newsreader-01.iinet.net.au ...
>>>>>>>>>> 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 SubDatasheetNam e 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 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
>>>>>>>>>> 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
>>>>>>>>>>
>>>>>>>>>> Sub CreateIndexesDA O()
>>>>>>>>>> Dim db As DAO.Database
>>>>>>>>>> Dim tdf As DAO.TableDef
>>>>>>>>>> Dim ind As DAO.Index
>>>>>>>>>>
>>>>>>>>>> 'Initialize
>>>>>>>>>> Set db = CurrentDb()
>>>>>>>>>> Set tdf = db.TableDefs("t blDaoContractor ")
>>>>>>>>>>
>>>>>>>>>> '1. Primary key index.
>>>>>>>>>> Set ind = tdf.CreateIndex ("PrimaryKey ")
>>>>>>>>>> With ind
>>>>>>>>>> .Fields.Append .CreateField("C ontractorID")
>>>>>>>>>> .Unique = False
>>>>>>>>>> .Primary = True
>>>>>>>>>> End With
>>>>>>>>>> tdf.Indexes.App end ind
>>>>>>>>>>
>>>>>>>>>> '2. Single-field index.
>>>>>>>>>> Set ind = tdf.CreateIndex ("Inactive")
>>>>>>>>>> ind.Fields.Appe nd ind.CreateField ("Inactive")
>>>>>>>>>> tdf.Indexes.App end ind
>>>>>>>>>>
>>>>>>>>>> '3. Multi-field index.
>>>>>>>>>> Set ind = tdf.CreateIndex ("FullName")
>>>>>>>>>> With ind
>>>>>>>>>> .Fields.Append .CreateField("S urname")
>>>>>>>>>> .Fields.Append .CreateField("F irstName")
>>>>>>>>>> End With
>>>>>>>>>> tdf.Indexes.App end ind
>>>>>>>>>>
>>>>>>>>>> 'Refresh the display of this collection.
>>>>>>>>>> tdf.Indexes.Ref resh
>>>>>>>>>>
>>>>>>>>>> 'Clean up
>>>>>>>>>> Set ind = Nothing
>>>>>>>>>> Set tdf = Nothing
>>>>>>>>>> Set db = Nothing
>>>>>>>>>> Debug.Print "tblDaoContract or indexes created."
>>>>>>>>>> End Sub
>>>>>>>>>> ----------------------code ends-----------------
>>>>>>>>>>
>>>>>>>>>> "Dixie" <dixie@dogmail. com> wrote in message
>>>>>>>>>> news:431e4763$1 @duster.adelaid e.on.net...
>>>>>>>>>>> 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?[/color][/color]
>
>[/color]

[Allen Browne]

Re: Setting field properties in code

Chances are that dbEngine(0)(0) has not discovered the newly created index
yet. Try using CurrentDb() in its place. When you call CurrentDb, Access
updates all the collections, and creates a new instance of the object to
work with. It is therefore preferable for where the structure is changing
(creating/deleting/modifying tables/fields/indexes/relations), but you will
need to create a Database object or else the reference lifetime is
inadequate.

There's no problem with the DROP INDEX method. DDL is quite powerful, though
not adequate for setting some of the properties you were referring to
earlier.

If you want further help with DDL queries, in Access 2003 you can open the
code window, click the Help icon on the toolbar, and walk down the Table of
Contents to:
Microsoft Jet SQL Reference
Data Definition Language
DROP Statement
--
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:431fa8f6@d uster.adelaide. on.net...[color=blue]
>I tried to delete an index that I had just created. When I run the code to
>delete it, I get an error '3265' Item not found in this collection. I have
>typed in the correct name for the index, which in this test instance is
>MyNewIndex. The table is tblFaculty. The code is being run temporarily
>from an on click event on a button. The exact line of code I used was.
>
> DBEngine(0)(0). TableDefs("tblF aculty").Indexe s.Delete "MyNewIndex "
>
> I have viewed the indexes for this field and it is indeed showing as
> MyNewIndex. There is only one table in this test database, therefore no
> relationships to get in the way.
>
> What have I done wrong now? Sorry.
>
> I also found a reference to DROP Index in the Help file. I created a SQL
> query using
> DROP INDEX MyNewIndex ON tblFaculty;
> and it worked. Is there a downside to that method?
>
> dixie[/color]