Any way to get a count of matching records from another table while in a continuous form

[tagteam]

I have a continuous form that has a car id in the form. I would like to get a count of the matching car id's in another table based on the active records car id.



For example:
Each line item in the continuous form has an car id. I would like to count the number of active records in another table based on the active car id.

[June7]

Sounds like need a domain aggregate function like DCount(). Search Access Help or Google for specifics on domain aggregate functions.

[John_G]

If you are not going to be updating the data in that form, the other way you could do this is base the form on a Totals query linking the two tables, and which has a Count on the CarID from the second table, and Group By on all the fields from the first table.

[tagteam]

I cant use dcount because it is on a continuous form and the value I want to use as a filter is the field value on the current line in the continuous form.

So in my continuous form each line is for a car. I want to use that car id as a filter to get a count of the records in an alerts table. So in laymens terms: if current line is for car 12 then look at the alerts table and show me how many total alerts there are for car 12 and display it.

[June7]

Yes, so in a textbox next to that carID textbox: =DCount("*", "tablename", "carID=" & [carID])

Is carID a number type field?

[tagteam]

Ok, thanks, well that works. I was told previously that a dsum or count wont work on a continuous form. I guess that is wrong or only in certain cases.

[June7]

It would not work if you used VBA code to calculate with the DSum and set textbox Value property. Every record would show the same value because there is only the one control. Property settings are reflected in every instance of a control. Expression in ControlSource property allows dynamic reference to the carID by concatenating. The carID is treated as a variable.