Count of related records

[cbende2]

Hello all,

I'm having trouble trying to get what I'd like.

In an ideal world, on my continuous form, I will have an unbound textbox that gets the count of each records ID in a different table.

Background info:
1) I have two tables, table1 and table2.
2) table2 is joined to table1 with a (1 to many) relation.


3) table1 is bound to a continuous form.
4) unbound textbox on continuous form pulls count of how many records are related in table2.

So I'm guessing it would be something like....

txtbox1 = Dcount(IDField, table2, IDField FROM table1 = IDField FROM table2)

Thanks

[JoeM]

Why not just do an Aggregate Query between Table1 and Table2, getting your counts (using the Count function).
Then use this Query as the Control Source for your Form.

As long as you are using this Form for viewing/reporting and not editing, I think it should work.

[cbende2]

I will need to be able to edit data in the continuous form. I got the aggregate query working giving me the correct counts.

Is there anyway I can use an unbound txtbox to pull from this query where ID on continuous form is = to ID in aggregate query?

EDIT:

I can't use it as Control Source because not all records in table1 have related records in table2, therefor I would be missing records in the continuous form.

[JoeM]

EDIT:

I can't use it as Control Source because not all records in table1 have related records in table2, therefor I would be missing records in the continuous form.

Not if you use a Left Join instead of an Inner Join! It will return ALL the records from Table1.
However, if you need to be able to update the Form, my original idea probably will not work, since Aggregate Queries are not updateable. So we can not use an Aggregate Query as the Control Source of the Form.

Going back to your original idea, we should be able to use DCOUNT as the source of a unbound Text Box. I think you just need to change your syntax a little, like this:

=DCount("IDField","Table2","IDField='" & [IDField] & "'")

(assuming that your IDField in Table1 and Table2 have the same name.

Here is a good write-up on DCOUNT, for your reference: http://www.techonthenet.com/access/f...ain/dcount.php

[cbende2]

I'm getting a #Name? error.

I'll give you my actual table names and field names so you can diagnose problem.

Table1(table bound to continuous form): FundAddition&TransferChecklist
IDField in table1: [ID]

Table2: FundAttachmentTable
IDField in table2: [FundID]

=DCount([FundID],[FundAttachmentTable],"FundID = '" & [ID] & "'")

EDIT:


After changing code to this:

=DCount("FundID","FundAttachmentTable","FundID = '" & [ID] & "'")

Im getting #Error now in the textbox.

[JoeM]

What is the data type of the "ID" field?
Is it numeric or text?

[cbende2]

It's Numeric.

Both fields are numeric. [ID] is an autonumber field, and [FundID] is a Number field.

[JoeM]

Then you want to remove the text qualifiers (single-quotes) from the third argument, i.e.

=DCount("FundID","FundAttachmentTable","FundID=" & [ID])

[cbende2]

Yes!

That works perfect.

Thank you JoeM!