How to set field properties from VBA

[spikepl]

I am creating a table on the fly, and need for external reasons to format the Double field into STandard with 2 decimals. The code fails with Invalid operation.

What have I missed?

I have used the SetFieldProperty elsewhere on an existing table, and it worked just fine.

Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim fldprop As DAO.Property

    If TableExists("tblKPI") Then
        CurrentDb.TableDefs.Delete ("tblKPI")
    End If
    
 
    
    Set tbl = CurrentDb.CreateTableDef("tblKPI")
    Set fld = tbl.CreateField("KPI", dbText)
    
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("SOurce", dbText)
    tbl.Fields.Append fld

    
    rstSource.MoveFirst
    Dim KortNavn As String
    Dim myID As Long
    
    While Not rstSource.EOF
        
        myID = rstSource!StamdataID
        KortNavn = DLookup("ShortName", "tblBasics", "BasicsID=" & myID)
        Set fld = tbl.CreateField(ShortName, dbDouble)
[COLOR=Red]        SetFieldProperty fld, "Format", dbText, "Standard"[/COLOR]
        SetFieldProperty fld, "DecimalPlaces", dbInteger, 4
        tbl.Fields.Append fld

        rstSource.MoveNext
    Wend

    CurrentDb.TableDefs.Append tbl

Public Sub SetFieldProperty(ByVal fld As DAO.Field, ByVal strPropertyName As String, ByVal iDataType As DAO.DataTypeEnum, ByVal vValue As Variant)
    Dim prp As DAO.Property

    Set prp = Nothing

    On Error Resume Next
    Set prp = fld.Properties(strPropertyName)
    On Error GoTo 0

    If prp Is Nothing Then
        Set prp = fld.CreateProperty(strPropertyName, iDataType, vValue)
[COLOR=Red]        fld.Properties.Append prp[/COLOR] 'this gives Invalid operation
    Else
        prp.Value = vValue
    End If
End Sub

 

[jdraw]

Spike,

I saw your post and always wondered how to set decimal places via code.
I searched the AllenBrowne Dao stuff, but he doesn't show decimal places.

http://allenbrowne.com/func-dao.html


I copied his code samples down to my PC and started playing with his tables.

I did manage to alter his code to put a field called PenDouble into his tblDAOContractor. I substituted my field for his PenaltyRate

In his CreateTableDAO I changed this

'Number field.
' .Fields.Append .CreateField("PenaltyRate", dbDouble)
.Fields.Append .CreateField("PenDouble", dbDouble, 8)

I then tried to create the property DecimalPlaces, but you can't do that until the Fields collection has been saved -- you get an error message.

So I looked at his Standardproperties function and commented out his line and added another to deal with decimal places on a dbDouble type field see this

' Case dbLong, dbInteger, dbByte, dbDouble, dbSingle, dbDecimal
' fld.DefaultValue = vbNullString
'**************************************** JED
Case dbDouble
Call SetPropertyDAO(fld, "DecimalPlaces", dbByte, _
2, strErrMsg)
'******************************* JED

I then set up a small procedure as follows

Sub Jacktest()
Dim s As String
s = "tblDaoContractor"
StandardProperties (s)
End Sub

And, ##@!%%$#@, it worked, I set the decimal places to 2 for dbDouble datatype in his table.

Note, there are several procs in Allen's stuff. I copied them into a Module and worked as mentioned above. You may wish to put all of his DAO materials into a separate module, adjust as I did, then create a procedure to call his standard properties proc with your table name.

Good luck.

Perhaps the secret with your code is to Append the fields to Fields collection, then set the dbdouble field's DecimalPlaces property.

 

[spikepl]

Thanks I'll check it out. Cannot figure out where I go wrong. Tried to modify before appending, after appending , after saving table .... it's obviously something utterly OBVIOUS

I'll post my solution when I get there.

 

[elliotgr]

Try this

Dim db as database
Set db = currentdb
db.Execute "ALTER TABLE TableName ADD COLUMN [ColumnName] DOUBLE;"
CurrentProject.Connection.Execute "ALTER TABLE TableName ALTER COLUMN ColumnName DECIMAL (28,0)"
db.close

You have to create the field before modifying it.
With others field you normally can create with the correct formatting, but Access doesn't handle decimal too well.
Hope this helps.

 

[jdraw]

For what it's worth, I tried an SQL Alter statement with db.execute

It gave error.
I then tried CurrentProject.connection with an Alter using a Double data type. It also gave error (syntax) no matter what I tried.

I then tried

sql = "ALTER TABLE Pharmacy Alter COLUMN CostTest3 decimal(10,4)"
CurrentProject.Connection.Execute sql, dbFailOnError

And it worked fine with DECIMAL data type. It gave syntax problems with DOUBLE datatype.