#Name? Error setting numeric default value

[Daryl]

I have the following problem:

My clients need to have an autonumber Id, but they want to be able to
set the value to arbitrary numbers nonetheless. Thus, what they really
want is for the default value in the input form to be the next logical
Id.

Basically I have my ID as a "number", and then in the input form I set
the default value for the text box to be "=[select max(ID)+1 from
Items]". However, when I go to add a new record in the form (i.e. click
the ">*" button), I get "#Name?" in the text box instead of the next
ID.

I know I have done this before and it has worked for other clients, but
today I can't seem to get it to work. Does anybody have any idea what
could be wrong?

(Could it be that the default value doesn't work because ID is a
number? If so, how do I get this to work?)

 

[Marshall Barton]

My clients need to have an autonumber Id, but they want to be able to
set the value to arbitrary numbers nonetheless. Thus, what they really
want is for the default value in the input form to be the next logical
Id.

Basically I have my ID as a "number", and then in the input form I set
the default value for the text box to be "=[select max(ID)+1 from
Items]". However, when I go to add a new record in the form (i.e. click
the ">*" button), I get "#Name?" in the text box instead of the next
ID.

You can not use SQL as a default value. Use DMax for this
purpose:

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

 

[Daryl]

Okay, thank you very much! That worked perfectly.

I had searched the group for the solution, but didn't find it. Thanks
for helping!

 

[Daryl]

Ah, I see. Thanks!

Your code will work after the first row is in the table. With an empty
table, it will always return Null and no value will be generated. That is
why I use the Nz function to handle the first record situation.

My clients need to have an autonumber Id, but they want to be able to
set the value to arbitrary numbers nonetheless. Thus, what they really
want is for the default value in the input form to be the next logical
Id.

Basically I have my ID as a "number", and then in the input form I set
the default value for the text box to be "=[select max(ID)+1 from
Items]". However, when I go to add a new record in the form (i.e. click
the ">*" button), I get "#Name?" in the text box instead of the next
ID.

You can not use SQL as a default value. Use DMax for this
purpose:

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