Solved

Auto Increment Field in a Query

Posted on 2007-03-21
3
564 Views
Last Modified: 2008-01-09
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
Comment
Question by:erichranz
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 125 total points
ID: 18764635
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
 
LVL 4

Author Comment

by:erichranz
ID: 18800702
Thank you, sir!
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 18800730
You are welcome!

/gustav
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question