[AccessD] Custom autonumbering

Asger,

    It is observed that if the form object is also passed as an argument to the embedded function assigned to default property of the bound control carrying the custom numbers, and Recalc action is performed at appropriate stage in the function code, generation & display of default custom number becomes very prompt, just like the text (AutoNumber) displayed for pure autonumber on a new record. 

    As soon as the new record is dirtied, next custom number in series, as per given prefix, gets displayed in the fresh new record.

    The approach outlined above is demonstrated in my sample db named Form_CustomSeriesByDefaultProperty, available at Rogers Access Library. Link - 
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45


    As the data entry progresses, the default value keeps incrementing as required, without having to undertake any special manipulation via form's current event. Moreover, it is not necessary to use other form events like Dirty / Before or After Insert / Before or After Update etc. 
  
    The prefix is selected via combo box on the main form. Whenever, a new prefix is selected, the newly displayed default value on the subform gets set 1 higher than the existing highest in alpha-numeric sequence pertaining to the selected prefix. If there is no existing entry with the given prefix, the default value gets set to prefix followed by 000001. 

    Type of prefix can be selected via option group as follows:
    (a) Prefix by combo box selection. 
    (b) Prefix as per current year (yyyy)
    (c) Prefix as per current year month (yyyymm) 

    The sample is in Access 2000 file format and no extra reference is needed. It has been tested for single user. You might like to verify whether it stands the test for multi-user environment.

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Asger Blond 
  To: 'Access Developers discussion and problem solving' 
  Sent: Thursday, July 02, 2009 04:27
  Subject: Re: [AccessD] Custom autonumbering


  Actually I don't want to mimic the AutoNumber field type. I want a custom
  autonumber which is 1) reliable sequential, and 2) customable. Your
  two-table solution provides the first requirement and I will consider it for
  future use. But sometimes I need to supply an easy way of creating a new
  offset for the autonumber: e.g. using the current year as a leading number
  the user should have the ability to change the number let's say 20098001 to
  20100001, making this a new starting point for the sequential numbering
  (normally this custom autonumber field should be locked on the form and only
  editable providing a password).
  I still find the DMAX-solution easier to manage for the second requirement.
  But as said I appreciate your proposal for a reliable sequential numbering.

  Asger