[AccessD] Add records with sequential numbers

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


> I've got an invoicing database that the end user has used to create invoices 
> on an individual basis.  Each time she clicks the "new invoice" button, I've 
> coded the form to give her the next number in sequence.  She has a specific 
> range of numbers she's required to use, so it can't just be an Auto Number.  
> Anyway, it's been working great, but now they want to automate the process 
> so that the db automatically generates all invoices for the month after I've 
> presented her with a screen to check or uncheck those that should be 
> created.  So, what I need help with doing is taking the temp table which 
> contains those items she's checked, appending them to the Invoices table, 
> and assigning them numbers in sequence.  If this was a straight auto number 
> situation it would be easy, but I can't figure out how to get it to work 
> using her numbering system.  The invoice number field is a text field 
> because it contains non-numeric characters, but I've been able to get it to 
> behave as a number for the individual creation purpose I described above by 
> stripping the alpha characters, adding 1, then putting the alpha characters 
> back in.  Can someone give me an idea of how to use an append query, or 
> something similar, that will allow me to automate this process?