Setting the default value for a data field using a calculated valu

[Craig]

I am trying to set up a data field on a form so that when the "NEW" record
button is selected the value of a key data field is incremented automatically
from the previous value of that field without user input. The value of this
field needs to start at 300000. I have tried using the Expression Builder to
set the value using "Max([Data]![NO]) + 1", "Count([Data]![NO])+300000"; when
the "New" record button is selected I get 1 and 300000 respectively. I was
expecting the Max function to look at all the data in the NO column and
return the max value in this expression. I was expecting the Count function
to return the number of records for the NO column. Is there anyway to have a
field increment itself from a user defined starting point. Once again this
field is intended to be the key value for the record.

Any suggestions would be greatly appreciated.

 

[John W. Vinson]

I am trying to set up a data field on a form so that when the "NEW" record
button is selected the value of a key data field is incremented automatically
from the previous value of that field without user input. The value of this
field needs to start at 300000. I have tried using the Expression Builder to
set the value using "Max([Data]![NO]) + 1", "Count([Data]![NO])+300000"; when
the "New" record button is selected I get 1 and 300000 respectively. I was
expecting the Max function to look at all the data in the NO column and
return the max value in this expression. I was expecting the Count function
to return the number of records for the NO column. Is there anyway to have a
field increment itself from a user defined starting point. Once again this
field is intended to be the key value for the record.

Any suggestions would be greatly appreciated.

Rather than *counting* records, which will basically give you nightmares if
you ever have to delete a record, look up the largest existing value. Use the
Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![NO] = NZ(DMax("[NO]", "[tablename]"), 299999)) + 1
End Sub