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.

--
Message posted via http://www.accessmonster.com

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"peljo via AccessMonster.com" <u19312@uwe> wrote in message
news:5e0b6471ec2a5@uwe...
> 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.
>
> --
> Message posted via http://www.accessmonster.com

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

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







(E-Mail Removed) wrote:
>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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...dules/200603/1

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


(E-Mail Removed) wrote:
>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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...dules/200603/1

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"peljo via AccessMonster.com" <u19312@uwe> wrote in message
news:5e0dfcf028f70@uwe...
> I have deleted the cong ad it works just fine.Thank you very mcuh !
>
>
> (E-Mail Removed) wrote:
> >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
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200603/1

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