Solved

Auto Increment Field in a Query

Posted on 2007-03-21
3
541 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now