Dmax problem

[Tony Williams]

I have a control called txtrefnbr which I want to be populated by the next
number from a table tblcompnbrs providing the value of txtcd1retd is not
null I have this in the control source
=IIf(IsNull([txtcd1retd])," ",Nz(DMax("[txtnextnbr]","tblcompnbrs"),0)+1)
So if [txtcd1retd] is null the value of txtrefnbr is null, if [txtcd1retd]
is not null I want to use the next number from the next value of txtnextnbr
from the table tblcompnbrs.
However all my numbers are 1 and the value of txtnextnbr is not changing.
What am I doing wrong?
Thanks
Tony

 

[Jeff Boyce]

Tony

You mention a table, a field, a form and a control.

Does the form's control "know" which table field it belongs to? (this is
the field to which the control is "bound")

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

If you want Null to be returned when txtcd1retd is Null, you need to return
Null, not " ":

=IIf(IsNull([txtcd1retd]),Null,Nz(DMax("[txtnextnbr]","tblcompnbrs"),0)+1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a control called txtrefnbr which I want to be populated by the next
number from a table tblcompnbrs providing the value of txtcd1retd is not
null I have this in the control source

So if [txtcd1retd] is null the value of txtrefnbr is null, if
[txtcd1retd]

is not null I want to use the next number from the next value of
txtnextnbr from the table tblcompnbrs.
However all my numbers are 1 and the value of txtnextnbr is not changing.
What am I doing wrong?
Thanks
Tony

 

[Tony Williams]

Thanks Douglas

If you want Null to be returned when txtcd1retd is Null, you need to
return Null, not " ":

=IIf(IsNull([txtcd1retd]),Null,Nz(DMax("[txtnextnbr]","tblcompnbrs"),0)+1)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a control called txtrefnbr which I want to be populated by the next
number from a table tblcompnbrs providing the value of txtcd1retd is not
null I have this in the control source

So if [txtcd1retd] is null the value of txtrefnbr is null, if
[txtcd1retd]

is not null I want to use the next number from the next value of
txtnextnbr from the table tblcompnbrs.
However all my numbers are 1 and the value of txtnextnbr is not changing.
What am I doing wrong?
Thanks
Tony

 

[Tony Williams]

Hi Jeff, sorry I didn't get back last night I'm in the Uk and it was getting
late.
The form control txtrefnbr is bound to a field with the same name in
tblcomplaints. The field where I'm holding the consecutive numbers is
txtnextnbr and is in the table tblcompnbrs. I'm getting a number generated
in txtnextnbr but its value is always 1?
Any help?
Tony

Tony

You mention a table, a field, a form and a control.

Does the form's control "know" which table field it belongs to? (this is
the field to which the control is "bound")

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a control called txtrefnbr which I want to be populated by the next
number from a table tblcompnbrs providing the value of txtcd1retd is not
null I have this in the control source
=IIf(IsNull([txtcd1retd])," ",Nz(DMax("[txtnextnbr]","tblcompnbrs"),0)+1)
So if [txtcd1retd] is null the value of txtrefnbr is null, if
[txtcd1retd] is not null I want to use the next number from the next
value of txtnextnbr from the table tblcompnbrs.
However all my numbers are 1 and the value of txtnextnbr is not changing.
What am I doing wrong?
Thanks
Tony