Calculated Controls on a Filtered Form (1 Viewer)

[jleval]

I have something that is rather complicated and has been eating me up all night.

I have a form with a sub form. I need to make some calculations off of the sub form that are displayed on the main form. These calculations are based on tool measurements.

On my form there are 5 separate bins that separate tool measurements fall into. When tools fall into a bin, they are assigned a number. Bin 1, Bin 2, Bin 3, Bin 4, and Bin 5.

I need to be able to make calculations for two separate avgs on each bin, & counts for the amount of tools in each bin. I have a query that works perfectly for this on another form, but it wont work on this one because the records are being filtered. This is really driving me crazy.

I tried the DCount([SortBin],[SubForm],[SortBin] = 1) thinking that I could get this to somehow count the number of records that fall into the sort bin.

Is there a way to make a qry that would be based on the records being shown in the current form.

The Qry in the other form goes like this:

SELECT CutterToolMeasurement.SortBin, CutterToolMeasurement.BatchID, Avg(CutterToolMeasurement.DiaMax) AS AvgOfDiaMax, Avg(CutterToolMeasurement.TIR) AS AvgOfTIR, Count(CutterToolMeasurement.ToolID) AS ToolsPerBin
FROM CutterToolMeasurement
GROUP BY CutterToolMeasurement.SortBin, CutterToolMeasurement.BatchID
ORDER BY CutterToolMeasurement.SortBin;

However, this is not based on a filtered form.

Is there someway to have a subform within the form that uses the controls current values as its query parameters?

Attached is a jpeg of the original forms calculated controls. These records are based on a batch. What I would like to do is have these same calculations be based on the current form


If this is confusing, please feel free to ask any questions you need to ask. Thank you for you help

 

[Galaxiom]

The LinkMasterFields of the subformcontrol can be a calculated control/field. It should work with the reference to the control/field as the criteria in the subform's RecordSource query or SQL.

You will need to Requery the subform when the calculated control/field changes probably using the OnCurrent Event of the main form.