Making The Value of a Numeric Access Field For a New Record Be One Higher Than That of The Field's Highest Value*

1. Open the form used to enter data into the field in design view.
2. Make the label and text box for the field invisible.
3. Click on the text box for the field.
4. In the data properties text box labeled default value enter [Currentdb].[TableDefs]("table name").[RecordCount]+1
   
   replacing table name with the name of the table where data entered into the form is stored.
5. In the afterupdate event for the first control in the tab order   enter
   If Me.NewRecord = True Then
       Me.Refresh
       Me.txtItem__.DefaultValue = [CurrentDb].[TableDefs] _
      ("table  name").[RecordCount] + 1
   End If
   
   replacing table name with the name of the table where data entered into the form is stored.

*Please note that this will only work if the highest existing value for the field is equal to the number of records in the table containing the field.

I use this for favorite songs tape mangement.  I like my reports to  display the songs in the order they appear on tape which can change with re recording.  Access does not allow data to be rearanged like the AmiPro tables I was using.  To solve the problem I assigned each song a number corresponding to its location on the tape and created a query to sort on the field.  When I reorganized to have one table for each tape to elimate the tape number field I renumbered them so that each tape's first song is number 1.

After a while I relized that each new record created for a still not full  tape is to have a number one higher than the previous record.  That is when I came up with the above.  With this method I need only enter numbers during reordering which I do in the data sheet view so I can see the new order (This lets me map out the changes then re record using the data base as a quide.)

Lila Godel's Web Site
Brought To You By Hamilton Services, Inc.