How to use the value selected in a combobox to update multiple records in a report?

[Diana Miller]

Hi All,

I have an Access 2007 report where the user selects a date from a combo box. After selecting the date, I need the AfterUpdate() event of the combo box to fill in text fields with a calculated total for that month.

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

The report is grouped by "YEARMONTH" and then by "JOBTYPE"

The field "JOBTYPE" is populated by the field "JOBTYPE" in the database. The "JOBTYPE" appears in the "JOBTYPE" header of the report. I also have this part working great.

Here is where I run into problems: Each "JOBTYPE" has a "PAGES" numeric value associated with it. In the "JOBTYPE" footer, I need to calculate the total number of "PAGES" for the "JOBTYPE" by the "YEARMONTH" that is the "SelectYearMont h".

Can anyone please help me with the calculation I need?

Any help is greatly appreciated!

-Diana

[orangeCat]

Isn't the combobox etc on a form, and wouldn't you update a query that would run against the database and then run the report using the revised query or a filter?

I don't have Acc2007, but that would seem to be the process involved..

[gnawoncents]

Is this a different problem than your previous post?

[Diana Miller]

This is a different problem than previous post

Hi gnawoncents,

Yes, this is a different report than my previous post. It goes into a little bit more detail than the other report.

I am still having trouble figuring out the proper syntax for the afterupdate code for this one. My previous post didn't have to deal with multiple groups. There was no "JOBTYPE" group. It was a straight forward calculation of all pages for the month, not all pages by type for the month.

I tried manipulating the calculation you gave me for the monthly total to break it down by the group "JOBTYPE" but, I am not having any success with it.

Any help is super appreciated.

Thanks,
Diana

[gnawoncents]

Is JOBTYPE numeric or text?

[Diana Miller]

Jobtype

"JOBTYPE" is text
"PAGE" is numeric

[gnawoncents]

What about:

Me.TotalPages = DSum("Pages", "tblMONTH", "YEARMONTH = '" & [SelectYearMonth] & "' AND JOBTYPE = '" & Me.[JOBTYPE] & "'")

[Diana Miller]

Hi gnawoncents,

I get an error of a null value when I try to use this calculation. Am I missing something in the syntax here?

 Me.TotalPagesTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & "' AND JOBTYPE = '" & Me.[JOBTYPE] & "'")

[Diana Miller]

Here is the error code I get with the code above:

Run-Time error '3075':
Syntax error (missing operator) in query expression 'YEARMONTH = 201010' AND JOBTYPE = 'E".

What is the operator I am missing?

[gnawoncents]

If you copied it here exactly as in your database, you are missing one single-quote. Try:

Me.TotalPagesTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = '" & [SelectYearMonth] & "' AND JOBTYPE = '" & Me.[JOBTYPE] & "'")

Let me know how it goes.

[Diana Miller]

I have to remove that single quote from YEARMONTH or I get a type mismatch, it is numeric. There must be something else wrong with the syntax regarding JOBTYPE...

[gnawoncents]

So YEARMONTH is numeric, then we need to get rid of both single quotes.

Me.TotalPagesTextbox = DSum("Pages", "HBELP_FINAL_Q", "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = '" & Me.[JOBTYPE] & "'")

If this doesn't work, please post a sample of your database, because I'm obviously missing something.

[Diana Miller]

Hi,

Well, you are getting closer to the answer. By removing the other single quote, I now get a number in my text box. However, it is the same number for all four different JOBTYPE's. The number I am getting for all four types is the correct number for the first one listed.

I am sorry I can't post a sample of the database here for security reasons but we are getting really close here...wish we were playing horseshoes! :o)

[Diana Miller]

Hi gnawoncents,

I am over trying to get this thing working by groups it is just too frustrating! I have coded the individual JOBTYPE boxes, since there are only 4 JOBTYPES as:

Me.ExactReprint Textbox = DSum("Pages", "HBELP_FINAL_Q" , "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'E'")

Me.KeylineUpdat eTextbox = DSum("Pages", "HBELP_FINAL_Q" , "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'K'")

Me.OriginalsTex tbox = DSum("Pages", "HBELP_FINAL_Q" , "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'O'")

Me.ReprintsText box = DSum("Pages", "HBELP_FINAL_Q" , "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'R'")

This is working fine for the report. I have added a total of pages at the bottom of the report as:

Me.GrandTotalTe xtbox = DSum("Pages", "HBELP_FINAL_Q" , "YEARMONTH = " & [SelectYearMonth])

This is also working great.

I would like to add a job count to each type and I am trying to use:

Me.ExtactJobsTe xtbox = Count("JOBTYPE" , "HBELP_FINAL_Q" , "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'E'")

But something is not quite right with this calculation. Do you see where I am going wrong on this one?

You have really been a great help and I truly appreciate it.

Thanks,
Diana

[Diana Miller]

OMG, I wrote Count in my code and not DCount! It works great now as:

DCount("JOBTYPE ", "HBELP_FINAL_Q" , "YEARMONTH = " & [SelectYearMonth] & " AND JOBTYPE = 'E'")