Default value for a new record

[Guest]

I am trying to create a new record that will have a unique numeric ID.
However, AutoNumber was not used for the ID field when the DB was created,
and I cannot change it. I was wondering if there was a way to find the
highest current value of all the IDs in order to determine which number
should be the next one used (Example: 1501 is the highest current value, and
I need 1502 to be the default for the next new record). I have tried using
the Max() function to get the highest value of the ID number, but it always
returns 0 for the value.

If anyone has any suggestions they would be most appreciated. Thank you
very much.

 

[Rick Brandt]

I am trying to create a new record that will have a unique numeric ID.
However, AutoNumber was not used for the ID field when the DB was
created, and I cannot change it. I was wondering if there was a way
to find the highest current value of all the IDs in order to
determine which number should be the next one used (Example: 1501 is
the highest current value, and I need 1502 to be the default for the
next new record). I have tried using the Max() function to get the
highest value of the ID number, but it always returns 0 for the value.

If anyone has any suggestions they would be most appreciated. Thank
you very much.

Not Max(), but DMax().

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

However; using that as the DefaultValue property will only work in a single view
form with a single user adding records or else you will get duplicate value
collisions. If you need multi-user capability use the BeforeUpdate event rather
than the DefaultValue.

 

[Guest]

Not Max(), but DMax().

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

However; using that as the DefaultValue property will only work in a single view
form with a single user adding records or else you will get duplicate value
collisions. If you need multi-user capability use the BeforeUpdate event rather
than the DefaultValue.

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


Thank you very much, Rick. THat is very helpful

 

[Guest]

Not Max(), but DMax().

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

However; using that as the DefaultValue property will only work in a single view
form with a single user adding records or else you will get duplicate value
collisions. If you need multi-user capability use the BeforeUpdate event rather
than the DefaultValue.

I tried using the BeforeUpdate mentioned above, but i was given an error
because the ID is a required field, and was being inserted with the creation
of the new record.