Count records from different table

[Guest]

Not sure if this is even do-able or not.

I have a form (lets call it FORM A) that has details information about each
Case. Each case can have multiple Collection records.

I want text box on FORM A that just tells me the number of Collection
records with the same Case number.

I tried DCount in a new unbound text box, but i just get '#Error'.

Any suggestions?

 

[SusanV]

You can use an unbound textbox, and in the form's On Current event open a
recordset of the table, then populate the textbox with the recordcount from
that recordset.

For example, if your other table was called TBL and you want to show the
number of records in TBL in a textbox called txtBox on a form based on the
table TBL123:

Dim cnx As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cnx = CurrentProject.Connection

rs.Open "TBL", cnx, adOpenKeyset, adLockOptimistic, adCmdText
Me.txtBox = rs.RecordCount

If you wanted to count the number of records with a where condition, you can
either use a stored query or a SQL statement in place of the table name
("TBL")

 

[fredg]

Not sure if this is even do-able or not.

I have a form (lets call it FORM A) that has details information about each
Case. Each case can have multiple Collection records.

I want text box on FORM A that just tells me the number of Collection a
records with the same Case number.

I tried DCount in a new unbound text box, but i just get '#Error'.

Any suggestions?

DCount is just fine, but you didn't tell us what your DCount syntax
was so it's hard to guess what your difficulty is.
Try something like this:

=DCount("*","TableName","[CaseNumber] = " & Me.[CaseNumberOnForm])

The above assumes that [CaseNumber] is a Number datatype.

If, in fact, [CaseNumber] is a Text datatype, then use:

=DCount("*","TableName","[CaseNumber] = '" & Me.[CaseNumberOnForm] &
"'")

Change the table and field names as appropriate.

Make sure the control name is not the same as the name of any field
used in it's control source expression.