calculate an autonumber that isn't the primary key

[Guest]

access only allows one autonumber field per table, which is my primary key
field, but i have another number field in the table that i'd like to
automatically populate based on the previous record's value in this field.

The users should be able to edit this field as well.

Is this an aggregate function situation?

Many thanks for any help ~

 

[Ken Snell [MVP]]

You need to define what is meant by "previous record", but let's assume that
you're incrementing the value in that field for each new record.

Use a form for data entry. In that form, for the control that is bound to
this second field, set the Default Value property to this:

=Nz(DMax("NameOfField","NameOfTable"),0)+1

This will put the number in the control (when you create a new record) that
is the next sequential number for the table. The user can modify that value
in the form if desired.

Note that the above expression can be modified to further filter the DMax
function (a third argument for the function) if you wish to use specific
criteria for which field combinations you wish to use.