i have a complex reporting module which creates reports on the fly. that’s why i’m adding table programmatically, this is a way to allow to add and edit reports without having to redesign the app, everything is dynamic, there’s a set of tables that store configuration parameters and adding or changing a report just requires changing some data in those tables

so, need to set the DecimalPlaces property, it’s set to Auto by default, i want to change it to 0 or 2 (depending on the settings)
the code doesn’t give any errors but the property doesn’t change, DecimalPlaces still says AUTO for all. i stepped through the code, it goes through those lines, just nothing changes in the table

Sub test()
fnReports 4
End Sub

Function fnReports(vrReportNo As Long)

Dim db As DAO.database
Dim rs As DAO.Recordset
Dim rsS As DAO.Recordset
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim vrSQL As String
Dim i As Integer
Dim vrTableName As String

'create report table
 
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb

vrTableName = "mtMDAR" & vrReportNo

vrSQL = "SELECT MDARReportFields.SortNoField, MDARReportFields.FieldName, MDARReportFields.FieldSize, FieldFormats.FieldFormat " & _
        ", MDARReportFields.FieldDecimalNo, FieldDataTypes.FieldDataTypeNo, FieldDataTypes.FieldDataType, FieldDataTypes.FieldDataTypeVBA " & _
        "FROM FieldFormats RIGHT JOIN (FieldDataTypes RIGHT JOIN MDARReportFields ON FieldDataTypes.FieldDataTypeID = MDARReportFields.FieldDataTypeID) " & _
        "ON FieldFormats.FieldFormatID = MDARReportFields.FieldFormatID WHERE MDARReportFields.ReportNo=" & vrReportNo & "  ORDER BY MDARReportFields.SortNoField"

fnDeleteObjectIfExists "table", vrTableName
Set tdf = db.CreateTableDef(vrTableName)

Dim vrSortNoField As Long
Dim vrFieldName As String
Dim vrFieldSize As Long
Dim vrFieldFormat As String
Dim vrFieldDecimalNo As Long
Dim vrFieldDataTypeNo As Long
Dim vrFieldDataType As String
Dim vrFieldDataTypeVBA As String
Dim prp As DAO.Property

Set db = CurrentDb
Set rsS = db.OpenRecordset(vrSQL, dbOpenSnapshot)


Do Until rsS.EOF
 
    vrFieldName = rsS!FieldName
    vrFieldDataTypeNo = rsS!FieldDataTypeNo
    If vrFieldDataTypeNo = 10 Then
        vrFieldSize = rsS!FieldSize
    End If

    With tdf
        If vrFieldDataTypeNo = 10 Then
            Set fld = .CreateField(vrFieldName, vrFieldDataTypeNo, vrFieldSize)
        Else
            Set fld = .CreateField(vrFieldName, vrFieldDataTypeNo)
        End If
        .Fields.Append fld
    End With

    rsS.MoveNext
Loop

db.TableDefs.Append tdf


Set rsS = db.OpenRecordset(vrSQL, dbOpenSnapshot)
Do Until rsS.EOF

    'vrSortNoField = rsS!SortNoField
    vrFieldName = rsS!FieldName
    vrFieldDataTypeVBA = rsS!FieldDataTypeVBA
    vrFieldDataTypeNo = rsS!FieldDataTypeNo
    vrFieldDataType = rsS!FieldDataType
    If vrFieldDataTypeNo <> 5 And vrFieldDataTypeNo <> 10 Then
        vrFieldFormat = rsS!FieldFormat
        vrFieldDecimalNo = rsS!FieldDecimalNo
    End If


    With tdf
        Set fld = .Fields(vrFieldName)
     
        If vrFieldDataTypeNo <> 5 And vrFieldDataTypeNo <> 10 Then

            Set prp = fld.CreateProperty("DecimalPlaces", vrFieldDataTypeNo, vrFieldDecimalNo)
            fld.Properties("DecimalPlaces") = 0
            fld.Properties.Append prp
             
     
            Set prp = fld.CreateProperty("Format", dbText, vrFieldFormat)
            fld.Properties.Append prp
        
        End If

    End With

    rsS.MoveNext
Loop


Set tdf = Nothing
rsS.close
Set rsS = Nothing

Set db = Nothing

 

End Function