Setting field properties in code

[Dixie]

Can I set the Format property in a date/time field in code?

Can I set the Input Mask in a date/time field in code?

Can I set the Format of a Yes/No field to Checkbox in code?

I am working on a remote update of tables and fields and can't find enough
information on these things.

Also, how do you index a field in code?

TIA
dixie

[Allen Browne]

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:431e4763$1...@duster.adelaide.on.net...

[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" <Allen...@SeeSig.Invalid> wrote in message
news:431e4f7d$0$14481$5a62...@per-qv1-newsreader-01.iinet.net.au...

[Albert D. Kallal]

You got some answer here already.

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

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

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

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

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

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOO...@msn.com
http://www.members.shaw.ca/AlbertKallal

[Allen Browne]

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:431e...@duster.adelaide.on.net...

[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" <Allen...@SeeSig.Invalid> wrote in message

news:431e6d53$0$14487$5a62...@per-qv1-newsreader-01.iinet.net.au...

[Allen Browne]

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]

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" <Allen...@SeeSig.Invalid> wrote in message

news:431e99d1$0$14492$5a62...@per-qv1-newsreader-01.iinet.net.au...

[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

[Allen Browne]

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.)

[David W. Fenton]

"Albert D. Kallal" <kal...@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

[Dixie]

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" <Allen...@SeeSig.Invalid> wrote in message

news:431efbf1$0$14453$5a62...@per-qv1-newsreader-01.iinet.net.au...

[Allen Browne]

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:431f...@duster.adelaide.on.net...

[Dixie]

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.Delete "MyNewIndex"

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

What have I done wrong now? Sorry.

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

dixie

"Allen Browne" <Allen...@SeeSig.Invalid> wrote in message

news:431f930f$0$14493$5a62...@per-qv1-newsreader-01.iinet.net.au...

[Allen Browne]

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:431f...@duster.adelaide.on.net...

[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" <Allen...@SeeSig.Invalid> wrote in message

news:431fd30a$0$14457$5a62...@per-qv1-newsreader-01.iinet.net.au...

[David W. Fenton]

"Allen Browne" <Allen...@SeeSig.Invalid> wrote in

news:431fd30a$0$14457$5a62...@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.

[Dixie]

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" <Allen...@SeeSig.Invalid> wrote in message

news:431fd30a$0$14457$5a62...@per-qv1-newsreader-01.iinet.net.au...

[Dixie]

Sorry, have just worked that one out.

dixie

"Dixie" <di...@dogmail.com> wrote in message

news:4320b460$1...@duster.adelaide.on.net...