Edit Field Attributes

[dchillman]

I have a table that is created at run time with an autoincrement field.
After populating the table, I want to change the attribute for the
autoincrement field to a fixed field (i.e., I want to remove the
autoincrement).

I've tried the following, but it results in an invalid operation error. I
think it is my syntax, but I can't seem to find an example of how to change
an existing field attribute. Can someone point me in the right direction?

thanks;

dim db as database
dim tdf as tabledef
set db = currentdb
set tdf=currentdb.tabledefs("Table1)
tdf.Fields("Autonumfield").Attributes = dbFixedField

 

[George Nicholson]

AutoNumber is one option for the DataType property of a field, not
Attributes.
You can't "change" DataTypes of an existing field. You have to create a new
field of the desired type, copy "old" data into it, and delete the old
field. This is what Access does behind-the-scenes when you change the
DataType of a field in Table Design view.

HTH,

 

[dchillman]

When I created the field, the autonumber was added by using the attribute
property, as shown below;

Set db = CurrentDb
Set tdf = db.TableDefs("Table1")
Set f = tdf.CreateField("Autonumfield")
f.Type = dbLong
f.Attributes = dbAutoIncrField
tdf.Fields.Append f
tdf.Fields("Autonumfield").DefaultValue = 1

So, are you saying that once a field is created, neither its Type or
Attributes properties can be modified?

thanks for the quick response.

 

[George Nicholson]

are you saying that once a field is created, neither its Type or

Attributes properties can be modified?

Well, per DAO help file, Type is read-only once the field is appended (so,
no, it can't be modified), but Attributes is *supposed* to be read-write
when the appended Field object is associated with a TableDef. I'm a little
surprised at that, since experience says otherwise: Even if it's just a
regular numeric field (i.e., not AutoNumber), you'll get an Invalid
Operation if you try to reset the Attribute property to anything, even it's
default of 0.

Perhaps Attribute and Type are just so intertwined in some cases (like this)
that Attribute becomes read-only. I don't know. I do know that creating a
new field and updating "old" data to it would be a work around.

but maybe a MVP will contribute their thoughts on the subject...

HTH,