Edit Field Attributes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dchillman - 16 Jan 2006 19:11 GMT

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

Signature

dchillman

Reply to this Message

George Nicholson - 16 Jan 2006 19:44 GMT

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,

Signature

George Nicholson

Remove 'Junk' from return address.

>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
[quoted text clipped - 13 lines]
> set tdf=currentdb.tabledefs("Table1)
> tdf.Fields("Autonumfield").Attributes = dbFixedField

Reply to this Message

dchillman - 16 Jan 2006 19:56 GMT

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.

Signature

dchillman

> AutoNumber is one option for the DataType property of a field, not
> Attributes.
[quoted text clipped - 21 lines]
> > set tdf=currentdb.tabledefs("Table1)
> > tdf.Fields("Autonumfield").Attributes = dbFixedField

Reply to this Message

George Nicholson - 16 Jan 2006 21:57 GMT

> 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,

Signature

George Nicholson

Remove 'Junk' from return address.

> When I created the field, the autonumber was added by using the attribute
> property, as shown below;
[quoted text clipped - 40 lines]
>> > set tdf=currentdb.tabledefs("Table1)
>> > tdf.Fields("Autonumfield").Attributes = dbFixedField

Reply to this Message