Originally Posted by
John_G
The problem here is that a form does not have set number of records - its underlying table does. The form will display all or only some of them, depending on filtering that is set.
I gather from what you said that when you insert a new record you want to have a field be assigned the value one higher than the current maximum, i.e. the next number is a sequence.
You can do this using VBA in the Before Insert event of your form, with the DMax function:
me![ID_Field] = nz(dmax("ID_Field","TableName"),0) +1
This finds the current maximum value of ID_Field, adds 1 to it, and puts the resulting value in the form field.
I used nz to cover the case where there are no existing records in the table.
One caveat with this is that if you create this new record, and then delete it, the next new record you create will have the same ID number. There are also potential problems if you have multiple users accessing the same table at the same time.
HTH
John