Incrementing without Autonumber, always enters the same value twice

[ClarCE]

Hello,

I've been up and down these forums looking for a solution to this
issue, but so far have had no luck.

I need to automatically assign serial numbers starting from 1 and then
incrementing by 1 as each new record is entered.

I've tried all manner of variations to this line:

=nz(dmax("Serial Number","Faults"),0)+1

trying it in both control set, record set and default value, but it
always adds 2 records with the same number, ie:

Name Serial
xx 1
xx 1
xx 2
xx 2

and so on, its very odd and highly frustrating! Any ideas would be
much appreicated...

Cheers,

Chris.

 

[Rick Brandt]

Hello,

I've been up and down these forums looking for a solution to this
issue, but so far have had no luck.

I need to automatically assign serial numbers starting from 1 and then
incrementing by 1 as each new record is entered.

I've tried all manner of variations to this line:

=nz(dmax("Serial Number","Faults"),0)+1

trying it in both control set, record set and default value, but it
always adds 2 records with the same number, ie:

Name Serial
xx 1
xx 1
xx 2
xx 2

and so on, its very odd and highly frustrating! Any ideas would be
much appreicated...

Cheers,

Chris.

This is what happens on a continuous or datasheet form if you are using the
default value property because as soon as you dirty a new row *another* new row
is generated beneath it and since the record you are working in hasn't been
saved yet the "new new" row gets the same default value.

Use the BeforeUpdate event of the form. It not only avoids this problem but
handles concurrency better than any other event you could pick.

 

[chrisc]

This is what happens on a continuous or datasheet form if you are using the
default value property because as soon as you dirty a new row *another* new row
is generated beneath it and since the record you are working in hasn't been
saved yet the "new new" row gets the same default value.

Use the BeforeUpdate event of the form. It not only avoids this problem but
handles concurrency better than any other event you could pick.

Thanks for that, I can see why from what you've said.

However it now doesnt do anything, is there some variation on the
expression if I put it in before update?

Many thanks,

Chris.

 

[Rick Brandt]

Thanks for that, I can see why from what you've said.

However it now doesnt do anything, is there some variation on the
expression if I put it in before update?

Did you create a BeforeUpdate code routine in the VBA window or did you just
enter that expression into the BeforeUpdate event on the property sheet
(that only works with macros names or function names). For code you have to
enter "[Event Procedure]" in the property and then click the build [...]
button to get to the VBA window and then put the expression there.