Count records in another table

[Linda]

I have a "Project Action" form with a button that opens another form, the
"Risk Form", using a DoCmd.OpenForm command. The records displayed in the
new form have an associated Project Action number that is the same as the
Project Action number in the calling form. The users would like the Project
Action form to indicate if any matching risk records exist without having to
actually open the risk form. How would I do that?

 

[Rick Brandt]

I have a "Project Action" form with a button that opens another form,
the "Risk Form", using a DoCmd.OpenForm command. The records displayed
in the new form have an associated Project Action number that is the
same as the Project Action number in the calling form. The users would
like the Project Action form to indicate if any matching risk records
exist without having to actually open the risk form. How would I do
that?

You can use the DCount() to do this.

DCount("*", "TableName", "Where clause")

 

[Linda]

I created a text box on my Project Action form. In the record source I put
=DCount("*","Risk_Tracker","Assoc_PA_Num=Me.[SPR Number]")
where Risk_Tracker is the form I want to count records in.
Assoc_PA_Num is the field in the Risk_Tracker form that I want to equal the
value in the SPR Number field on the currently opened form.
I get a#Error in the text box.
What have I done wrong?
Thanks,

 

[Linda]

My follow-up question wasn't right. Risk_Tracker is a table. I can't seem
to get a query right or the record source right to return the number of
records in the table that match the ID of the current record from the Project
Action record being displayed in the form. I either get a #Error or the
total number of records in the Risk_Tracker table. Can anyone clear this up
for me?
Thanks,
Linda
--
Linda

I created a text box on my Project Action form. In the record source I put
=DCount("*","Risk_Tracker","Assoc_PA_Num=Me.[SPR Number]")
where Risk_Tracker is the form I want to count records in.
Assoc_PA_Num is the field in the Risk_Tracker form that I want to equal the
value in the SPR Number field on the currently opened form.
I get a#Error in the text box.
What have I done wrong?
Thanks,
--
Linda

You can use the DCount() to do this.

DCount("*", "TableName", "Where clause")