• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

Auto Increment Field in a Query

I want to have a new field in the results of an access query that auto increments (1,2,3...).  Is this possible?  Thanks!
0
erichranz
Asked:
erichranz
  • 2
1 Solution
 
Gustav BrockCIOCommented:
Yes, use a function like this where the field ID is your primary or at least unique key:

Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean) _
  As Long
 
' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.

  Static col As New Collection
 
  On Error GoTo Err_RowCounter
 
  If booReset = True Then
    Set col = Nothing
  Else
    col.Add col.Count + 1, strKey
  End If
 
  RowCounter = col(strKey)
 
Exit_RowCounter:
  Exit Function
 
Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

/gustav
0
 
erichranzAuthor Commented:
Thank you, sir!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now