[AccessD] Autonumber in queries

    A word of caution! Incremental numbers based upon global variables are found to exhibit certain peculiarities when used in a query.

    Each navigational pass through such a calculated field, triggers a change in its value. If one experiments by moving up & down (keeping such a field selected), the displayed values keeps on increasing. The phenomenon is persistent, irrespective of the fact whether the select query is viewed directly or the results are displayed on a form using such a query as the record source. 

    Under the circumstances, global variable approach is apparently best suited only to single pass action queries (e.g. append query), and NOT for display purposes (e.g. select query).

Regards,
A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: Andy Lacey 
  To: 'Access Developers discussion and problem solving' 
  Sent: Wednesday, July 07, 2004 12:08
  Subject: RE: [AccessD] Autonumber in queries


  Hi Dale

  Create a module with this in:

  Global lngTableCounter As Long

  Function MyAutoCtr(prmAny)
  MyAutoCtr = lngTableCounter
  lngTableCounter = lngTableCounter + 1
  End Function

  Before running the query set lngTableCounter =0

  In Query make a column such as

  MyAutoCtr([anyfieldname]) As Counter

  You must pass a field from the input table(s) as in this example so that function called for each record otherwise Access thinks that the function will always return the same value and only calls it once and every output record gets the same value.

  -- Andy Lacey
  http://www.minstersystems.co.uk 



  > -----Original Message-----
  > From: accessd-bounces at databaseadvisors.com 
  > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dale Kalsow
  > Sent: 06 July 2004 22:02
  > To: accessd at databaseadvisors.com
  > Subject: [AccessD] Autonumber in queries
  > 
  > 
  > Does any one know how to add a column in a query that is an 
  > autonumber column?>  
  > 
  > Thanks!  
  > 
  > Dale