Always returns the same number?

[Tony Williams]

Can someone tell me why this expression always returns the same number?
=IIf(IsNull([txtcd1retd]),Null,DMax("txtrefnbr","tblcomplaints")+1)
I have the expression in the control source of the control txtrefnbr
Thanks
Tony

 

[J_Goddard via AccessMonster.com]

Hi -

Probably because the maximum value of [txtrefnbr] in the table [tblcomplaints]
never changes. It won't until a new record is added to the table with a
higher value of [txtrefnbr].

I can't say much more, because you have given us very little to go on. What
do you do with that control once it has received the value?

John

Can someone tell me why this expression always returns the same number?
=IIf(IsNull([txtcd1retd]),Null,DMax("txtrefnbr","tblcomplaints")+1)
I have the expression in the control source of the control txtrefnbr
Thanks
Tony

 

[Tony Williams]

Hi John incidentally thanks for your help yesterday.
What I'm trying to do is this
I have a control on a form [txtrefnbr] which is based on a field of the same
name in a table tblcomplaints. I have another control on the form called
[txtcd1retd] again based on a field of the same name in a table
tblcomplaints. When the control txtcd1retd is entered I want the value of
txtrefnbr to increase by 1 and for that control to be popultaed with the
next number. One problem is I see that to start with I haven't got a number
in there anyway so how do I kick of the numbering with the first one then
increase it on individual records as I input the value of txtcd1retd?
Hope that makes sense?
Thanks
Tony

Hi -

Probably because the maximum value of [txtrefnbr] in the table
[tblcomplaints]
never changes. It won't until a new record is added to the table with a
higher value of [txtrefnbr].

I can't say much more, because you have given us very little to go on.
What
do you do with that control once it has received the value?

John

Can someone tell me why this expression always returns the same number?
=IIf(IsNull([txtcd1retd]),Null,DMax("txtrefnbr","tblcomplaints")+1)
I have the expression in the control source of the control txtrefnbr
Thanks
Tony

 

[Tony Williams]

Just one thing data is input into txtcd1retd on existing records not new
ones.
Tony

Hi -

Probably because the maximum value of [txtrefnbr] in the table
[tblcomplaints]
never changes. It won't until a new record is added to the table with a
higher value of [txtrefnbr].

I can't say much more, because you have given us very little to go on.
What
do you do with that control once it has received the value?

John

Can someone tell me why this expression always returns the same number?
=IIf(IsNull([txtcd1retd]),Null,DMax("txtrefnbr","tblcomplaints")+1)
I have the expression in the control source of the control txtrefnbr
Thanks
Tony

 

[J_Goddard via AccessMonster.com]

Hi Tony -

Are you using the form to input new records to the table [tblcomplaints]? If
this is the case, how do you get the value of [txtrefnbr] into the new record
- the control is not bound to a table field.

If my guess is correct, try using the table field txtrefnbr as the control
source for [txtrefnbr], and then put me![txtrefnbr] = nz(DMax("txtrefnbr",
"tblcomplaints")+1),1) in the After Update event of [txtcd1retd].

John

Hi John incidentally thanks for your help yesterday.
What I'm trying to do is this
I have a control on a form [txtrefnbr] which is based on a field of the same
name in a table tblcomplaints. I have another control on the form called
[txtcd1retd] again based on a field of the same name in a table
tblcomplaints. When the control txtcd1retd is entered I want the value of
txtrefnbr to increase by 1 and for that control to be popultaed with the
next number. One problem is I see that to start with I haven't got a number
in there anyway so how do I kick of the numbering with the first one then
increase it on individual records as I input the value of txtcd1retd?
Hope that makes sense?
Thanks
Tony

Hi -

[quoted text clipped - 14 lines]

 

[J_Goddard via AccessMonster.com]

Sorry - posted my reply before I saw your note.

What are you wanting to do when you input data into txtcd1retd on an existing
record - i.e. you are updating the record. Do you want to update the
txtrefnbr field in the SAME record so that it now has the maximum value for
the table?


John

Just one thing data is input into txtcd1retd on existing records not new
ones.
Tony

Hi -

[quoted text clipped - 14 lines]

 

[Tony Williams]

Hi John. The txtcd1retd is a date field where we input the date a form
(paper form!) is returned to us in the appropriate record. When we input the
return date we want to allocate a reference number ie txtrefnbr, so by
putting in the date we want txtrefnbr to be populated with the next value. I
have some other controls that then uses the value of txtrefnbr to build up
another control, which I can do as long as I can get the value of txtrefnbr.
So I think my answer to your question is yes.
Thanks
Tony

Sorry - posted my reply before I saw your note.

What are you wanting to do when you input data into txtcd1retd on an
existing
record - i.e. you are updating the record. Do you want to update the
txtrefnbr field in the SAME record so that it now has the maximum value
for
the table?


John

Just one thing data is input into txtcd1retd on existing records not new
ones.
Tony

Hi -

[quoted text clipped - 14 lines]

Thanks
Tony

 

[J_Goddard via AccessMonster.com]

Hi Tony -

All you need to to then is put this in the After Update event of [txtcd1retd]:


me![tstrefnbr]=DMax("txtrefnbr","tblcomplaints")+1)
me.refresh

me.refresh forces an immediate update to the underlying table.


Note that the table tblcomplaints will only contain a reference to the latest
complaint received about a particular member/associate. If you want to
retrieve all the complaints about a specific member, you will need additional
table structures.

From what I can determine, you need two tables - one for the details of the
members/associates, and another for the specific complaints. They would be
related by the membership number.

John

Hi John. The txtcd1retd is a date field where we input the date a form
(paper form!) is returned to us in the appropriate record. When we input the
return date we want to allocate a reference number ie txtrefnbr, so by
putting in the date we want txtrefnbr to be populated with the next value. I
have some other controls that then uses the value of txtrefnbr to build up
another control, which I can do as long as I can get the value of txtrefnbr.
So I think my answer to your question is yes.
Thanks
Tony

Sorry - posted my reply before I saw your note.

[quoted text clipped - 15 lines]

 

[Tony Williams]

Thanks John that did it!
Tony

Hi Tony -

All you need to to then is put this in the After Update event of
[txtcd1retd]:


me![tstrefnbr]=DMax("txtrefnbr","tblcomplaints")+1)
me.refresh

me.refresh forces an immediate update to the underlying table.


Note that the table tblcomplaints will only contain a reference to the
latest
complaint received about a particular member/associate. If you want to
retrieve all the complaints about a specific member, you will need
additional
table structures.

From what I can determine, you need two tables - one for the details of
the
members/associates, and another for the specific complaints. They would
be
related by the membership number.

John

Hi John. The txtcd1retd is a date field where we input the date a form
(paper form!) is returned to us in the appropriate record. When we input
the
return date we want to allocate a reference number ie txtrefnbr, so by
putting in the date we want txtrefnbr to be populated with the next value.
I
have some other controls that then uses the value of txtrefnbr to build up
another control, which I can do as long as I can get the value of
txtrefnbr.
So I think my answer to your question is yes.
Thanks
Tony

Sorry - posted my reply before I saw your note.

[quoted text clipped - 15 lines]

Thanks
Tony