Determine whether a field is an autonumber

Guys, I'm having a brain cramp.  Must be long weekend syndrom, or working outside in 90+ degree heat.

How can I determine whether a field is an autonumber field without counting on it also being the primary key of the table?

When I examine the fields Type property, it returns 4, long integer.

If it is not the primary key, the fields Required property returns false, but in the table design view I can see a "New Values" property on the General tab, with options of 'Increment' and 'Random', but I cannot seem to find the appropriate property for the field to determine whether it is an autonumber field.
LVL 51
Dale FyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can do this:

If currentdb.TableDefs("YourTable").Fields("YourField").Attributes and dbautoincrfield = dbAutoIncrField Then
  '/ the field is an autoincrement
End If
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Dale,

 You need to check the field attributes:

debug.? CurrentDB().TableDefs(<table name>).Fields(<field Name).Attributes And dbAutoIncrField

If it retuns 0, it's a long.  16, it's an autonumber.

Jim.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I created an AN field in a table, and it returns 17.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I created an AN field in a table, and it returns 17. >>

 A numeric field by default is fixed in size, so if your just looking at the attribute value, it will be 17 (bits 5 and 1 are on  00010001)

But if you do this:

debug.? CurrentDB().TableDefs(<table name>).Fields(<field Name>).Attributes And dbAutoIncrField

You'll get 16 back,  which is specifically checking bit 5 alone.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<A numeric field by default is fixed in size, so if your just looking at the attribute value, it will be 17 (bits 5 and 1 are on  00010001)>>

 BTW, old habit of mine; I always put the least significant bit on the right.

Jim.
0
Dale FyeAuthor Commented:
Thanks, guys.

It's great to know a bunch of guys with nothing better to do on a national holiday than answer questions!  Anybody going to Seattle in Nov?  I'm looking for a room mate.

Happy Labor Day!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<It's great to know a bunch of guys with nothing better to do on a national holiday than answer questions!  Anybody going to Seattle in Nov?  I'm looking for a room mate.>>

 I've been on the fence; one of the problems was who to share a room with.  The other is that I'm so far behind with everything.  Really feel like I can't aford to take the time off.

 I really gotta make up my mind this week...

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.