change field property

[peljo via AccessMonster.com]

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.

 

[Douglas J Steele]

Your code is trying to change the Format, not the Type. You cannot change
the Type of a field using DAO once it's been added to a table. You can add a
new field of the correct type, run an update query to transfer existing
values from the old field to the new field, delete the old field then rename
the new field.

 

[chris.nebinger]

I just wrote the code for that for another post, so....



Sub FieldChange()
'This will fail if the field is a part of any index or
relationship.....
'Additional code is needed if that is a possibility
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Contacts")
Set fld = tdf.CreateField("LastName1", dbLong)
tdf.Fields.Append fld
dbs.Execute "Update Contacts set lastname1 = clong(lastname)"
tdf.Fields.Delete "LastName"
tdf.Fields.Refresh
Set fld = tdf.CreateField("LastName", dbLong)
tdf.Fields.Append fld
dbs.Execute "Update Contacts set lastname = lastname1"
tdf.Fields.Delete "LastName1"
tdf.Fields.Refresh
End Sub


Chris Nebinger

 

[peljo via AccessMonster.com]

Thank you for your reply.I copied your suggestion but i get an error : cannot
find function clong.Could you help ?
Public Function FieldChange()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Pricelist")
Set fld = tdf.CreateField("code1", dbLong)
tdf.Fields.Append fld
dbs.Execute "Update PriceList set code1 = clong(code)"
tdf.Fields.Delete "code"
tdf.Fields.Refresh
Set fld = tdf.CreateField("code", dbLong)
tdf.Fields.Append fld
dbs.Execute "Update PriceList set code = code1"
tdf.Fields.Delete "code1"
tdf.Fields.Refresh
End Function

 

[peljo via AccessMonster.com]

I have deleted the cong ad it works just fine.Thank you very mcuh !

 

[Douglas J Steele]

It should have been CLng, not CLong, but as you've found, it's not really
necessary.

 

[chris.nebinger]

That's what I get for typing "Air Code"...