Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Stuck in Michigan
    Posts
    4

    Exclamation 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.

  2. #2
    Join Date
    May 2003
    Location
    Stuck in Michigan
    Posts
    4
    Fixed it! I put the query code into the Form.Current event for the CHILD form instead of the Parent form. Now it works!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •