Change field property

[peljo]

Can you help me with the code to change the property of a field in a table.My field is called code and i want to change the property from text into number.I have made the following code:

Public Function ChangeProperty()

Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim dbs As DAO.Database
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Pricelist")
Set fld = tdf.Fields("code")
Set prp = fld.CreateProperty("Format", dbsingle)
fld.Properties.Append prp
dbs.Close
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing

End Function

I get the following error :


Property value must be set before using this method.

 

[Remou]

I think you need to use Alter Table for this kind of action. Field type is read only once it has been appended. The trouble with Alter Table is it comes with all sorts of 'ifs'.
[tt]strSQL = "Alter Table pricelist Alter Column code Int"
CurrentDb.Execute strSQL[/tt]

 

[Golom]

He's trying to change the Format (I think) ... not the data type. It needs something like

Public Sub ChangeProperty()

    Dim tdf                         As DAO.TableDef
    Dim fld                         As DAO.Field
    Dim prp                         As DAO.Property
    Dim dbs                         As DAO.Database
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("Pricelist")
    Set fld = tdf.Fields("code")

    Set prp = fld.CreateProperty("Format", dbText, "$#,##0.00;($#,##0.00)")
    fld.Properties.Append prp
    dbs.Close
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
End Sub

The arguments for "CreateProperty" are

CreateProperty(name, type, value, DDL)

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Remou]

Golom

change the property from text into number

But perhaps you are right, at that.