[AccessD] Add records with sequential numbers

Wow!  Great solutions from several of you.  I'm going to try at least this 
one from Gustav, and probably the others to see which one fits our situation 
best.  Thank you for such good ideas so quickly!

Gina


From: Gustav Brock <gustav at cactus.dk>
Reply-To: Access Developers discussion and problem 
solving<accessd at databaseadvisors.com>
To: Access Developers discussion and problem 
solving<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Add records with sequential numbers
Date: Tue, 24 Feb 2004 17:37:18 +0100

Hi Gina

For your append query which copies the invoices to the invoice table,
you may be able to modify the functions we use for tasks of this type.
The _Set function is used for setting the initial value (the first
number), the _Get function is used in the query. For varDummy you may
use any field from your source (temp) table.

<code>

' Declaration.
Public lngKeyCounter As Long

Public Function NextKey_Get(Optional ByVal varDummy, Optional ByVal 
intIncrement As Integer = 1, Optional ByVal intInitial As Integer) As Long

   ' Increments dynamic public (global) variable lngKeyCounter in 
Declarations:
   '
   '   Public lngKeyCounter As Long
   '
   ' with intIncrement.
   ' Returns the new value of global variable lngKeyCounter.
   ' Parameter varDummy is used to force repeated calls of this function 
when used in a query.

   Dim intSgn As Integer

   If Not intIncrement = 0 Then
     intSgn = Sgn(intIncrement)
     If intSgn * lngKeyCounter < intSgn * intInitial Then
       lngKeyCounter = intInitial
     Else
       lngKeyCounter = lngKeyCounter + intIncrement
     End If
   End If

   NextKey_Get = lngKeyCounter

End Function

Public Function NextKey_Set(Optional ByVal lngSet As Long) As Long

   ' Sets dynamic public (global) variable lngKeyCounter in Declarations:
   '
   '   Public lngKeyCounter As Long
   '
   ' to value of lngSet.
   ' Retrieves current value of lngKeyCounter.

   NextKey_Set = lngKeyCounter
   lngKeyCounter = lngSet

End Function

</code>

Have fun!

/gustav

_________________________________________________________________
Dream of owning a home? Find out how in the First-time Home Buying Guide. 
http://special.msn.com/home/firsthome.armx