Sum Query ?

[gregorg]

Hi I have a Sum Query called SumQuery [sumquery].

I want a text box [Text10] on a form [MainFrm]to fill with the result of the sum query [SumQuery] (one value only) when I click on a control on the form. The SumQuery uses a value off the form, a text box called [weekofyeartxt] as criteria to calculate the sum query result.

Can you help ?

Gregor.

 

[apr pillai]

I will show you a similar example so that you can do it yourself.

Sample Query that Sums up a Student's Total Marks of all subjects in one column, based on the Student's Name entered in a text box on the Form (say [WeekofYearText].

Sample Query: myQuery

SELECT Sum(tbl_Students.Marks) AS Marks
FROM tbl_Students
WHERE (((tbl_Students.Student)=[forms]![frm_Students]![WeekofYearText]));

The Query result will be available in the Column: Marks

To display that value in a Textbox (say [Text10]) on the Form when you click on a Command Button on the Form, you should do two things.

1. Write the following expression in the Control Source property of the [Text10] control on the Form:

=Dlookup("Marks","myQuery")

2. Create a Command Button on the Form, display the property sheet (F4), select [Event Procedure] from the drop-down control of On Click Event, click on the build button (. . .) to open the VBA Editing Window. Write the following statement in the middle of the empty Command Button Click event procedure:

Me.Refresh

When completed the VBA Procedure will look something like the code below:

Private Sub Command20_Click()

   Me.Refresh

End Sub

3. After the changes, open the Form and try out.