Report record selection

[raugust]

OK, just getting my feet wet in VBA.

I am working on a volunteer organization's database, and am trying to create capacity to print an invoice for a donation pledge. I have a sub-form that displays donation records for a selected individual. From the sub-form I have created a command button with an OnClick event procedure with the following code:

DoCmd.OpenReport "Reports![RadThonInvoiceR]", acViewNormal, , "MasterID= " & Me.MasterID

This currently selects and prints something like the following set:

ID Year Amount Status

53 2008 $200.00 Paid
53 2009 $200.00 Paid
53 2013 $200.00 Unpaid

Can I modify my Where condition so that the report selects only the most recent entry (by Year) from among the current selections?

Any help much appreciated.

Rick

 

[JHB]

..
Can I modify my Where condition so that the report selects only the most recent entry (by Year) from among the current selections?

The answer is short, No!
But you can base the report on a query, actually 2 queries.
Query #1 select the max year group by Id.
And then link Query #1 in query #2.
Query #1

SELECT Id, Max(Year) AS MaxYear
FROM YourTableName
GROUP BY Id;

SELECT YourTableName.Id, YourTableName.Year, YourTableName.Amount, YourTableName.Status
FROM YourTableName INNER JOIN YourQueryName ON (YourTableName.Year = YourQueryName.MaxYear) AND (YourTableName.Id = YourQueryName.Id);

Remember to change "YourTableName" to the name you use for the table and "YourQueryName" to the name you use for the query.
"Year" is a reserved word in MS-Access, so don't use it as field name, it can give you problem.

 

[raugust]

Thanks for that!

To explain the context a little better: In this database the donations file (RadThonT) is linked to a contact file (ContactMasterT) by a field called MasterID. The user looks up an individual in a form, and then has available all of that individual's donations in a sub-form. From the sub-form, I am trying to automate the printing of an invoice for the most recent donation using a command button on the sub-form.

Here is my rendering of your code with proper field names and such:

RadThonInvoiceQ1:

SELECT MasterID, Max(RDYear) AS RDMaxYear
FROM RadThonT
GROUP BY MasterID;

RadThonInvoiceQ2:

SELECT RadthonT.MasterID, RadThonT.RDYEAR, RadThonT.RDAmount, RadThonT.RDStatus, RadThonT.RDID, RadThonT.RDNotes
FROM RadThonT INNER JOIN RadThonInvoiceQ1 ON (RadThonT.RDYear=RadThonInvoiceQ1.RDMaxYear) AND (RadthonT.MasterID=RadThonInvoiceQ1.MasterID);

My report now uses RadThonInvoiceQ2 as data source.

When I run the report from a command button macro from the sub-form, I get a report that lists the most recent donation of all individuals in the database.

This will actually be very useful to me in another context. However, for this task I need the report limited to the most recent donation by the particular individual being viewed by the user in the sub-form. Is there a way to apply this limitation?

 

[raugust]

OK, trying a new tack here, to try to get the first query to filter to a single donation record. This code ...

SELECT Forms!PersonalMasterF!RadThonSubF.Form![MasterID] AS MasterID, Max(Forms!PersonalMasterf!RadThonSubF.Form![RDYear]) AS RDYear
FROM RadThonT;

... successfully finds a single donation record that has focus in the subform and successfully captures the year variable, but is not correctly capturing MasterID. Can anyone see anything I've got wrong in my code?

 

[JHB]

You need the WHERE clause in the query.
But can't you open the report the way you showed in your first post?

DoCmd.OpenReport "Reports![RadThonInvoiceR]", acViewNormal, , "MasterID= " & Me.MasterID

Else post a stripped version of your database with some sample data (zip it) + info in which form/report you have the problem.

 

[raugust]

JHB - I've uploaded the database with sample data. The problem area would be found in form PersonalMasterF under the Radiothon tab, which contains the RadthonSubF sub-form. The code based on your query suggestion is in RadThonInvoiceQ1/Q2, the report is RadthonInvoiceR. As this stands, it prints an invoice for the last pledge by year of every individual in the database. What I need to do is make it print for the currently viewed individual, either the last pledge by year or the one with current focus.

 

[JHB]

Try it now!
If you want to send the report directly to the printer change the "acViewPreview" to "acViewNormal"

  DoCmd.OpenReport "RadThonInvoiceR", [B][COLOR=Red]acViewPreview[/COLOR][/B], , "MasterID= " & Me.MasterID
Change it to:
  DoCmd.OpenReport "RadThonInvoiceR", [B][COLOR=Red]acViewNormal[/COLOR][/B], , "MasterID= " & Me.MasterID

 

[raugust]

Thank you, that's exactly what I need. Looks like I had bad syntax, yes?

 

[JHB]

...Looks like I had bad syntax, yes?

Yes and No - you have set the "On Click" event to run a Macro, but you have also the text "Reports!" which is wrong.