How to Increment Default Value for Each New Record

[bullfrog83]

I have a form with two textboxes: txtSortOrder and txtCourse. I want the txtSortOrder's default value to increment by 10 for each new record (this is to save data entry time). So, if I type in 10 for the first record, the default value for the second record is 20 and so on. Right now I have this code on the form's AfterUpdate event:

If Not IsNull(Me.txtSortOrder) Then
    Me.txtSortOrder.DefaultValue = Me.txtSortOrder.DefaultValue + 10
End If
 

However, this doesn't entirely work the way I want it to. For the first record, when I type in 10 for txtSortOrder, and then a value for txtCourse, Access creates a new blank record but with a txtSortOrder default value of 10, not 20. However, when I click on txtCourse for the new record, the default value then changes to 20. It appears that my code triggers once I select the new record but I want it to happen as soon as Access creates the new blank record for me.
I've tried adding this code to the form's AfterUpdate, AfterInsert, and BeforeInsert events. I also tried adding it to txtSortOrder's BeforeUpdate and AfterUpdate events. However, none give me the desired result. The default value for txtSortOrder only increments when I click on the new record. Is there a way to get what I want? If so, how?

[nico5038]

Dear bullfrog83,

I don't see a reason to store a value like this, as it's deductable from your table. Looks to me that using the DCOUNT() function in your txtSortOrder field will get the value displayed like:

=DCOUNT("id","tblID","where for grouping when needed") * 10
 

When you want to offer the user an additional way to change the sortorder you might be interested in my sample database.

See:
http://bytes.com/attachments/attachm...tright2000.zip

Nic;o)