Dmax problem

[Tony Williams]

I have a control, txtmemnumber, which is a number and which I want to advance
the last value by 1 when I create a new record. I have used the Dmax function
in the Default value like this:

=Nz(DMax("txtnextnbr","tblmemnbrs",""),0)+1

However it doesn't work. I want the numbers to start at 2001 so have made
the current value of txtnextnbr as 2000. When I create a new record, the
value of txtmemnumber is always 2001. Can someone point me in the right
direction please?
Tony

 

[BruceM]

The optional Where condition for DMax (an empty string) is never being met.
Just leave it out and you should be OK:
=Nz(DMax("txtnextnbr","tblmemnbrs"),0)+1

BTW, you could have used 2000 as the Nz value:
=Nz(DMax("txtnextnbr","tblmemnbrs"),2000)+1

This would have started you at 2001.

 

[Tony Williams]

Thanks Bruce. I've used your expression and it still wont work? I'm putting
the expression in the default value of txtmemnumber, the control on the form.
Is that the right place? The value of txtnextnbr in the table tblmemnbrs
isn't changing?
Any ideas?
Thanks
Tony

 

[BruceM]

It should be the right place if the text box txtmemnumber is bound to the
txtnextnbr field. Remember that Default Value applies only to new records,
so old records will not be changed.

 

[Tony Williams]

Ah that might be the problem the control txtmemnumber is bound to the field
txtmemnumber in the table tblindividual? I obviously need to revisit the
structure of the form?
Thanks Bruce
Tony

 

[BruceM]

I find it helps me keep things straight in my mind if the field and the
control have different names, but in most cases it won't matter (although it
is possible for Access to become confused).
Anyhow, if you need to increment the txtnextnbr field you need to do so in a
control bound to txtnextnbr. If you increment a field (txtmemnumber) based
on a value in another field (txtnextnbr), the next time you increment the
number you will get the same result unless the highest value for txtnextnbr
has changed for some other reason.

I would say that yes, you need to revisit your form's structure, or
something, but without knowing something of your database's structure or
purpose it is difficult to say more. What is the form's Record Source?

 

[Tony Williams]

The forms record source is a query which is based on two joined tables.
I'll play around with the form and the relationship of the field and control
as you said and see how I get on. I'll come back to you if I still struggle.
Thanks for your help.
Tony