I have a table with a number field. I would like to add a new record that
looks at the last record and auto generates a number of the last record plus
1.

Last record : 1000
New record: 1001

How can I do this? Does the field type have to be an auto number?

P.s. I'm using a form to create the new record and enter the data. I need
the form field to display the new auto generated number as well. Please advise

Whitney wrote:
> I have a table with a number field. I would like to add a new record
> that looks at the last record and auto generates a number of the last
> record plus 1.
>
> Last record : 1000
> New record: 1001
>
> How can I do this? Does the field type have to be an auto number?
>
> P.s. I'm using a form to create the new record and enter the data. I
> need
> the form field to display the new auto generated number as well.
> Please advise

You can set the DefaultValue property of the TextBox on your form to...

=DMax("FieldName", "TableName") + 1

That will NOT work if you have multiple users simultaneously entering
records and it will NOT work on a continous or datasheet view form. For
both of those situations you need to wait until the BeforeUpdate event to
grab the value and assign it to the control. That means that the user will
not see the value until after they save the new record...

If Me.NewRecord Then
Me.TextBoxName =DMax("FieldName", "TableName") + 1
End If

If you need either of those to work on a completely empty table with no
existing records then you would need to add the Nz() function like...

=Nz(DMax("FieldName", "TableName"),0) + 1

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com