Property Format and DecimalPlaces

[TNL]

Hi,
I use DAO to change structure of a table:
I add a new field with type: single. Now I want to define the format and
DecimalPlaces property of this field to "fixed" and 2.
In Access (2000) I can access these property with number 24, 25:
?currentdb.TableDefs("Output").Fields("Tax1").Properties(24).name
?currentdb.TableDefs("Output").Fields("Tax1").Properties(24).value

But these properties don't exist (for a new field, that created with DAO,
I'm not sure).
I think, I must create these properties and add to collection Properties. My
code is following, but doesn't work, I don't know, why.

Set tdf = db.TableDefs("Output")
Set fld = tdf.CreateField("Tax1", DAO.DataTypeEnum.dbSingle)
fld.DefaultValue = 0

Dim prop As DAO.Property
Set prop = fld.CreateProperty("Format", , "Fixed")
fld.Properties.Append prop

tdf.Fields.Append fld


Why?
Thanks
TNL

 

[Marshall Barton]

I use DAO to change structure of a table:
I add a new field with type: single. Now I want to define the format and
DecimalPlaces property of this field to "fixed" and 2.
In Access (2000) I can access these property with number 24, 25:
?currentdb.TableDefs("Output").Fields("Tax1").Properties(24).name
?currentdb.TableDefs("Output").Fields("Tax1").Properties(24).value

But these properties don't exist (for a new field, that created with DAO,
I'm not sure).
I think, I must create these properties and add to collection Properties. My
code is following, but doesn't work, I don't know, why.

Set tdf = db.TableDefs("Output")
Set fld = tdf.CreateField("Tax1", DAO.DataTypeEnum.dbSingle)
fld.DefaultValue = 0

Dim prop As DAO.Property
Set prop = fld.CreateProperty("Format", , "Fixed")
fld.Properties.Append prop

tdf.Fields.Append fld

The Format and DecimalPlaces are not DAO built-in
properties, instead they are custom properties added by
Access when you use them in table design view. If you
create the field in code, then you also have to create these
properties.

I think the issue is that you should append the field before
crating the properties. Here's some example code:

Sub CreateField()
Dim dbCur As Database
Dim tdf As TableDef
Dim fld As Field
Dim prp As Property

Set dbCur = CurrentDb()
Set tdf = dbCur.TableDefs("ZZZ")
Set fld = tdf.CreateField("MyField", dbLong)
tdf.Fields.Append fld ' Must append field before
creating properties
Set prp = fld.CreateProperty("Caption", dbText, "Some
String")
fld.Properties.Append prp
fld.Properties.Refresh
tdf.Fields.Refresh
End Sub

 

[TNL]

thanks
TNL

The Format and DecimalPlaces are not DAO built-in
properties, instead they are custom properties added by
Access when you use them in table design view. If you
create the field in code, then you also have to create these
properties.

I think the issue is that you should append the field before
crating the properties. Here's some example code:

Sub CreateField()
Dim dbCur As Database
Dim tdf As TableDef
Dim fld As Field
Dim prp As Property

Set dbCur = CurrentDb()
Set tdf = dbCur.TableDefs("ZZZ")
Set fld = tdf.CreateField("MyField", dbLong)
tdf.Fields.Append fld ' Must append field before
creating properties
Set prp = fld.CreateProperty("Caption", dbText, "Some
String")
fld.Properties.Append prp
fld.Properties.Refresh
tdf.Fields.Refresh
End Sub