DCount error

[Linda]

I have a form called PA. It contains a text box called SR Number(it is
text). It also has a button which opens a Risk form when clicked. The Risk
forms have a text box call Assoc_PA which is the same value as the SR Number.
I would like to have a text box, called Rsk_Cnt, next to the Open Risk Form
button that displays the number of risk forms that have the Assoc_PA equal to
the SR Number. The table which the risk form gets its data from is called
Risk_Tracker. So I've tried to use the following statement as the control
source for Rsk_Cnt
=DCount("*","Risk_Tracker","Assoc_PA = Me.[SR Number]")
but I get #Error in the Rsk_Cnt text box. Please tell me what I'm doing
wrong.
Thanks.

 

[Douglas J. Steele]

You can't use the Me keyword in a ControlSource.

Try:

=DCount("*","Risk_Tracker","Assoc_PA = [SR Number]")

If that doesn't work, you may need to put the reference to the control
outside of the quotes. If Assoc_PA is a numeric field, try

=DCount("*","Risk_Tracker","Assoc_PA = " & [SR Number])

If it's a text field, use

=DCount("*","Risk_Tracker","Assoc_PA = '" & [SR Number] & "'")

Exagerated for clarity, that's

=DCount("*","Risk_Tracker","Assoc_PA = ' " & [SR Number] & " ' ")

 

[Rod Plastow]

You can't use the Me keyword in a ControlSource.

Er, you can, provided it's outside the quotation marks and concatenated via
'&'

=DCount("*","Risk_Tracker","Assoc_PA = " & Me.[SR Number])

will work assuming [SR Number] is numeric.

Apologies for being 'picky.'

Rod

 

[Rod Plastow]

Belay that! Just realised we're referring to the property sheet entries where
Me does not work.

Rod

 

[Linda]

Here is a cut and paste of what I now have in my control source. Assoc_PA is
a text field as is SR Number. I still get the #Error in the text box on the
form. Any other ideas?
=DCount("*","Risk_Tracker","Assoc_PA = '" & [SR Number] & "'")
Thanks,
--
Linda

You can't use the Me keyword in a ControlSource.

Try:

=DCount("*","Risk_Tracker","Assoc_PA = [SR Number]")

If that doesn't work, you may need to put the reference to the control
outside of the quotes. If Assoc_PA is a numeric field, try

=DCount("*","Risk_Tracker","Assoc_PA = " & [SR Number])

If it's a text field, use

=DCount("*","Risk_Tracker","Assoc_PA = '" & [SR Number] & "'")

Exagerated for clarity, that's

=DCount("*","Risk_Tracker","Assoc_PA = ' " & [SR Number] & " ' ")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a form called PA. It contains a text box called SR Number(it is
text). It also has a button which opens a Risk form when clicked. The
Risk
forms have a text box call Assoc_PA which is the same value as the SR
Number.
I would like to have a text box, called Rsk_Cnt, next to the Open Risk
Form
button that displays the number of risk forms that have the Assoc_PA equal
to
the SR Number. The table which the risk form gets its data from is called
Risk_Tracker. So I've tried to use the following statement as the control
source for Rsk_Cnt
=DCount("*","Risk_Tracker","Assoc_PA = Me.[SR Number]")
but I get #Error in the Rsk_Cnt text box. Please tell me what I'm doing
wrong.
Thanks.

 

[Douglas J. Steele]

So Assoc_PA is a text field? I can't see any reason why that wouldn't be
working. What value is in [SR Number]?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Here is a cut and paste of what I now have in my control source. Assoc_PA
is
a text field as is SR Number. I still get the #Error in the text box on
the
form. Any other ideas?
=DCount("*","Risk_Tracker","Assoc_PA = '" & [SR Number] & "'")
Thanks,
--
Linda

You can't use the Me keyword in a ControlSource.

Try:

=DCount("*","Risk_Tracker","Assoc_PA = [SR Number]")

If that doesn't work, you may need to put the reference to the control
outside of the quotes. If Assoc_PA is a numeric field, try

=DCount("*","Risk_Tracker","Assoc_PA = " & [SR Number])

If it's a text field, use

=DCount("*","Risk_Tracker","Assoc_PA = '" & [SR Number] & "'")

Exagerated for clarity, that's

=DCount("*","Risk_Tracker","Assoc_PA = ' " & [SR Number] & " ' ")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a form called PA. It contains a text box called SR Number(it is
text). It also has a button which opens a Risk form when clicked. The
Risk
forms have a text box call Assoc_PA which is the same value as the SR
Number.
I would like to have a text box, called Rsk_Cnt, next to the Open Risk
Form
button that displays the number of risk forms that have the Assoc_PA
equal
to
the SR Number. The table which the risk form gets its data from is
called
Risk_Tracker. So I've tried to use the following statement as the
control
source for Rsk_Cnt
=DCount("*","Risk_Tracker","Assoc_PA = Me.[SR Number]")
but I get #Error in the Rsk_Cnt text box. Please tell me what I'm
doing
wrong.
Thanks.