Assign a value to a field in the query underlying a report

[NeoPa]

I'm actually looking at it now :)
I'm going to suggest an UPDATE query (Details to follow.) which should run before you open the report. This way you can use the newly updated [IncludeInMM] flag (If and ONLY if it is appropriate for YOUR situation.) to control what is printed in the report too. You won't NEED to change anything, but it may give you the option.

-Adrian.

[NeoPa]

This really isn't as simple as it looks is it?
I've had to create a test rig to try to get it to work without complaining about needing an updatable query in the UPDATE.
In case you're interested, this is what I have so far (That I know doesn't work).

UPDATE tblMembers AS M INNER JOIN (SELECT P.MemberID
FROM tblPledges AS P INNER JOIN tblPledgePayments AS PP
  ON P.PledgeID=PP.PledgeID
GROUP BY P.MemberID,P.PledgeID,P.PledgeAmount
HAVING P.PledgeAmount>Sum(PP.ContributionAmount)) AS subQ
  ON M.MemberID = subQ.MemberID
SET M.IncludeInMM = True

[NeoPa]

As the criteria must, of necessity, total up the amounts paid, and a GROUP BY clause is never compatible with an UPDATE query, we will need to add an extra field into the [tblPledges] table called [TempPaid]. This will be a Currency field just like the [PledgeAmount] field, but it will typically be undefined. It will only ever have reliable information immediately after running through this process so should generally be treated with high suspicion.
This is more of a complicated solution so please give me a little more time to complete the details, but it would be a good start to get the field added now anyway.

[NeoPa]

An actual answer at last!

If you run the (Pledges) report from a CommandButton on your form, then before the DoCmd.OpenRepor t() you would have some code that updates the records like :

Dim strSQL As String

Call DoCmd.SetWarnings(False)
'Reset [TempPaid] field
strSQL = "UPDATE tblPledges SET TempPaid=0"
Call DoCmd.RunSQL(strSQL)
strSQL = "UPDATE tblPledges AS P " & _
         "INNER JOIN tblPledgePayments AS PP " & _
         "ON P.PledgeID=PP.PledgeID " & _
         "SET P.TempPaid=P.TempPaid+PP.ContributionAmount"
Call DoCmd.RunSQL(strSQL)
'Using [TempPaid], set [IncludeInMM] flag
'This code is commented out as you said it was already effected
'but you can uncomment it if it's required
'strSQL = "UPDATE tblMembers SET IncludeInMM=False"
'Call DoCmd.RunSQL(strSQL)
strSQL = "UPDATE tblMembers AS M " & _
         "INNER JOIN tblPledges AS P " & _
         "ON M.MemberID=P.MemberID " & _
         "SET M.IncludeInMM=True " & _
         "WHERE P.PledgeAmount>P.TempPaid"
Call DoCmd.RunSQL(strSQL)
Call DoCmd.SetWarnings(False)
'Your code to run the report would follow on here...

In the end, this would probably have been easier doing the 'correct' way, as a SELECT query would not have fallen foul of the same restrictions (Needing to be an updatable query) that the UPDATE query does.

[djsdaddy]

An actual answer at last!

If you run the (Pledges) report from a CommandButton on your form, then before the DoCmd.OpenRepor t() you would have some code that updates the records like :

Dim strSQL As String

Call DoCmd.SetWarnings(False)
'Reset [TempPaid] field
strSQL = "UPDATE tblPledges SET TempPaid=0"
Call DoCmd.RunSQL(strSQL)
strSQL = "UPDATE tblPledges AS P " & _
         "INNER JOIN tblPledgePayments AS PP " & _
         "ON P.PledgeID=PP.PledgeID " & _
         "SET P.TempPaid=P.TempPaid+PP.ContributionAmount"
Call DoCmd.RunSQL(strSQL)
'Using [TempPaid], set [IncludeInMM] flag
'This code is commented out as you said it was already effected
'but you can uncomment it if it's required
'strSQL = "UPDATE tblMembers SET IncludeInMM=False"
'Call DoCmd.RunSQL(strSQL)
strSQL = "UPDATE tblMembers AS M " & _
         "INNER JOIN tblPledges AS P " & _
         "ON M.MemberID=P.MemberID " & _
         "SET M.IncludeInMM=True " & _
         "WHERE P.PledgeAmount>P.TempPaid"
Call DoCmd.RunSQL(strSQL)
Call DoCmd.SetWarnings(False)
'Your code to run the report would follow on here...

In the end, this would probably have been easier doing the 'correct' way, as a SELECT query would not have fallen foul of the same restrictions (Needing to be an updatable query) that the UPDATE query does.

I had a longer than expected day yesterday,and when I went to log on the site last night, I saw that it was down,and then when it rains it pours; early this morning my browser, Firefox, kept freezing on me (which has happened with more frequency after installing some MS updates and programs). I have one project to move this morning and then I will come back and incorporate your answer into my application and let you know how it works.

Thanks for all of your assistance,

David

[NeoPa]

Not a problem David.
I too had problems accessing the site yesterday and also at lunchtime today. Changes are afoot. They should ultimately be for the best though, incorporating searching through specific forums for threads etc.

[djsdaddy]

Not a problem David.
I too had problems accessing the site yesterday and also at lunchtime today. Changes are afoot. They should ultimately be for the best though, incorporating searching through specific forums for threads etc.

Hi Adrian, Thanks for the time you have put into my problem. I haven't been able to squeeze enough time in to apply it to my application, and then Mrs. David took off from work today, and dared me to ignore her while working on my computer, so today was shot. I hope to do it tomorrow. I'll keep you posted.

Thanks again and have a pleasant day.

David

[NeoPa]

Hi Adrian, Thanks for the time you have put into my problem. I haven't been able to squeeze enough time in to apply it to my application, and then Mrs. David took off from work today, and dared me to ignore her while working on my computer, so today was shot. I hope to do it tomorrow. I'll keep you posted.

Thanks again and have a pleasant day.

David

So, did you dare to ignore her. I want all the gory details - How much blood? Any usable limbs left? How DOES one eat without teeth?

It would have been very infra-gallant to ignore your good lady when you have the opportunity to spend quality time together, so I'm sure you didn't. Please pass her my best wishes and get back to the project when you're good and ready.

-Adrian.

[djsdaddy]

So, did you dare to ignore her. I want all the gory details - How much blood? Any usable limbs left? How DOES one eat without teeth?

It would have been very infra-gallant to ignore your good lady when you have the opportunity to spend quality time together, so I'm sure you didn't. Please pass her my best wishes and get back to the project when you're good and ready.

-Adrian.

Woooo-ooo-Weee and a lot of laughing!! I would have never figured that one out. The difference between your magic and Copperfield's is that yours is real. That was spectacular. FYI, I didn't need to comment out

'strSQL = "UPDATE tblMembers SET IncludeInMM=False"
'Call DoCmd.RunSQL(strSQL)

because as you were already aware, I had taken care of that part of the feature. Also, I assumed that

Call DoCmd.SetWarnings(False)

at the end of your code should be set to True. Additionally, I added a line of code in the Report's OnClose event that opened my Print-Label Form and all of the appropriate names showed up - Unbelievable!! Consequently, thanks to you, all the user needs to do after printing the reports is place the labels in the printer and press the print button on the Print-Label Form.

Adrian, I am always thankful when someone comes to my rescue,and working with you over the last week taught me two things: First, how to set the IncludeInMM field to True for the Pledge Report; and two, how to be more specific in my explanations for assistance. Thanks for both lessons.

Have a blessed day as you have blessed mine.

David

p.s. I have been married too long and have tried too often to sneak on the computer when the Mrs is home to try that "prank" now. I am too old to run for my life :o).

[NeoPa]

You're absolutely right about the True on the return leg. Sloppy post, but you noticed it so that's cool.
I'm very pleased to hear it worked for you - that's what makes it all worthwhile, when you can help someone get that pleased :)