[AccessD] Add records with sequential numbers

Gina,

    You could consider a query for appending new records having values incrementing by 1, starting with MaxVal + 1 . (MaxVal is existing maximum value of the field holding series data)

    It involves an interesting use of simple custom functions based upon global variables. Relevant SQL as well as the code details are given below.

Regards,
A.D.Tejpal
--------------
======================
    Let main table be named T_Series having one of the fields named RefValue (Number type long) for holding a series of numerical values. Table T_SeriesAppend is the table from which data is to be appended to table T_Series and has same structure.
======================
SQL Statement (Append Query) -
    INSERT INTO T_Series ( RefValue )
    SELECT Fn_SeriesNext([T_SeriesAppend].[RefValue]*0) 
    AS RFV
    FROM T_SeriesAppend
    WHERE Fn_SeriesMax()>=0;

Note - (i) Where clause is meant to set initial values for global variables MaxVal and NextVal via custom function Fn_SeriesMax()
            (ii) Multiplication by zero in the argument passed to function Fn_SeriesNext() in the Append SQL is meant to force discarding of existing value for field RefValue in the second table i.e. T_SeriesAppend so that newly appended records will only carry series of values in continuation to the existing maximum in the main table.
            (iii) In the sample SQL for append action, only the series field has been mentioned for illustration. Other fields can be  included as desired.

Code Module -
Option Compare Database
Option Explicit
Public MaxVal As Long, NextVal As Long

Function Fn_SeriesMax() As Long
    MaxVal = Nz(DMax("RefValue", "T_Series"), 0)
    NextVal = MaxVal + 1
    Fn_SeriesMax = MaxVal
End Function

Function Fn_SeriesNext(ByVal FValue As Variant) As Long
    FValue = Nz(FValue, 0)
    If FValue > 0 Then
        Fn_SeriesNext = FValue
    Else
        Fn_SeriesNext = NextVal
        NextVal = NextVal + 1
    End If
End Function
=====================================
  ----- Original Message ----- 
  From: Gina Hoopes 
  To: AccessD at databaseadvisors.com 
  Sent: Tuesday, February 24, 2004 21:28
  Subject: [AccessD] Add records with sequential numbers


  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?

  Thanks,
  Gina