Trying to Change field properties 1

[hpicken]

I have a table called "readings" and a field called "XSRate" which has data in it. I'm using DAO 3.6

At the moment this field is set to 4 decimal places but I want to change it to 2 decimal places (it won't effect the data).

I've tried using...


Function ChangeField()

Dim db As Database, tdf As DAO.TableDef, fld As DAO.Field
Dim prp As DAO.Property
On Error Resume Next

Set db = OpenDatabase("M:\SMData\smdata.mdb")
Set tdf = db.TableDefs!readings
Set fld = tdf.Fields!XSRate
Set prp = fld.CreateProperty("DecimalPlaces", dbByte, 2)
fld.Properties.Append prp

db.Close
Set db = Nothing

End Function

but I get the following error.

Runtime error "3367"
Cannot Append. An object with that name already exists in the collection.

Anyone have any ideas about what I'm doing wrong.

 

[DeanWilliams]

Hi,

Instead of the line:
Set prp = fld.CreateProperty("DecimalPlaces", dbByte, 2)

Use

[COLOR=blue]
Set prp = fld.Properties("DecimalPlaces") = 2
[/color]

And leave out the Append line.
HTH Dean

 

[TonyJollans]

Hi hpicken,

You don't want to be setting the prp object variable to anything; all you want to do is set the value of the property itself, so just use ..

[blue]fld.Properties("DecimalPlaces") = 2[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[hpicken]

Thanks Tony that worked. Sorry Dean I got an eror with yours but thanks for the response.

Howard

 

[DeanWilliams]

Oh yeah, doh!

School boy error.

 

[kevinwilson]

I have a similar problem where I want to update a fields datatype. My field name is "Tech2Notes" and it's datatype is "text", I want to change the datatype to "Memo". I tried modifying the above syntax but kept screwing it up! I would imagine this is simple to do but I'm pretty brain dead!

Any suggestions?

Many thanks

 

[TonyJollans]

Hi kevinwilson,

I'm afraid the datatype needs a completely different approach ..

[blue]DoCmd.RunSQL "ALTER TABLE [i]YourTableName[/i] ALTER COLUMN Tech2Notes Memo"[/blue]

This needs A2K. I have a feeling you can't do it in code in 97 - no doubt someone will correct me if I'm wrong.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.