Count related sub datasheet records

  • Thread starter Thread starter Bradley C. Hammerstrom
  • Start date Start date
B

Bradley C. Hammerstrom

Is this possible?

A Form in datasheet view with a sub DS linked to the main DS (master/child
links).
Rather than user having to expand each record listed in the main DS to find
out if (and how many) records are linked in the sub DS, have a column in the
main DS that displays the Count of those records behind the 'plus' sign.

Wow, that would be handy.
Can it be done?

Brad H.
 
Haven't tried it but you can try a Calculated Control on the Main Form with
the ControlSource set to:

= [Form]![SubformControl].RecordsetClone.RecordCount

provided that you are correct in saying that it is a *Form* in DatasheetView
with a Subform in DatasheetView also.
 
Thanks Van,

I couldn't get your idea to work, but found this to work in the
ControlSource of the calculated textbox on the main form:

=DCount("[Defect_ID]","[qryTagObs]","[Defect_ID] = [DefectID]")

Where:
The main form is based on a table containing DefectID;
the subform's RecordSource is qryTagObs,
and Defect_ID is in qryTagObs and is the Child link to DefectID on the main
form.

Iterestingly, as I scroll down the main form's subdatasheet, the calculation
takes a second or two before becoming visible; but that's fine.

Brad H.
 
Yes, DCount should work fine but it is slightly less efficient since you
have to access the saved Records (i.e. hard-disk access) again. That's
probably why you see the delay.

Alternatively, you can try using an intermediate TextBox (txtInter) in the
Footer of the Subform using the ControlSource:

= Form.RecordsetClone.RecordCount

and then use the txtInter as the ControlSource of the TextBox in the main
Form.

= Form!SubformControl.Form!txtInter

(which is the technique used in the Orders Form / Orders Subform in the
sample database "NorthWind").
 
Back
Top