How to use value selected in combobox with a button to show a total

[Diana Miller]

Hi All,

I am creating a report from Access 2007. I have a form where the user selects a Month from a combo box and then presses a button to show the sum of all pages worked for the Month selected in the combo box. I am populating the combo box from the database and everything there works great.

My problem is this: How do I code the button/combo box to take the value selected by the user in the combo box and then calculate the total number of pages worked for that month and output it into a text field on the report?

My combo box is named "SelectYearMont h" and is populated by the field "YEARMONTH" in my database. I have this part working great.

My button is named "ShowPageTotalB utton" and I need it to calculate a total from the field "PAGES" for every record for the month selected.
The calculation I am performing is:

   =Sum([Pages])

Both the combo box and the button are located in the header of the report.

The text box that I want the calculation of total pages to appear in is called "PageTotalTextB ox".

This text box is located in the footer of the report as I am hiding the details section since there are thousands of records.

Any help is greatly appreciated.

Thanks,
Diana

[gnawoncents]

Diana,

you don't even need the button, if you don't want it. In the after update event of your combo box, add the following code:

Me.PageTotalTextBox = DSum("Pages", "YourTableName", "YEARMONTH = '" & [SelectYearMonth] & "'")

Of course, you'll need to change "YourTableN ame" to the actual name of your table. Also, if you don't store the month as a text value, you will need to remove the single quote marks for a numerical value or replace with pound signs (#) for a date. Let me know if you have any questions.

[Diana Miller]

Excellent Answer!

That's it! You have solved my dilemma. I had to take out the single quote marks and it works great. Thanks you so much for making this easy!

[gnawoncents]

Glad to help :)