Adding Fields property DecimalPlaces using the fields collection

[jeff]

Hi gurus

I am using vb code to create a table and add fields. I need to set the
DecimalPlaces property of a field to a value. I cannot see how to do this as
it is not a "standard" attribute of the field object eg like
fieldobject.AllowZeroLength = True where I want to
fieldobject.DecimalPlaces=1

Can anyone help me with this...it is not so obvious in the online help...or
am i missing something entirely??

cheers
Jeff

 

[Alex Dybenko]

Hi,
you have to add property DecimalPlaces to this field. See below a sample to
add property to database, this give you an idea, you can adjust it for field

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


Sub SetProperty(dbsTemp As Database, strName As String, _
booTemp As Boolean)

Dim prpNew As Property
Dim errLoop As Error

' Attempt to set the specified property.
On Error GoTo Err_Property
dbsTemp.Properties("strName") = booTemp
On Error GoTo 0

Exit Sub

Err_Property:

' Error 3270 means that the property was not found.

If DBEngine.Errors(0).Number = 3270 Then
' Create property, set its value, and append it to the
' Properties collection.
Set prpNew = dbsTemp.CreateProperty(strName, _
dbBoolean, booTemp)
dbsTemp.Properties.Append prpNew
Resume Next
Else
' If different error has occurred, display message.
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop

End
End If

End Sub

 

[Aussie Jeff]

Alex

Thx for this. But this is an example straight from the help file. I have
already tried this using a field definition and appending to the field
properties collection. I can create the property but when I append it to the
collection it produces an error.

Also I am not sure if the DecimalPlaces property is a dbInteger or dbLong or
dbText field? I notice that in the design for a table the DecimalPlaces
property can have a value of Auto or 0 thru to 15. Is Auto represented
with -1?

I have posted my code below:-

Dim ldbsCurrent As DAO.Database

Dim lrstSource As DAO.Recordset
Dim lfldSource As DAO.Field
Dim lstrDestination As String
Dim lrstDestination As DAO.Recordset
Dim ltdfDestination As DAO.TableDef
Dim lprpDestination As DAO.Property
Dim lstrCurrentEntityClass As String
Dim lstrNewEntityClass As String

Set ldbsCurrent = CurrentDb()
Set lrstSource = ldbsCurrent.OpenRecordset("tblEntityFieldUsage")
lstrDestination = "tblXML_FieldUsage_Component"
Set ltdfDestination = ldbsCurrent.CreateTableDef(lstrDestination)

'using the source fields collection, create fields in the new table
definition
For Each lfldSource In lrstSource.Fields
ltdfDestination.Fields.Append
ltdfDestination.CreateField(lfldSource.Name, lfldSource.Type)
ltdfDestination.Fields(lfldSource.Name).AllowZeroLength = True
If lfldSource.Type = dbLong Then
'code fails here....
Set lprpDestination =
ltdfDestination.Fields(lfldSource.Name).CreateProperty("DecimalPlaces",
dbInteger, 0)
ltdfDestination.Fields(lfldSource.Name).Properties.Append
lprpDestination
End If
Next

'append the new destination table to the tables collection and
'open a new recordset
ldbsCurrent.TableDefs.Append ltdfDestination
Set lrstDestination = ldbsCurrent.OpenRecordset(lstrDestination)

Maybe you or someone else can spot the mistake? (I have been staring at it
for toooo long...)

cheers
Jeff

 

[Alex Dybenko]

Hi Jeff,
what error do you get?
try like this:

dim fld as dao.field


set fld=ltdfDestination.CreateField(lfldSource.Name, lfldSource.Type)
Set lprpDestination = fld.CreateProperty("DecimalPlaces", dbByte, 0)
fld.Properties.Append lprpDestination
ltdfDestination.Fields.Append

this is how it works at me

as for Auto - i think it is the same as no DecimalPlaces defined, and
property does not exists

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[jeff]

Alex
I have changed my code to match yours....

Dim ldbsCurrent As DAODatabase
Dim lrstSource As DAO.Recordset
Dim lfldSource As DAO.Field
Dim lstrDestination As String
Dim lrstDestination As DAO.Recordset
Dim ltdfDestination As DAO.TableDef
Dim lfldDestination As DAO.Field '<<<<<<< added
Dim lprpDestination As DAO.Property
Dim lstrCurrentEntityClass As String
Dim lstrNewEntityClass As String

'using the source fields collection, create fields in the new
table definition
For Each lfldSource In lrstSource.Fields
Set lfldDestination =
ltdfDestination.CreateField(lfldSource.Name, lfldSource.Type)
If lfldSource.Type = dbLong Then
Set lprpDestination =
lfldDestination.CreateProperty("DecimalPlaces", dbByte, 0)
lfldDestination.Properties.Append lprpDestination
'<<<<<<<< fails at this line with error below
End If
ltdfDestination.Fields.Append lfldDestination
ltdfDestination.Fields(lfldSource.Name).AllowZeroLength =
True
Next

'append the new destination table to the tables collection and
'open a new recordset (implicitly closes the previous open
recordset)
ldbsCurrent.TableDefs.Append ltdfDestination
Set lrstDestination = ldbsCurrent.OpenRecordset(lstrDestination)

Error:

Error# 3219 was generated by DAO.Property
Invalid operation.


It looks as though it should work...but...

cheers
Jeff (aussie jeff!)

 

[Douglas J. Steele]

Given that a Long Integer cannot have any decimal places, it really doesn't
make sense to set the DecimalPlaces property for such a field.

I'm wondering if that's the problem?

 

[jeff]

Thx Douglas for the input!

When viewing an equivalent field in the table design view, the field is set
to Number and has a field size of Long Integer with Decimal Places set to 2.
The data in the field is showing 2 decimal places, so it appears that it is
correct.

I understand what you are saying in that an Integer by definition is a whole
number and therefore no decimal places...but I am not sure that Access
understands this.. <grinning>. The Access Design view allows a size of Long
Integer to be specified and Decimal Places of 2 to be set.

It is baffling me...but i May just have to find a workaround...more to
come...

cheers
Jeff

 

[jeff]

Guys

I have done some more work...I enumerated the properties of a manually
created tabledef with the appropriate field def using the table design
view:-

PercentageUsage
Value = 18
Attributes = 34
CollatingOrder = 18
Type = 20
Name = PercentageUsage
OrdinalPosition = 4
Size = 16
SourceField = PercentageUsage
SourceTable = tblXML_FieldUsage_Component
ValidateOnSet = False
DataUpdatable = True
ForeignName
DefaultValue = 0
ValidationRule =
ValidationText =
Required = False
AllowZeroLength = False
FieldSize = 4
OriginalValue =
VisibleValue =
ColumnWidth = -1
ColumnOrder = 0
ColumnHidden = False
Description = Percentage Usage of the Field in the Table
PercentageUsage
DecimalPlaces = 2
DisplayControl = 109

Type = 20 is for a DAO object a dbDecimal and the Type for the DecimalPlaces
is 2 (dbByte).so I tried the following...

If lfldSource.Name = "PercentageUsage" Then
Set lfldDestination =
ltdfDestination.CreateField(lfldSource.Name, dbDecimal)
Set lprpDestination =
lfldDestination.CreateProperty("Decimal Places", dbByte, 0)
lfldDestination.Properties.Append lprpDestination
Else
Set lfldDestination =
ltdfDestination.CreateField(lfldSource.Name, lfldSource.Type)
End If

and now I get an error of:-

Error# 3259 was generated by DAO.Field
Invalid field data type.

the saga continues...
jeff

 

[jeff]

Guys and finally....

It appears that the table needs to be appended to the TableDefs collection
BEFORE adding these properties...maybe something to do with the fact that
they only relate to an Access database table and not say a SQL database
table??? (maybe barking up the wrong tree). Anyway by simply creating the
tabledef completely and appending it to the collection I then changed the
particular fields proeprties as below:-

ldbsCurrent.TableDefs.Append ltdfDestination

Set ltdfDestination = ldbsCurrent.TableDefs(lstrDestination)
Set lfldDestination = ltdfDestination.Fields("PercentageUsage")
Set lprpDestination = lfldDestination.CreateProperty("Format",
dbText, "Fixed") '<=this appears critical
lfldDestination.Properties.Append lprpDestination
Set lprpDestination =
lfldDestination.CreateProperty("DecimalPlaces", 2, 0) '<= this now
works....
lfldDestination.Properties.Append lprpDestination

Anyway...it achieved the result I was after...BTW the field was first
created as a Double....maybe there is something to that!

cheers and thanks to Alex and Doug for your Input!!

aussie jeff

 

[Alex Dybenko]

Great, that you made it work. But strange, AFAIR - in my code for just
created field it was required to add first properties. I never understand
this...

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com