Unanswered: Form Field Calculated from Child Form Data
I have a form (Direct Payments 2005, based on table Enquiry 2005) that contains a child form (Child81, contains form Payment Events 2005 which is based on table Payment Events, linked to master form via [Payment Events].LocationFK = [Enquiry 2005].LocationID and [Payment Events].EnquiryFK = [Enquiry 2005].EnquiryID). I need to display some aggregate data on the main form, based on the child form's data (need to show number of visits and total time spent where action type = home visit, e.g., 4 visits, 8 hours). I tried using VBA to load and display the data using the Form.Current event, but for some reason it always displayed data from the last record I was on, not the current one. So now I'm trying to calculate it directly within the "Control Source" property of the text box. Here is my current query:
SELECT COUNT([Payment Events].Action) & ' Visits, ' & SUM([Payment Events].TimeSpent) & ' hours' AS TotalVisit FROM [Payment Events] WHERE [Payment Events].LocationFK = [LocationID] AND [Payment Events].EnquiryFK = [EnquiryID] AND [Payment Events].Action = 'Home Visit' GROUP BY [Payment Events].Action, [Payment Events].TimeSpent;
If I load this SQL into a fresh Query object and plug some values into [LocationID] and [EnquiryID], it works just fine. What I can't seem to do is get it to pull those two values from the current record of the form. All I get is #Name? in the text box when I run the form.
Help solving either the Form.Current problem or the #Name? problem would be greatly appreciated. Thanks in advance.