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
18  18269 
If the Field in the TableDef does not have the property, you need to
CreateProperty(). The example below shows how to create the property if it
does not exist, and set it.
To make a yes/no field display as s check box, create and set the
DisplayControl property.
To create an index on a field, CreateIndex on the table.
Note that if you are working on an attached table, you will need to
OpenDatabase and work directly on the back end.
The example below illustrates how to set what you might consider standard
properties:
- setting the table's SubDatasheetName to [None].
- setting AllowZeroLength to No for all text fields, memos, and hyperlinks.
- removing that darn zero as Default Value for numeric fields.
- setting the Format property for Currency types (and illustrating how to
set the Default Value if you wish.)
- setting Yes/No fields to display as a check box.
- setting a Caption with spaces on fields that have a mixed-case name (e.g.
OrderDate.)
- setting a Description of each field
Finally, the last example shows how to create a primary key index, a
single-field index, and a multi-field index.
Paste the code into a module, and see how it works.
----------------------code starts-----------------
Sub StandardProperties(strTableName As String)
'Purpose: Properties you always want set by default:
' TableDef: Subdatasheets off.
' Numeric fields: Remove Default Value.
' Currency fields: Format as currency.
' Yes/No fields: Display as check box. Default to No.
' Text/memo/hyperlink: AllowZeroLength off,
' UnicodeCompression on.
' All fields: Add a caption if mixed case.
'Argument: Name of the table.
'Note: Requires: SetPropertyDAO()
Dim db As DAO.Database 'Current database.
Dim tdf As DAO.TableDef 'Table nominated in argument.
Dim fld As DAO.Field 'Each field.
Dim strCaption As String 'Field caption.
Dim strErrMsg As String 'Responses and error messages.
'Initalize.
Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
'Set the table's SubdatasheetName.
Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
strErrMsg)
For Each fld In tdf.Fields
'Handle the defaults for the different field types.
Select Case fld.Type
Case dbText, dbMemo 'Includes hyperlinks.
fld.AllowZeroLength = False
Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean, _
True, strErrMsg)
Case dbCurrency
fld.DefaultValue = 0
Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
strErrMsg)
Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
fld.DefaultValue = vbNullString
Case dbBoolean
Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
CInt(acCheckBox))
End Select
'Set a caption if needed.
strCaption = ConvertMixedCase(fld.Name)
If strCaption <> fld.Name Then
Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
End If
Next
'Clean up.
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
If Len(strErrMsg) > 0 Then
Debug.Print strErrMsg
Else
Debug.Print "Properties set for table " & strTableName
End If
End Sub
Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.
If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True
ExitHandler:
Exit Function
ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
Sub CreateIndexesDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("tblDaoContractor")
'1. Primary key index.
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ContractorID")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind
'2. Single-field index.
Set ind = tdf.CreateIndex("Inactive")
ind.Fields.Append ind.CreateField("Inactive")
tdf.Indexes.Append ind
'3. Multi-field index.
Set ind = tdf.CreateIndex("FullName")
With ind
.Fields.Append .CreateField("Surname")
.Fields.Append .CreateField("FirstName")
End With
tdf.Indexes.Append ind
'Refresh the display of this collection.
tdf.Indexes.Refresh
'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
Debug.Print "tblDaoContractor indexes created."
End Sub
----------------------code ends-----------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.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
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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@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 SubDatasheetName to [None].
- setting AllowZeroLength to No for all text fields, memos, and
hyperlinks.
- removing that darn zero as Default Value for numeric fields.
- setting the Format property for Currency types (and illustrating how to
set the Default Value if you wish.)
- setting Yes/No fields to display as a check box.
- setting a Caption with spaces on fields that have a mixed-case name
(e.g. OrderDate.)
- setting a Description of each field
Finally, the last example shows how to create a primary key index, a
single-field index, and a multi-field index.
Paste the code into a module, and see how it works.
----------------------code starts-----------------
Sub StandardProperties(strTableName As String)
'Purpose: Properties you always want set by default:
' TableDef: Subdatasheets off.
' Numeric fields: Remove Default Value.
' Currency fields: Format as currency.
' Yes/No fields: Display as check box. Default to No.
' Text/memo/hyperlink: AllowZeroLength off,
' UnicodeCompression on.
' All fields: Add a caption if mixed case.
'Argument: Name of the table.
'Note: Requires: SetPropertyDAO()
Dim db As DAO.Database 'Current database.
Dim tdf As DAO.TableDef 'Table nominated in argument.
Dim fld As DAO.Field 'Each field.
Dim strCaption As String 'Field caption.
Dim strErrMsg As String 'Responses and error messages.
'Initalize.
Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
'Set the table's SubdatasheetName.
Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
strErrMsg)
For Each fld In tdf.Fields
'Handle the defaults for the different field types.
Select Case fld.Type
Case dbText, dbMemo 'Includes hyperlinks.
fld.AllowZeroLength = False
Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean, _
True, strErrMsg)
Case dbCurrency
fld.DefaultValue = 0
Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
strErrMsg)
Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
fld.DefaultValue = vbNullString
Case dbBoolean
Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
CInt(acCheckBox))
End Select
'Set a caption if needed.
strCaption = ConvertMixedCase(fld.Name)
If strCaption <> fld.Name Then
Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
End If
Next
'Clean up.
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
If Len(strErrMsg) > 0 Then
Debug.Print strErrMsg
Else
Debug.Print "Properties set for table " & strTableName
End If
End Sub
Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.
If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True
ExitHandler:
Exit Function
ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function
Public Function HasProperty(obj As Object, strPropName As String) As
Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
Sub CreateIndexesDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("tblDaoContractor")
'1. Primary key index.
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ContractorID")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind
'2. Single-field index.
Set ind = tdf.CreateIndex("Inactive")
ind.Fields.Append ind.CreateField("Inactive")
tdf.Indexes.Append ind
'3. Multi-field index.
Set ind = tdf.CreateIndex("FullName")
With ind
.Fields.Append .CreateField("Surname")
.Fields.Append .CreateField("FirstName")
End With
tdf.Indexes.Append ind
'Refresh the display of this collection.
tdf.Indexes.Refresh
'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
Debug.Print "tblDaoContractor indexes created."
End Sub
----------------------code ends-----------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.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
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/AlbertKal...plit/index.htm
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl*****************@msn.com http://www.members.shaw.ca/AlbertKallal
The idea is:
SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty") .Fields("StartDate"),_
"Format", dbText, "dd/mm/yyyy")
Or you might use "General Date" in place of "dd/mm/yyyy", which will use the
format that the user has defined in the Windows Control Panel, under
Regional Options.
BTW, if you are having difficulties with the Australian date format in
Access, see:
International Date Formats in Access
at: http://allenbrowne.com/ser-36.html
It deals with the 3 cases that often trip people up.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@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 SubDatasheetName to [None].
- setting AllowZeroLength to No for all text fields, memos, and
hyperlinks.
- removing that darn zero as Default Value for numeric fields.
- setting the Format property for Currency types (and illustrating how to
set the Default Value if you wish.)
- setting Yes/No fields to display as a check box.
- setting a Caption with spaces on fields that have a mixed-case name
(e.g. OrderDate.)
- setting a Description of each field
Finally, the last example shows how to create a primary key index, a
single-field index, and a multi-field index.
Paste the code into a module, and see how it works.
----------------------code starts-----------------
Sub StandardProperties(strTableName As String)
'Purpose: Properties you always want set by default:
' TableDef: Subdatasheets off.
' Numeric fields: Remove Default Value.
' Currency fields: Format as currency.
' Yes/No fields: Display as check box. Default to No.
' Text/memo/hyperlink: AllowZeroLength off,
' UnicodeCompression on.
' All fields: Add a caption if mixed case.
'Argument: Name of the table.
'Note: Requires: SetPropertyDAO()
Dim db As DAO.Database 'Current database.
Dim tdf As DAO.TableDef 'Table nominated in argument.
Dim fld As DAO.Field 'Each field.
Dim strCaption As String 'Field caption.
Dim strErrMsg As String 'Responses and error messages.
'Initalize.
Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
'Set the table's SubdatasheetName.
Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
strErrMsg)
For Each fld In tdf.Fields
'Handle the defaults for the different field types.
Select Case fld.Type
Case dbText, dbMemo 'Includes hyperlinks.
fld.AllowZeroLength = False
Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean, _
True, strErrMsg)
Case dbCurrency
fld.DefaultValue = 0
Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
strErrMsg)
Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
fld.DefaultValue = vbNullString
Case dbBoolean
Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
CInt(acCheckBox))
End Select
'Set a caption if needed.
strCaption = ConvertMixedCase(fld.Name)
If strCaption <> fld.Name Then
Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
End If
Next
'Clean up.
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
If Len(strErrMsg) > 0 Then
Debug.Print strErrMsg
Else
Debug.Print "Properties set for table " & strTableName
End If
End Sub
Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.
If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True
ExitHandler:
Exit Function
ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set
to " & varValue & ". Error " & Err.Number & " - " & Err.Description &
vbCrLf
Resume ExitHandler
End Function
Public Function HasProperty(obj As Object, strPropName As String) As
Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
Sub CreateIndexesDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("tblDaoContractor")
'1. Primary key index.
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ContractorID")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind
'2. Single-field index.
Set ind = tdf.CreateIndex("Inactive")
ind.Fields.Append ind.CreateField("Inactive")
tdf.Indexes.Append ind
'3. Multi-field index.
Set ind = tdf.CreateIndex("FullName")
With ind
.Fields.Append .CreateField("Surname")
.Fields.Append .CreateField("FirstName")
End With
tdf.Indexes.Append ind
'Refresh the display of this collection.
tdf.Indexes.Refresh
'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
Debug.Print "tblDaoContractor indexes created."
End Sub
----------------------code ends-----------------
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.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
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("Faculty") .Fields("StartDate"),"Format",
dbText, "dd/mm/yyyy")
Is there something obviously wrong?
dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au... The idea is:
SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty") .Fields("StartDate"),_
"Format", dbText, "dd/mm/yyyy")
Or you might use "General Date" in place of "dd/mm/yyyy", which will use
the format that the user has defined in the Windows Control Panel, under
Regional Options.
BTW, if you are having difficulties with the Australian date format in
Access, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
It deals with the 3 cases that often trip people up.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@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 SubDatasheetName to [None].
- setting AllowZeroLength to No for all text fields, memos, and
hyperlinks.
- removing that darn zero as Default Value for numeric fields.
- setting the Format property for Currency types (and illustrating how
to set the Default Value if you wish.)
- setting Yes/No fields to display as a check box.
- setting a Caption with spaces on fields that have a mixed-case name
(e.g. OrderDate.)
- setting a Description of each field
Finally, the last example shows how to create a primary key index, a
single-field index, and a multi-field index.
Paste the code into a module, and see how it works.
----------------------code starts-----------------
Sub StandardProperties(strTableName As String)
'Purpose: Properties you always want set by default:
' TableDef: Subdatasheets off.
' Numeric fields: Remove Default Value.
' Currency fields: Format as currency.
' Yes/No fields: Display as check box. Default to No.
' Text/memo/hyperlink: AllowZeroLength off,
' UnicodeCompression on.
' All fields: Add a caption if mixed case.
'Argument: Name of the table.
'Note: Requires: SetPropertyDAO()
Dim db As DAO.Database 'Current database.
Dim tdf As DAO.TableDef 'Table nominated in argument.
Dim fld As DAO.Field 'Each field.
Dim strCaption As String 'Field caption.
Dim strErrMsg As String 'Responses and error messages.
'Initalize.
Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
'Set the table's SubdatasheetName.
Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
strErrMsg)
For Each fld In tdf.Fields
'Handle the defaults for the different field types.
Select Case fld.Type
Case dbText, dbMemo 'Includes hyperlinks.
fld.AllowZeroLength = False
Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean, _
True, strErrMsg)
Case dbCurrency
fld.DefaultValue = 0
Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
strErrMsg)
Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
fld.DefaultValue = vbNullString
Case dbBoolean
Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
CInt(acCheckBox))
End Select
'Set a caption if needed.
strCaption = ConvertMixedCase(fld.Name)
If strCaption <> fld.Name Then
Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
End If
Next
'Clean up.
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
If Len(strErrMsg) > 0 Then
Debug.Print strErrMsg
Else
Debug.Print "Properties set for table " & strTableName
End If
End Sub
Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String)
As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.
If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName,
intType, varValue)
End If
SetPropertyDAO = True
ExitHandler:
Exit Function
ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set
to " & varValue & ". Error " & Err.Number & " - " & Err.Description &
vbCrLf
Resume ExitHandler
End Function
Public Function HasProperty(obj As Object, strPropName As String) As
Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
Sub CreateIndexesDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("tblDaoContractor")
'1. Primary key index.
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ContractorID")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind
'2. Single-field index.
Set ind = tdf.CreateIndex("Inactive")
ind.Fields.Append ind.CreateField("Inactive")
tdf.Indexes.Append ind
'3. Multi-field index.
Set ind = tdf.CreateIndex("FullName")
With ind
.Fields.Append .CreateField("Surname")
.Fields.Append .CreateField("FirstName")
End With
tdf.Indexes.Append ind
'Refresh the display of this collection.
tdf.Indexes.Refresh
'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
Debug.Print "tblDaoContractor indexes created."
End Sub
----------------------code ends-----------------
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.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
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" <di***@dogmail.com> wrote in message
news:43******@duster.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("Faculty") .Fields("StartDate"),"Format",
dbText, "dd/mm/yyyy")
Is there something obviously wrong?
dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au... The idea is:
SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty") .Fields("StartDate"),_
"Format", dbText, "dd/mm/yyyy")
Or you might use "General Date" in place of "dd/mm/yyyy", which will use
the format that the user has defined in the Windows Control Panel, under
Regional Options.
BTW, if you are having difficulties with the Australian date format in
Access, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
It deals with the 3 cases that often trip people up.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@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 SubDatasheetName to [None].
- setting AllowZeroLength to No for all text fields, memos, and
hyperlinks.
- removing that darn zero as Default Value for numeric fields.
- setting the Format property for Currency types (and illustrating how
to set the Default Value if you wish.)
- setting Yes/No fields to display as a check box.
- setting a Caption with spaces on fields that have a mixed-case name
(e.g. OrderDate.)
- setting a Description of each field
Finally, the last example shows how to create a primary key index, a
single-field index, and a multi-field index.
Paste the code into a module, and see how it works.
----------------------code starts-----------------
Sub StandardProperties(strTableName As String)
'Purpose: Properties you always want set by default:
' TableDef: Subdatasheets off.
' Numeric fields: Remove Default Value.
' Currency fields: Format as currency.
' Yes/No fields: Display as check box. Default to No.
' Text/memo/hyperlink: AllowZeroLength off,
' UnicodeCompression on.
' All fields: Add a caption if mixed case.
'Argument: Name of the table.
'Note: Requires: SetPropertyDAO()
Dim db As DAO.Database 'Current database.
Dim tdf As DAO.TableDef 'Table nominated in argument.
Dim fld As DAO.Field 'Each field.
Dim strCaption As String 'Field caption.
Dim strErrMsg As String 'Responses and error messages.
'Initalize.
Set db = CurrentDb()
Set tdf = db.TableDefs(strTableName)
'Set the table's SubdatasheetName.
Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
strErrMsg)
For Each fld In tdf.Fields
'Handle the defaults for the different field types.
Select Case fld.Type
Case dbText, dbMemo 'Includes hyperlinks.
fld.AllowZeroLength = False
Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean, _
True, strErrMsg)
Case dbCurrency
fld.DefaultValue = 0
Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
strErrMsg)
Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
fld.DefaultValue = vbNullString
Case dbBoolean
Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
CInt(acCheckBox))
End Select
'Set a caption if needed.
strCaption = ConvertMixedCase(fld.Name)
If strCaption <> fld.Name Then
Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
End If
Next
'Clean up.
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
If Len(strErrMsg) > 0 Then
Debug.Print strErrMsg
Else
Debug.Print "Properties set for table " & strTableName
End If
End Sub
Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String)
As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.
If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName,
intType, varValue)
End If
SetPropertyDAO = True
ExitHandler:
Exit Function
ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set
to " & varValue & ". Error " & Err.Number & " - " & Err.Description &
vbCrLf
Resume ExitHandler
End Function
Public Function HasProperty(obj As Object, strPropName As String) As
Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant
On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
Sub CreateIndexesDAO()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
'Initialize
Set db = CurrentDb()
Set tdf = db.TableDefs("tblDaoContractor")
'1. Primary key index.
Set ind = tdf.CreateIndex("PrimaryKey")
With ind
.Fields.Append .CreateField("ContractorID")
.Unique = False
.Primary = True
End With
tdf.Indexes.Append ind
'2. Single-field index.
Set ind = tdf.CreateIndex("Inactive")
ind.Fields.Append ind.CreateField("Inactive")
tdf.Indexes.Append ind
'3. Multi-field index.
Set ind = tdf.CreateIndex("FullName")
With ind
.Fields.Append .CreateField("Surname")
.Fields.Append .CreateField("FirstName")
End With
tdf.Indexes.Append ind
'Refresh the display of this collection.
tdf.Indexes.Refresh
'Clean up
Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
Debug.Print "tblDaoContractor indexes created."
End Sub
----------------------code ends-----------------
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.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
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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@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" <di***@dogmail.com> wrote in message
news:43******@duster.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("Faculty") .Fields("StartDate"),"Format",
dbText, "dd/mm/yyyy")
Is there something obviously wrong?
dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au... The idea is:
SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty") .Fields("StartDate"),_
"Format", dbText, "dd/mm/yyyy")
Or you might use "General Date" in place of "dd/mm/yyyy", which will use
the format that the user has defined in the Windows Control Panel, under
Regional Options.
BTW, if you are having difficulties with the Australian date format in
Access, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
It deals with the 3 cases that often trip people up.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@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 SubDatasheetName to [None].
> - setting AllowZeroLength to No for all text fields, memos, and
> hyperlinks.
> - removing that darn zero as Default Value for numeric fields.
> - setting the Format property for Currency types (and illustrating how
> to set the Default Value if you wish.)
> - setting Yes/No fields to display as a check box.
> - setting a Caption with spaces on fields that have a mixed-case name
> (e.g. OrderDate.)
> - setting a Description of each field
>
> Finally, the last example shows how to create a primary key index, a
> single-field index, and a multi-field index.
>
> Paste the code into a module, and see how it works.
> ----------------------code starts-----------------
> Sub StandardProperties(strTableName As String)
> 'Purpose: Properties you always want set by default:
> ' TableDef: Subdatasheets off.
> ' Numeric fields: Remove Default Value.
> ' Currency fields: Format as currency.
> ' Yes/No fields: Display as check box. Default to No.
> ' Text/memo/hyperlink: AllowZeroLength off,
> ' UnicodeCompression on.
> ' All fields: Add a caption if mixed case.
> 'Argument: Name of the table.
> 'Note: Requires: SetPropertyDAO()
> Dim db As DAO.Database 'Current database.
> Dim tdf As DAO.TableDef 'Table nominated in argument.
> Dim fld As DAO.Field 'Each field.
> Dim strCaption As String 'Field caption.
> Dim strErrMsg As String 'Responses and error messages.
>
> 'Initalize.
> Set db = CurrentDb()
> Set tdf = db.TableDefs(strTableName)
>
> 'Set the table's SubdatasheetName.
> Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
> strErrMsg)
>
> For Each fld In tdf.Fields
> 'Handle the defaults for the different field types.
> Select Case fld.Type
> Case dbText, dbMemo 'Includes hyperlinks.
> fld.AllowZeroLength = False
> Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean, _
> True, strErrMsg)
> Case dbCurrency
> fld.DefaultValue = 0
> Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
> strErrMsg)
> Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
> fld.DefaultValue = vbNullString
> Case dbBoolean
> Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
> CInt(acCheckBox))
> End Select
>
> 'Set a caption if needed.
> strCaption = ConvertMixedCase(fld.Name)
> If strCaption <> fld.Name Then
> Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
> End If
> Next
>
> 'Clean up.
> Set fld = Nothing
> Set tdf = Nothing
> Set db = Nothing
> If Len(strErrMsg) > 0 Then
> Debug.Print strErrMsg
> Else
> Debug.Print "Properties set for table " & strTableName
> End If
> End Sub
>
> Function SetPropertyDAO(obj As Object, strPropertyName As String, _
> intType As Integer, varValue As Variant, Optional strErrMsg As String)
> As Boolean
> On Error GoTo ErrHandler
> 'Purpose: Set a property for an object, creating if necessary.
> 'Arguments: obj = the object whose property should be set.
> ' strPropertyName = the name of the property to set.
> ' intType = the type of property (needed for creating)
> ' varValue = the value to set this property to.
> ' strErrMsg = string to append any error message to.
>
> If HasProperty(obj, strPropertyName) Then
> obj.Properties(strPropertyName) = varValue
> Else
> obj.Properties.Append obj.CreateProperty(strPropertyName,
> intType, varValue)
> End If
> SetPropertyDAO = True
>
> ExitHandler:
> Exit Function
>
> ErrHandler:
> strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not
> set to " & varValue & ". Error " & Err.Number & " - " &
> Err.Description & vbCrLf
> Resume ExitHandler
> End Function
>
> Public Function HasProperty(obj As Object, strPropName As String) As
> Boolean
> 'Purpose: Return true if the object has the property.
> Dim varDummy As Variant
>
> On Error Resume Next
> varDummy = obj.Properties(strPropName)
> HasProperty = (Err.Number = 0)
> End Function
>
> Sub CreateIndexesDAO()
> Dim db As DAO.Database
> Dim tdf As DAO.TableDef
> Dim ind As DAO.Index
>
> 'Initialize
> Set db = CurrentDb()
> Set tdf = db.TableDefs("tblDaoContractor")
>
> '1. Primary key index.
> Set ind = tdf.CreateIndex("PrimaryKey")
> With ind
> .Fields.Append .CreateField("ContractorID")
> .Unique = False
> .Primary = True
> End With
> tdf.Indexes.Append ind
>
> '2. Single-field index.
> Set ind = tdf.CreateIndex("Inactive")
> ind.Fields.Append ind.CreateField("Inactive")
> tdf.Indexes.Append ind
>
> '3. Multi-field index.
> Set ind = tdf.CreateIndex("FullName")
> With ind
> .Fields.Append .CreateField("Surname")
> .Fields.Append .CreateField("FirstName")
> End With
> tdf.Indexes.Append ind
>
> 'Refresh the display of this collection.
> tdf.Indexes.Refresh
>
> 'Clean up
> Set ind = Nothing
> Set tdf = Nothing
> Set db = Nothing
> Debug.Print "tblDaoContractor indexes created."
> End Sub
> ----------------------code ends-----------------
>
> "Dixie" <di***@dogmail.com> wrote in message
> news:43********@duster.adelaide.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
Great, I have just worked out how to programmatically 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" <di***@dogmail.com> wrote in message
news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@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" <di***@dogmail.com> wrote in message
news:43******@duster.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("Faculty") .Fields("StartDate"),"Format",
dbText, "dd/mm/yyyy")
Is there something obviously wrong?
dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
The idea is:
SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty") .Fields("StartDate"),_
"Format", dbText, "dd/mm/yyyy")
Or you might use "General Date" in place of "dd/mm/yyyy", which will
use the format that the user has defined in the Windows Control Panel,
under Regional Options.
BTW, if you are having difficulties with the Australian date format in
Access, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
It deals with the 3 cases that often trip people up.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dixie" <di***@dogmail.com> wrote in message
news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
> news:43***********************@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 SubDatasheetName to [None].
>> - setting AllowZeroLength to No for all text fields, memos, and
>> hyperlinks.
>> - removing that darn zero as Default Value for numeric fields.
>> - setting the Format property for Currency types (and illustrating
>> how to set the Default Value if you wish.)
>> - setting Yes/No fields to display as a check box.
>> - setting a Caption with spaces on fields that have a mixed-case name
>> (e.g. OrderDate.)
>> - setting a Description of each field
>>
>> Finally, the last example shows how to create a primary key index, a
>> single-field index, and a multi-field index.
>>
>> Paste the code into a module, and see how it works.
>> ----------------------code starts-----------------
>> Sub StandardProperties(strTableName As String)
>> 'Purpose: Properties you always want set by default:
>> ' TableDef: Subdatasheets off.
>> ' Numeric fields: Remove Default Value.
>> ' Currency fields: Format as currency.
>> ' Yes/No fields: Display as check box. Default to No.
>> ' Text/memo/hyperlink: AllowZeroLength off,
>> ' UnicodeCompression on.
>> ' All fields: Add a caption if mixed case.
>> 'Argument: Name of the table.
>> 'Note: Requires: SetPropertyDAO()
>> Dim db As DAO.Database 'Current database.
>> Dim tdf As DAO.TableDef 'Table nominated in argument.
>> Dim fld As DAO.Field 'Each field.
>> Dim strCaption As String 'Field caption.
>> Dim strErrMsg As String 'Responses and error messages.
>>
>> 'Initalize.
>> Set db = CurrentDb()
>> Set tdf = db.TableDefs(strTableName)
>>
>> 'Set the table's SubdatasheetName.
>> Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
>> strErrMsg)
>>
>> For Each fld In tdf.Fields
>> 'Handle the defaults for the different field types.
>> Select Case fld.Type
>> Case dbText, dbMemo 'Includes hyperlinks.
>> fld.AllowZeroLength = False
>> Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean,
>> _
>> True, strErrMsg)
>> Case dbCurrency
>> fld.DefaultValue = 0
>> Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
>> strErrMsg)
>> Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
>> fld.DefaultValue = vbNullString
>> Case dbBoolean
>> Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
>> CInt(acCheckBox))
>> End Select
>>
>> 'Set a caption if needed.
>> strCaption = ConvertMixedCase(fld.Name)
>> If strCaption <> fld.Name Then
>> Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
>> End If
>> Next
>>
>> 'Clean up.
>> Set fld = Nothing
>> Set tdf = Nothing
>> Set db = Nothing
>> If Len(strErrMsg) > 0 Then
>> Debug.Print strErrMsg
>> Else
>> Debug.Print "Properties set for table " & strTableName
>> End If
>> End Sub
>>
>> Function SetPropertyDAO(obj As Object, strPropertyName As String, _
>> intType As Integer, varValue As Variant, Optional strErrMsg As
>> String) As Boolean
>> On Error GoTo ErrHandler
>> 'Purpose: Set a property for an object, creating if necessary.
>> 'Arguments: obj = the object whose property should be set.
>> ' strPropertyName = the name of the property to set.
>> ' intType = the type of property (needed for creating)
>> ' varValue = the value to set this property to.
>> ' strErrMsg = string to append any error message to.
>>
>> If HasProperty(obj, strPropertyName) Then
>> obj.Properties(strPropertyName) = varValue
>> Else
>> obj.Properties.Append obj.CreateProperty(strPropertyName,
>> intType, varValue)
>> End If
>> SetPropertyDAO = True
>>
>> ExitHandler:
>> Exit Function
>>
>> ErrHandler:
>> strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not
>> set to " & varValue & ". Error " & Err.Number & " - " &
>> Err.Description & vbCrLf
>> Resume ExitHandler
>> End Function
>>
>> Public Function HasProperty(obj As Object, strPropName As String) As
>> Boolean
>> 'Purpose: Return true if the object has the property.
>> Dim varDummy As Variant
>>
>> On Error Resume Next
>> varDummy = obj.Properties(strPropName)
>> HasProperty = (Err.Number = 0)
>> End Function
>>
>> Sub CreateIndexesDAO()
>> Dim db As DAO.Database
>> Dim tdf As DAO.TableDef
>> Dim ind As DAO.Index
>>
>> 'Initialize
>> Set db = CurrentDb()
>> Set tdf = db.TableDefs("tblDaoContractor")
>>
>> '1. Primary key index.
>> Set ind = tdf.CreateIndex("PrimaryKey")
>> With ind
>> .Fields.Append .CreateField("ContractorID")
>> .Unique = False
>> .Primary = True
>> End With
>> tdf.Indexes.Append ind
>>
>> '2. Single-field index.
>> Set ind = tdf.CreateIndex("Inactive")
>> ind.Fields.Append ind.CreateField("Inactive")
>> tdf.Indexes.Append ind
>>
>> '3. Multi-field index.
>> Set ind = tdf.CreateIndex("FullName")
>> With ind
>> .Fields.Append .CreateField("Surname")
>> .Fields.Append .CreateField("FirstName")
>> End With
>> tdf.Indexes.Append ind
>>
>> 'Refresh the display of this collection.
>> tdf.Indexes.Refresh
>>
>> 'Clean up
>> Set ind = Nothing
>> Set tdf = Nothing
>> Set db = Nothing
>> Debug.Print "tblDaoContractor indexes created."
>> End Sub
>> ----------------------code ends-----------------
>>
>> "Dixie" <di***@dogmail.com> wrote in message
>> news:43********@duster.adelaide.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
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" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net... Great, I have just worked out how to programmatically 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" <di***@dogmail.com> wrote in message
news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@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" <di***@dogmail.com> wrote in message
news:43******@duster.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("Faculty") .Fields("StartDate"),"Format",
dbText, "dd/mm/yyyy")
Is there something obviously wrong?
dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
> The idea is:
> SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty") .Fields("StartDate"),_
> "Format", dbText, "dd/mm/yyyy")
>
> Or you might use "General Date" in place of "dd/mm/yyyy", which will
> use the format that the user has defined in the Windows Control Panel,
> under Regional Options.
>
> BTW, if you are having difficulties with the Australian date format in
> Access, see:
> International Date Formats in Access
> at:
> http://allenbrowne.com/ser-36.html
> It deals with the 3 cases that often trip people up.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dixie" <di***@dogmail.com> wrote in message
> news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
>> news:43***********************@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 SubDatasheetName to [None].
>>> - setting AllowZeroLength to No for all text fields, memos, and
>>> hyperlinks.
>>> - removing that darn zero as Default Value for numeric fields.
>>> - setting the Format property for Currency types (and illustrating
>>> how to set the Default Value if you wish.)
>>> - setting Yes/No fields to display as a check box.
>>> - setting a Caption with spaces on fields that have a mixed-case
>>> name (e.g. OrderDate.)
>>> - setting a Description of each field
>>>
>>> Finally, the last example shows how to create a primary key index, a
>>> single-field index, and a multi-field index.
>>>
>>> Paste the code into a module, and see how it works.
>>> ----------------------code starts-----------------
>>> Sub StandardProperties(strTableName As String)
>>> 'Purpose: Properties you always want set by default:
>>> ' TableDef: Subdatasheets off.
>>> ' Numeric fields: Remove Default Value.
>>> ' Currency fields: Format as currency.
>>> ' Yes/No fields: Display as check box. Default to No.
>>> ' Text/memo/hyperlink: AllowZeroLength off,
>>> ' UnicodeCompression on.
>>> ' All fields: Add a caption if mixed case.
>>> 'Argument: Name of the table.
>>> 'Note: Requires: SetPropertyDAO()
>>> Dim db As DAO.Database 'Current database.
>>> Dim tdf As DAO.TableDef 'Table nominated in argument.
>>> Dim fld As DAO.Field 'Each field.
>>> Dim strCaption As String 'Field caption.
>>> Dim strErrMsg As String 'Responses and error messages.
>>>
>>> 'Initalize.
>>> Set db = CurrentDb()
>>> Set tdf = db.TableDefs(strTableName)
>>>
>>> 'Set the table's SubdatasheetName.
>>> Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
>>> strErrMsg)
>>>
>>> For Each fld In tdf.Fields
>>> 'Handle the defaults for the different field types.
>>> Select Case fld.Type
>>> Case dbText, dbMemo 'Includes hyperlinks.
>>> fld.AllowZeroLength = False
>>> Call SetPropertyDAO(fld, "UnicodeCompression", dbBoolean,
>>> _
>>> True, strErrMsg)
>>> Case dbCurrency
>>> fld.DefaultValue = 0
>>> Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
>>> strErrMsg)
>>> Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
>>> fld.DefaultValue = vbNullString
>>> Case dbBoolean
>>> Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
>>> CInt(acCheckBox))
>>> End Select
>>>
>>> 'Set a caption if needed.
>>> strCaption = ConvertMixedCase(fld.Name)
>>> If strCaption <> fld.Name Then
>>> Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
>>> End If
>>> Next
>>>
>>> 'Clean up.
>>> Set fld = Nothing
>>> Set tdf = Nothing
>>> Set db = Nothing
>>> If Len(strErrMsg) > 0 Then
>>> Debug.Print strErrMsg
>>> Else
>>> Debug.Print "Properties set for table " & strTableName
>>> End If
>>> End Sub
>>>
>>> Function SetPropertyDAO(obj As Object, strPropertyName As String, _
>>> intType As Integer, varValue As Variant, Optional strErrMsg As
>>> String) As Boolean
>>> On Error GoTo ErrHandler
>>> 'Purpose: Set a property for an object, creating if necessary.
>>> 'Arguments: obj = the object whose property should be set.
>>> ' strPropertyName = the name of the property to set.
>>> ' intType = the type of property (needed for creating)
>>> ' varValue = the value to set this property to.
>>> ' strErrMsg = string to append any error message to.
>>>
>>> If HasProperty(obj, strPropertyName) Then
>>> obj.Properties(strPropertyName) = varValue
>>> Else
>>> obj.Properties.Append obj.CreateProperty(strPropertyName,
>>> intType, varValue)
>>> End If
>>> SetPropertyDAO = True
>>>
>>> ExitHandler:
>>> Exit Function
>>>
>>> ErrHandler:
>>> strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not
>>> set to " & varValue & ". Error " & Err.Number & " - " &
>>> Err.Description & vbCrLf
>>> Resume ExitHandler
>>> End Function
>>>
>>> Public Function HasProperty(obj As Object, strPropName As String) As
>>> Boolean
>>> 'Purpose: Return true if the object has the property.
>>> Dim varDummy As Variant
>>>
>>> On Error Resume Next
>>> varDummy = obj.Properties(strPropName)
>>> HasProperty = (Err.Number = 0)
>>> End Function
>>>
>>> Sub CreateIndexesDAO()
>>> Dim db As DAO.Database
>>> Dim tdf As DAO.TableDef
>>> Dim ind As DAO.Index
>>>
>>> 'Initialize
>>> Set db = CurrentDb()
>>> Set tdf = db.TableDefs("tblDaoContractor")
>>>
>>> '1. Primary key index.
>>> Set ind = tdf.CreateIndex("PrimaryKey")
>>> With ind
>>> .Fields.Append .CreateField("ContractorID")
>>> .Unique = False
>>> .Primary = True
>>> End With
>>> tdf.Indexes.Append ind
>>>
>>> '2. Single-field index.
>>> Set ind = tdf.CreateIndex("Inactive")
>>> ind.Fields.Append ind.CreateField("Inactive")
>>> tdf.Indexes.Append ind
>>>
>>> '3. Multi-field index.
>>> Set ind = tdf.CreateIndex("FullName")
>>> With ind
>>> .Fields.Append .CreateField("Surname")
>>> .Fields.Append .CreateField("FirstName")
>>> End With
>>> tdf.Indexes.Append ind
>>>
>>> 'Refresh the display of this collection.
>>> tdf.Indexes.Refresh
>>>
>>> 'Clean up
>>> Set ind = Nothing
>>> Set tdf = Nothing
>>> Set db = Nothing
>>> Debug.Print "tblDaoContractor indexes created."
>>> End Sub
>>> ----------------------code ends-----------------
>>>
>>> "Dixie" <di***@dogmail.com> wrote in message
>>> news:43********@duster.adelaide.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
>
>
"Albert D. Kallal" <ka****@msn.com> wrote in
news:w4uTe.418584$s54.174586@pd7tw2no: I should point out that modifying the format in a table does NOT
change the format in your existing application.
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
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.Append ind.CreateField("fldAdditional")
tdf.Indexes.Append 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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au... 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" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net... Great, I have just worked out how to programmatically 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" <di***@dogmail.com> wrote in message
news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@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" <di***@dogmail.com> wrote in message
news:43******@duster.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("Faculty") .Fields("StartDate"),"Format",
> dbText, "dd/mm/yyyy")
>
> Is there something obviously wrong?
>
> dixie
>
> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
> news:43***********************@per-qv1-newsreader-01.iinet.net.au...
>> The idea is:
>> SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty") .Fields("StartDate"),_
>> "Format", dbText, "dd/mm/yyyy")
>>
>> Or you might use "General Date" in place of "dd/mm/yyyy", which will
>> use the format that the user has defined in the Windows Control
>> Panel, under Regional Options.
>>
>> BTW, if you are having difficulties with the Australian date format
>> in Access, see:
>> International Date Formats in Access
>> at:
>> http://allenbrowne.com/ser-36.html
>> It deals with the 3 cases that often trip people up.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Dixie" <di***@dogmail.com> wrote in message
>> news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
>>> news:43***********************@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 SubDatasheetName to [None].
>>>> - setting AllowZeroLength to No for all text fields, memos, and
>>>> hyperlinks.
>>>> - removing that darn zero as Default Value for numeric fields.
>>>> - setting the Format property for Currency types (and illustrating
>>>> how to set the Default Value if you wish.)
>>>> - setting Yes/No fields to display as a check box.
>>>> - setting a Caption with spaces on fields that have a mixed-case
>>>> name (e.g. OrderDate.)
>>>> - setting a Description of each field
>>>>
>>>> Finally, the last example shows how to create a primary key index,
>>>> a single-field index, and a multi-field index.
>>>>
>>>> Paste the code into a module, and see how it works.
>>>> ----------------------code starts-----------------
>>>> Sub StandardProperties(strTableName As String)
>>>> 'Purpose: Properties you always want set by default:
>>>> ' TableDef: Subdatasheets off.
>>>> ' Numeric fields: Remove Default Value.
>>>> ' Currency fields: Format as currency.
>>>> ' Yes/No fields: Display as check box. Default to
>>>> No.
>>>> ' Text/memo/hyperlink: AllowZeroLength off,
>>>> ' UnicodeCompression on.
>>>> ' All fields: Add a caption if mixed case.
>>>> 'Argument: Name of the table.
>>>> 'Note: Requires: SetPropertyDAO()
>>>> Dim db As DAO.Database 'Current database.
>>>> Dim tdf As DAO.TableDef 'Table nominated in argument.
>>>> Dim fld As DAO.Field 'Each field.
>>>> Dim strCaption As String 'Field caption.
>>>> Dim strErrMsg As String 'Responses and error messages.
>>>>
>>>> 'Initalize.
>>>> Set db = CurrentDb()
>>>> Set tdf = db.TableDefs(strTableName)
>>>>
>>>> 'Set the table's SubdatasheetName.
>>>> Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]", _
>>>> strErrMsg)
>>>>
>>>> For Each fld In tdf.Fields
>>>> 'Handle the defaults for the different field types.
>>>> Select Case fld.Type
>>>> Case dbText, dbMemo 'Includes hyperlinks.
>>>> fld.AllowZeroLength = False
>>>> Call SetPropertyDAO(fld, "UnicodeCompression",
>>>> dbBoolean, _
>>>> True, strErrMsg)
>>>> Case dbCurrency
>>>> fld.DefaultValue = 0
>>>> Call SetPropertyDAO(fld, "Format", dbText, "Currency", _
>>>> strErrMsg)
>>>> Case dbLong, dbInteger, dbByte, dbDouble, dbSingle,
>>>> dbDecimal
>>>> fld.DefaultValue = vbNullString
>>>> Case dbBoolean
>>>> Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
>>>> CInt(acCheckBox))
>>>> End Select
>>>>
>>>> 'Set a caption if needed.
>>>> strCaption = ConvertMixedCase(fld.Name)
>>>> If strCaption <> fld.Name Then
>>>> Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
>>>> End If
>>>> Next
>>>>
>>>> 'Clean up.
>>>> Set fld = Nothing
>>>> Set tdf = Nothing
>>>> Set db = Nothing
>>>> If Len(strErrMsg) > 0 Then
>>>> Debug.Print strErrMsg
>>>> Else
>>>> Debug.Print "Properties set for table " & strTableName
>>>> End If
>>>> End Sub
>>>>
>>>> Function SetPropertyDAO(obj As Object, strPropertyName As String, _
>>>> intType As Integer, varValue As Variant, Optional strErrMsg As
>>>> String) As Boolean
>>>> On Error GoTo ErrHandler
>>>> 'Purpose: Set a property for an object, creating if necessary.
>>>> 'Arguments: obj = the object whose property should be set.
>>>> ' strPropertyName = the name of the property to set.
>>>> ' intType = the type of property (needed for creating)
>>>> ' varValue = the value to set this property to.
>>>> ' strErrMsg = string to append any error message to.
>>>>
>>>> If HasProperty(obj, strPropertyName) Then
>>>> obj.Properties(strPropertyName) = varValue
>>>> Else
>>>> obj.Properties.Append obj.CreateProperty(strPropertyName,
>>>> intType, varValue)
>>>> End If
>>>> SetPropertyDAO = True
>>>>
>>>> ExitHandler:
>>>> Exit Function
>>>>
>>>> ErrHandler:
>>>> strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not
>>>> set to " & varValue & ". Error " & Err.Number & " - " &
>>>> Err.Description & vbCrLf
>>>> Resume ExitHandler
>>>> End Function
>>>>
>>>> Public Function HasProperty(obj As Object, strPropName As String)
>>>> As Boolean
>>>> 'Purpose: Return true if the object has the property.
>>>> Dim varDummy As Variant
>>>>
>>>> On Error Resume Next
>>>> varDummy = obj.Properties(strPropName)
>>>> HasProperty = (Err.Number = 0)
>>>> End Function
>>>>
>>>> Sub CreateIndexesDAO()
>>>> Dim db As DAO.Database
>>>> Dim tdf As DAO.TableDef
>>>> Dim ind As DAO.Index
>>>>
>>>> 'Initialize
>>>> Set db = CurrentDb()
>>>> Set tdf = db.TableDefs("tblDaoContractor")
>>>>
>>>> '1. Primary key index.
>>>> Set ind = tdf.CreateIndex("PrimaryKey")
>>>> With ind
>>>> .Fields.Append .CreateField("ContractorID")
>>>> .Unique = False
>>>> .Primary = True
>>>> End With
>>>> tdf.Indexes.Append ind
>>>>
>>>> '2. Single-field index.
>>>> Set ind = tdf.CreateIndex("Inactive")
>>>> ind.Fields.Append ind.CreateField("Inactive")
>>>> tdf.Indexes.Append ind
>>>>
>>>> '3. Multi-field index.
>>>> Set ind = tdf.CreateIndex("FullName")
>>>> With ind
>>>> .Fields.Append .CreateField("Surname")
>>>> .Fields.Append .CreateField("FirstName")
>>>> End With
>>>> tdf.Indexes.Append ind
>>>>
>>>> 'Refresh the display of this collection.
>>>> tdf.Indexes.Refresh
>>>>
>>>> 'Clean up
>>>> Set ind = Nothing
>>>> Set tdf = Nothing
>>>> Set db = Nothing
>>>> Debug.Print "tblDaoContractor indexes created."
>>>> End Sub
>>>> ----------------------code ends-----------------
>>>>
>>>> "Dixie" <di***@dogmail.com> wrote in message
>>>> news:43********@duster.adelaide.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
>>
>>
>
>
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 programmatically is probabably greater than the number shown in the
Indexes box in table design view.
You can delete an index programmatically by using the Delete method on the
Indexes collection of the TableDef:
dbEngine(0)(0).TableDefs("MyTable").Indexes.Delete "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" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net... 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.Append ind.CreateField("fldAdditional")
tdf.Indexes.Append 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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au... 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" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net... Great, I have just worked out how to programmatically 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" <di***@dogmail.com> wrote in message
news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@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" <di***@dogmail.com> wrote in message
> news:43******@duster.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("Faculty") .Fields("StartDate"),"Format",
>> dbText, "dd/mm/yyyy")
>>
>> Is there something obviously wrong?
>>
>> dixie
>>
>> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
>> news:43***********************@per-qv1-newsreader-01.iinet.net.au...
>>> The idea is:
>>> SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty") .Fields("StartDate"),_
>>> "Format", dbText, "dd/mm/yyyy")
>>>
>>> Or you might use "General Date" in place of "dd/mm/yyyy", which will
>>> use the format that the user has defined in the Windows Control
>>> Panel, under Regional Options.
>>>
>>> BTW, if you are having difficulties with the Australian date format
>>> in Access, see:
>>> International Date Formats in Access
>>> at:
>>> http://allenbrowne.com/ser-36.html
>>> It deals with the 3 cases that often trip people up.
>>>
>>> "Dixie" <di***@dogmail.com> wrote in message
>>> news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
>>>> news:43***********************@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 SubDatasheetName to [None].
>>>>> - setting AllowZeroLength to No for all text fields, memos, and
>>>>> hyperlinks.
>>>>> - removing that darn zero as Default Value for numeric fields.
>>>>> - setting the Format property for Currency types (and illustrating
>>>>> how to set the Default Value if you wish.)
>>>>> - setting Yes/No fields to display as a check box.
>>>>> - setting a Caption with spaces on fields that have a mixed-case
>>>>> name (e.g. OrderDate.)
>>>>> - setting a Description of each field
>>>>>
>>>>> Finally, the last example shows how to create a primary key index,
>>>>> a single-field index, and a multi-field index.
>>>>>
>>>>> Paste the code into a module, and see how it works.
>>>>> ----------------------code starts-----------------
>>>>> Sub StandardProperties(strTableName As String)
>>>>> 'Purpose: Properties you always want set by default:
>>>>> ' TableDef: Subdatasheets off.
>>>>> ' Numeric fields: Remove Default Value.
>>>>> ' Currency fields: Format as currency.
>>>>> ' Yes/No fields: Display as check box. Default to
>>>>> No.
>>>>> ' Text/memo/hyperlink: AllowZeroLength off,
>>>>> ' UnicodeCompression on.
>>>>> ' All fields: Add a caption if mixed case.
>>>>> 'Argument: Name of the table.
>>>>> 'Note: Requires: SetPropertyDAO()
>>>>> Dim db As DAO.Database 'Current database.
>>>>> Dim tdf As DAO.TableDef 'Table nominated in argument.
>>>>> Dim fld As DAO.Field 'Each field.
>>>>> Dim strCaption As String 'Field caption.
>>>>> Dim strErrMsg As String 'Responses and error messages.
>>>>>
>>>>> 'Initalize.
>>>>> Set db = CurrentDb()
>>>>> Set tdf = db.TableDefs(strTableName)
>>>>>
>>>>> 'Set the table's SubdatasheetName.
>>>>> Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]",
>>>>> _
>>>>> strErrMsg)
>>>>>
>>>>> For Each fld In tdf.Fields
>>>>> 'Handle the defaults for the different field types.
>>>>> Select Case fld.Type
>>>>> Case dbText, dbMemo 'Includes hyperlinks.
>>>>> fld.AllowZeroLength = False
>>>>> Call SetPropertyDAO(fld, "UnicodeCompression",
>>>>> dbBoolean, _
>>>>> True, strErrMsg)
>>>>> Case dbCurrency
>>>>> fld.DefaultValue = 0
>>>>> Call SetPropertyDAO(fld, "Format", dbText, "Currency",
>>>>> _
>>>>> strErrMsg)
>>>>> Case dbLong, dbInteger, dbByte, dbDouble, dbSingle,
>>>>> dbDecimal
>>>>> fld.DefaultValue = vbNullString
>>>>> Case dbBoolean
>>>>> Call SetPropertyDAO(fld, "DisplayControl", dbInteger, _
>>>>> CInt(acCheckBox))
>>>>> End Select
>>>>>
>>>>> 'Set a caption if needed.
>>>>> strCaption = ConvertMixedCase(fld.Name)
>>>>> If strCaption <> fld.Name Then
>>>>> Call SetPropertyDAO(fld, "Caption", dbText, strCaption)
>>>>> End If
>>>>> Next
>>>>>
>>>>> 'Clean up.
>>>>> Set fld = Nothing
>>>>> Set tdf = Nothing
>>>>> Set db = Nothing
>>>>> If Len(strErrMsg) > 0 Then
>>>>> Debug.Print strErrMsg
>>>>> Else
>>>>> Debug.Print "Properties set for table " & strTableName
>>>>> End If
>>>>> End Sub
>>>>>
>>>>> Function SetPropertyDAO(obj As Object, strPropertyName As String,
>>>>> _
>>>>> intType As Integer, varValue As Variant, Optional strErrMsg As
>>>>> String) As Boolean
>>>>> On Error GoTo ErrHandler
>>>>> 'Purpose: Set a property for an object, creating if
>>>>> necessary.
>>>>> 'Arguments: obj = the object whose property should be set.
>>>>> ' strPropertyName = the name of the property to set.
>>>>> ' intType = the type of property (needed for
>>>>> creating)
>>>>> ' varValue = the value to set this property to.
>>>>> ' strErrMsg = string to append any error message to.
>>>>>
>>>>> If HasProperty(obj, strPropertyName) Then
>>>>> obj.Properties(strPropertyName) = varValue
>>>>> Else
>>>>> obj.Properties.Append obj.CreateProperty(strPropertyName,
>>>>> intType, varValue)
>>>>> End If
>>>>> SetPropertyDAO = True
>>>>>
>>>>> ExitHandler:
>>>>> Exit Function
>>>>>
>>>>> ErrHandler:
>>>>> strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & "
>>>>> not set to " & varValue & ". Error " & Err.Number & " - " &
>>>>> Err.Description & vbCrLf
>>>>> Resume ExitHandler
>>>>> End Function
>>>>>
>>>>> Public Function HasProperty(obj As Object, strPropName As String)
>>>>> As Boolean
>>>>> 'Purpose: Return true if the object has the property.
>>>>> Dim varDummy As Variant
>>>>>
>>>>> On Error Resume Next
>>>>> varDummy = obj.Properties(strPropName)
>>>>> HasProperty = (Err.Number = 0)
>>>>> End Function
>>>>>
>>>>> Sub CreateIndexesDAO()
>>>>> Dim db As DAO.Database
>>>>> Dim tdf As DAO.TableDef
>>>>> Dim ind As DAO.Index
>>>>>
>>>>> 'Initialize
>>>>> Set db = CurrentDb()
>>>>> Set tdf = db.TableDefs("tblDaoContractor")
>>>>>
>>>>> '1. Primary key index.
>>>>> Set ind = tdf.CreateIndex("PrimaryKey")
>>>>> With ind
>>>>> .Fields.Append .CreateField("ContractorID")
>>>>> .Unique = False
>>>>> .Primary = True
>>>>> End With
>>>>> tdf.Indexes.Append ind
>>>>>
>>>>> '2. Single-field index.
>>>>> Set ind = tdf.CreateIndex("Inactive")
>>>>> ind.Fields.Append ind.CreateField("Inactive")
>>>>> tdf.Indexes.Append ind
>>>>>
>>>>> '3. Multi-field index.
>>>>> Set ind = tdf.CreateIndex("FullName")
>>>>> With ind
>>>>> .Fields.Append .CreateField("Surname")
>>>>> .Fields.Append .CreateField("FirstName")
>>>>> End With
>>>>> tdf.Indexes.Append ind
>>>>>
>>>>> 'Refresh the display of this collection.
>>>>> tdf.Indexes.Refresh
>>>>>
>>>>> 'Clean up
>>>>> Set ind = Nothing
>>>>> Set tdf = Nothing
>>>>> Set db = Nothing
>>>>> Debug.Print "tblDaoContractor indexes created."
>>>>> End Sub
>>>>> ----------------------code ends-----------------
>>>>>
>>>>> "Dixie" <di***@dogmail.com> wrote in message
>>>>> news:43********@duster.adelaide.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?
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("tblFaculty").Indexes.Del ete "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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au... 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 programmatically is probabably greater than the
number shown in the Indexes box in table design view.
You can delete an index programmatically by using the Delete method on the
Indexes collection of the TableDef:
dbEngine(0)(0).TableDefs("MyTable").Indexes.Delete "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" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net... 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.Append ind.CreateField("fldAdditional")
tdf.Indexes.Append 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" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au... 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" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...
Great, I have just worked out how to programmatically 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" <di***@dogmail.com> wrote in message
news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
> news:43***********************@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" <di***@dogmail.com> wrote in message
>> news:43******@duster.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("Faculty") .Fields("StartDate"),"Format",
>>> dbText, "dd/mm/yyyy")
>>>
>>> Is there something obviously wrong?
>>>
>>> dixie
>>>
>>> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
>>> news:43***********************@per-qv1-newsreader-01.iinet.net.au...
>>>> The idea is:
>>>> SetPropertyDAO(dbEngine(0)(0).TableDefs("Faculty") .Fields("StartDate"),_
>>>> "Format", dbText, "dd/mm/yyyy")
>>>>
>>>> Or you might use "General Date" in place of "dd/mm/yyyy", which
>>>> will use the format that the user has defined in the Windows
>>>> Control Panel, under Regional Options.
>>>>
>>>> BTW, if you are having difficulties with the Australian date format
>>>> in Access, see:
>>>> International Date Formats in Access
>>>> at:
>>>> http://allenbrowne.com/ser-36.html
>>>> It deals with the 3 cases that often trip people up.
>>>>
>>>> "Dixie" <di***@dogmail.com> wrote in message
>>>> news:43******@duster.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" <Al*********@SeeSig.Invalid> wrote in message
>>>>> news:43***********************@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 SubDatasheetName to [None].
>>>>>> - setting AllowZeroLength to No for all text fields, memos, and
>>>>>> hyperlinks.
>>>>>> - removing that darn zero as Default Value for numeric fields.
>>>>>> - setting the Format property for Currency types (and
>>>>>> illustrating how to set the Default Value if you wish.)
>>>>>> - setting Yes/No fields to display as a check box.
>>>>>> - setting a Caption with spaces on fields that have a mixed-case
>>>>>> name (e.g. OrderDate.)
>>>>>> - setting a Description of each field
>>>>>>
>>>>>> Finally, the last example shows how to create a primary key
>>>>>> index, a single-field index, and a multi-field index.
>>>>>>
>>>>>> Paste the code into a module, and see how it works.
>>>>>> ----------------------code starts-----------------
>>>>>> Sub StandardProperties(strTableName As String)
>>>>>> 'Purpose: Properties you always want set by default:
>>>>>> ' TableDef: Subdatasheets off.
>>>>>> ' Numeric fields: Remove Default Value.
>>>>>> ' Currency fields: Format as currency.
>>>>>> ' Yes/No fields: Display as check box. Default to
>>>>>> No.
>>>>>> ' Text/memo/hyperlink: AllowZeroLength off,
>>>>>> ' UnicodeCompression on.
>>>>>> ' All fields: Add a caption if mixed case.
>>>>>> 'Argument: Name of the table.
>>>>>> 'Note: Requires: SetPropertyDAO()
>>>>>> Dim db As DAO.Database 'Current database.
>>>>>> Dim tdf As DAO.TableDef 'Table nominated in argument.
>>>>>> Dim fld As DAO.Field 'Each field.
>>>>>> Dim strCaption As String 'Field caption.
>>>>>> Dim strErrMsg As String 'Responses and error messages.
>>>>>>
>>>>>> 'Initalize.
>>>>>> Set db = CurrentDb()
>>>>>> Set tdf = db.TableDefs(strTableName)
>>>>>>
>>>>>> 'Set the table's SubdatasheetName.
>>>>>> Call SetPropertyDAO(tdf, "SubdatasheetName", dbText, "[None]",
>>>>>> _
>>>>>> strErrMsg)
>>>>>>
>>>>>> For Each fld In tdf.Fields
>>>>>> 'Handle the defaults for the different field types.
>>>>>> Select Case fld.Type
>>>>>> Case dbText, dbMemo 'Includes hyperlinks.
>>>>>> fld.AllowZeroLength = False
>>>>>> Call SetPropertyDAO(fld, "UnicodeCompression",
>>>>>> dbBoolean, _
>>>>>> True, strErrMsg)
>>>>>> Case dbCurrency
>>>>>> fld.DefaultValue = 0
>>>>>> Call SetPropertyDAO(fld, "Format", dbText, "Currency",
>>>>>> _
>>>>>> strErrMsg)
>>>>>> Case dbLong, dbInteger, dbByte, dbDouble, dbSingle,
>>>>>> dbDecimal
>>>>>> fld.DefaultValue = vbNullString
>>>>>> Case dbBoolean
>>>>>> Call SetPropertyDAO(fld, "DisplayControl", dbInteger,
>>>>>> _
>>>>>> CInt(acCheckBox))
>>>>>> End Select
>>>>>>
>>>>>> 'Set a caption if needed.
>>>>>> strCaption = ConvertMixedCase(fld.Name)
>>>>>> If strCaption <> fld.Name Then
>>>>>> Call SetPropertyDAO(fld, "Caption", dbText,
>>>>>> strCaption)
>>>>>> End If
>>>>>> Next
>>>>>>
>>>>>> 'Clean up.
>>>>>> Set fld = Nothing
>>>>>> Set tdf = Nothing
>>>>>> Set db = Nothing
>>>>>> If Len(strErrMsg) > 0 Then
>>>>>> Debug.Print strErrMsg
>>>>>> Else
>>>>>> Debug.Print "Properties set for table " & strTableName
>>>>>> End If
>>>>>> End Sub
>>>>>>
>>>>>> Function SetPropertyDAO(obj As Object, strPropertyName As String,
>>>>>> _
>>>>>> intType As Integer, varValue As Variant, Optional strErrMsg As
>>>>>> String) As Boolean
>>>>>> On Error GoTo ErrHandler
>>>>>> 'Purpose: Set a property for an object, creating if
>>>>>> necessary.
>>>>>> 'Arguments: obj = the object whose property should be set.
>>>>>> ' strPropertyName = the name of the property to set.
>>>>>> ' intType = the type of property (needed for
>>>>>> creating)
>>>>>> ' varValue = the value to set this property to.
>>>>>> ' strErrMsg = string to append any error message to.
>>>>>>
>>>>>> If HasProperty(obj, strPropertyName) Then
>>>>>> obj.Properties(strPropertyName) = varValue
>>>>>> Else
>>>>>> obj.Properties.Append obj.CreateProperty(strPropertyName,
>>>>>> intType, varValue)
>>>>>> End If
>>>>>> SetPropertyDAO = True
>>>>>>
>>>>>> ExitHandler:
>>>>>> Exit Function
>>>>>>
>>>>>> ErrHandler:
>>>>>> strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & "
>>>>>> not set to " & varValue & ". Error " & Err.Number & " - " &
>>>>>> Err.Description & vbCrLf
>>>>>> Resume ExitHandler
>>>>>> End Function
>>>>>>
>>>>>> Public Function HasProperty(obj As Object, strPropName As String)
>>>>>> As Boolean
>>>>>> 'Purpose: Return true if the object has the property.
>>>>>> Dim varDummy As Variant
>>>>>>
>>>>>> On Error Resume Next
>>>>>> varDummy = obj.Properties(strPropName)
>>>>>> HasProperty = (Err.Number = 0)
>>>>>> End Function
>>>>>>
>>>>>> Sub CreateIndexesDAO()
>>>>>> Dim db As DAO.Database
>>>>>> Dim tdf As DAO.TableDef
>>>>>> Dim ind As DAO.Index
>>>>>>
>>>>>> 'Initialize
>>>>>> Set db = CurrentDb()
>>>>>> Set tdf = db.TableDefs("tblDaoContractor")
>>>>>>
>>>>>> '1. Primary key index.
>>>>>> Set ind = tdf.CreateIndex("PrimaryKey")
>>>>>> With ind
>>>>>> .Fields.Append .CreateField("ContractorID")
>>>>>> .Unique = False
>>>>>> .Primary = True
>>>>>> End With
>>>>>> tdf.Indexes.Append ind
>>>>>>
>>>>>> '2. Single-field index.
>>>>>> Set ind = tdf.CreateIndex("Inactive")
>>>>>> ind.Fields.Append ind.CreateField("Inactive")
>>>>>> tdf.Indexes.Append ind
>>>>>>
>>>>>> '3. Multi-field index.
>>>>>> Set ind = tdf.CreateIndex("FullName")
>>>>>> With ind
>>>>>> .Fields.Append .CreateField("Surname")
>>>>>> .Fields.Append .CreateField("FirstName")
>>>>>> End With
>>>>>> tdf.Indexes.Append ind
>>>>>>
>>>>>> 'Refresh the display of this collection.
>>>>>> tdf.Indexes.Refresh
>>>>>>
>>>>>> 'Clean up
>>>>>> Set ind = Nothing
>>>>>> Set tdf = Nothing
>>>>>> Set db = Nothing
>>>>>> Debug.Print "tblDaoContractor indexes created."
>>>>>> End Sub
>>>>>> ----------------------code ends-----------------
>>>>>>
>>>>>> "Dixie" <di***@dogmail.com> wrote in message
>>>>>> news:43********@duster.adelaide.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?
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" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net... 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("tblFaculty").Indexes.Del ete "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
Thanks once more Allen, the CurrentDB() worked. I'm only using Access 2000,
so will probably not be doing much reading on DDL.
dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au... 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" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...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("tblFaculty").Indexes.Del ete "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" <Al*********@SeeSig.Invalid> wrote in
news:43***********************@per-qv1-newsreader-01.iinet.net.au: 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.
But if you're using DBEngine, all you have to do is refresh the
collection that you're working with. I think it would be wise to do
that after adding indexes, etc., or, at the very least, before you
try to work with the ones you've just created.
On the other hand, I always use CurrentDB, but a cached version of
it, so that means I need to refresh its collections just like you
would with DBEngine. The collections get refreshed only on the
initial call to CurrentDB -- the collections don't stay perpetually
refreshed in, say, a db variable that was assigned using CurrentDB.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
I have merged all of the procedures together so I can do a big update. It
works well if all of the fields do not exist. I have run into one problem.
If I wish to say add a default value or change the default value of an
existing field, or set the AllowZeroLength property, the procedure breaks
down. I can see why, and that is because it was part of a procedure that
creates the field, then adds the properties. However, if the field exists,
the first line fails and it can't do the following ones.
Best if I give the example:
Set fldTemp = tdf.CreateField("MyField", dbText, 255)
fldTemp.AllowZeroLength = True
fldTemp.DefaultValue = "My Default"
fldTemp.Required = True
tdf.Fields.Append fldTemp
Obviously, if MyField already exists, fldTemp does not get set and the
following 3 lines fail.
How do I overcome this problem?
dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au... 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" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...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("tblFaculty").Indexes.Del ete "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
Sorry, have just worked that one out.
dixie
"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net... I have merged all of the procedures together so I can do a big update. It
works well if all of the fields do not exist. I have run into one problem.
If I wish to say add a default value or change the default value of an
existing field, or set the AllowZeroLength property, the procedure breaks
down. I can see why, and that is because it was part of a procedure that
creates the field, then adds the properties. However, if the field exists,
the first line fails and it can't do the following ones.
Best if I give the example:
Set fldTemp = tdf.CreateField("MyField", dbText, 255)
fldTemp.AllowZeroLength = True
fldTemp.DefaultValue = "My Default"
fldTemp.Required = True
tdf.Fields.Append fldTemp
Obviously, if MyField already exists, fldTemp does not get set and the
following 3 lines fail.
How do I overcome this problem?
dixie
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au... 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" <di***@dogmail.com> wrote in message
news:43******@duster.adelaide.on.net...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("tblFaculty").Indexes.Del ete "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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jason Butera |
last post by:
I know that I can read/write custom properties of an object by using
the following:
Setting:
document.all.customProp = "this";
Getting:
document.all.customProp;
Is there a way I can run...
|
by: John Hargrove |
last post by:
I'm having trouble setting decimal place values in the results field
of a sample management database I'm building for an environmental
testing laboratory. The degree of sensitivity varies among...
|
by: Larry |
last post by:
In Access each field has individual properties (font, size, color etc). Is
there a way around having to enter all these similar properties for each
field. This seems so redundant. Is there a...
|
by: Andrew Smith via AccessMonster.com |
last post by:
Okay here is my problem. I have a table that is imported by the user and
then I need to add a boolean field to the table with Format Yes/No and the
Display Control as Check box. Here is what I...
|
by: Danko Greiner |
last post by:
Thanx Willy, this was very helpful.
But i also need (and want to know) how to do this from code.
Can you plase give me right topic in MSDN? is there good example?
Thanx
p.s. this is...
|
by: Angel |
last post by:
Is there any way to set custom public properties that i created on the page via JavaScript. I would like to open a Web Dialog Window but while I open the aspx page in that dialog window I'd like to...
|
by: Donald Grove |
last post by:
Is it possible to retrieve field properties from a table in access2000
using code?
I have tried:
"
dim dbs as dao.database
dim tbl as dao.tabledef
dim fld as dao.field
dim prop as...
|
by: Hexman |
last post by:
I'm creating a new Access table using ADOX. I can add columns and
indexes, but I'm baffled on how to change field properties. Can
someone give me a hand?
Want to change properties such as:...
|
by: |
last post by:
I have made some user controls with custom properties. I can set those
properties on instances of my user controls, and I have programmed my user
control to do useful visual things in response to...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
