INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

Can I create a field in a query which auto-increments? by JimConrad
Posted: 17 May 00

It is possible using a slightly tricky Function call.

WARNING: Running functions which use STATIC variables (as this one does) within a query can cause strange effects if you scroll back and forth within the query results.

RECOMMENDATION: Use a "make table" query to extract the data into a separate table before you use it.


Public Function AutoIncr(SomeField As Variant, Optional StartFrom As Variant) As Integer

    Static CurrentCounter As Integer
    
    If IsMissing(StartFrom) Then
        CurrentCounter = CurrentCounter + 1
    ElseIf IsNumeric(StartFrom) Then
        CurrentCounter = StartFrom
    End If
    
    AutoIncr = CurrentCounter
    
End Function


Access tries to be efficient when dealing with function calls in a query.  When it sees an unchanging function, it will only call it ONCE, so we need to tell it to run the function for each row.  We can do this by refering to some column in one of the underlying tables.  For example, if you have a column called "EmployeeName"...

Add this to the query grid...

MyCounter: AutoIncr([EmployeeName])

You do not need to include the column you choose in the query itself and the AutoIncr function doesn't use it either.

You'll notice that if you run your query multiple times, that the number does NOT reset to 1.

To do that run the same query (or another setup for just this purpose):

MyCounter: AutoIncr("",0)

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close