Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Auto Increment Field in a Query

Posted on 2007-03-21
3
Medium Priority
?
598 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 500 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 51

Expert Comment

by:Gustav Brock
ID: 18800730
You are welcome!

/gustav
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

730 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