Use VBA to set Format Property of a Field

[rwboyden via AccessMonster.com]

I need to create a field using VBA which will display with 2 decimal places.
I can do it manually using the type "Currency" and the format property
"Fixed". I can create the field with the currency type using the VBA
CreateField function below. I can't seem to figure out the code needed to
set the format property to "Fixed". I understand that the property needs to
be created, but I'm missing something in the syntax. Any suggestions would be
appreciated.


' dbT and tdf have been declared

Dim fld As Field
Dim prop As Property

Set dbT = DBEngine.Workspaces(0).OpenDatabase(strDataPath)
Set tdf = dbT.TableDefs("3 Photo Log")

' Create a New Field
Set fld = tdf.CreateField("Issue Order", dbCurrency)

' Add the Format Property
Set prop = fld.CreateProperty("Format", dbCurrency, "Fixed")
fld.Properties.Append prop

' Append the field to the table
tdf.Fields.Append fld

 

[Douglas J. Steele]

The Format property is a String, not a Currency.

Try:

Set prop = fld.CreateProperty("Format", dbText, "Fixed")

 

[rwboyden via AccessMonster.com]

The Format property is a String, not a Currency.

Try:

Set prop = fld.CreateProperty("Format", dbText, "Fixed")

I need to create a field using VBA which will display with 2 decimal
places.

[quoted text clipped - 24 lines]

' Append the field to the table
tdf.Fields.Append fld

Very logical ... tried this and got an "invalid operation" error on the
"Append.fld" line.
The Format property is "read only" ... is there a way around this or is
there a set of
VBA-assignable specs which would give me the the 2 decimal place format?

 

[Douglas J. Steele]

If you've got references set to both ADO and DAO, try being explicit in your
declaration"

Dim fld As DAO.Field
Dim prop As DAO.Property

Field and Property are objects in both models, and since you're using DAO in
your code, it's critical that they be instantiated as the correct type.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The Format property is a String, not a Currency.

Try:

Set prop = fld.CreateProperty("Format", dbText, "Fixed")

I need to create a field using VBA which will display with 2 decimal
places.

[quoted text clipped - 24 lines]

' Append the field to the table
tdf.Fields.Append fld

Very logical ... tried this and got an "invalid operation" error on the
"Append.fld" line.
The Format property is "read only" ... is there a way around this or is
there a set of
VBA-assignable specs which would give me the the 2 decimal place format?

 

[rwboyden via AccessMonster.com]

If you've got references set to both ADO and DAO, try being explicit in your
declaration"

Dim fld As DAO.Field
Dim prop As DAO.Property

Field and Property are objects in both models, and since you're using DAO in
your code, it's critical that they be instantiated as the correct type.
[quoted text clipped - 13 lines]

there a set of
VBA-assignable specs which would give me the the 2 decimal place format?

Douglas ... thanks for the help!

I've changed the instantiation as recommended to include the DAO spec but
still get the "Invalid Operation" error ... on the Append prop line (sorry ...
not on the Append fld line). Any further thoughts?

 

[rwboyden via AccessMonster.com]

If you've got references set to both ADO and DAO, try being explicit in your
declaration"

[quoted text clipped - 10 lines]
Douglas ... thanks for the help!

I've changed the instantiation as recommended to include the DAO spec but
still get the "Invalid Operation" error ... on the Append prop line (sorry ...
not on the Append fld line). Any further thoughts?

30 Minutes Later ... I discovered that CreateField("Issue Order", dbSingle)
creates a Number field with a Single field size. This allows me to work with
values with 2 decimal places. The only restriction is that a whole number (2.
00) will not show the trailing 0's, but that's not a problem in this
appication.

Many thanks for the help and all the best wishes for the Holiday Season.

Bob Boyden