Show Most Recent Information for each Problem Item for One Company

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My database has one table, tblPlanData, with Fields: CustID, ProbItemNo,
CallDate, and CallNote (Memo). I would like to run a Report for One CustID
that shows only the most recent CallNote for each ProbItemNo.
 
Base the report on a TOP 1 query. To do this, in query design view sort the
records by the date/time field. Click View > Properties (or click Alt +
Enter). Set the Top Values to 1. You could do this in SQL view by adding
"TOP 1" (without the quotes) after SELECT.
 
Group your report by ProbItemNo with a group header. Sort next by CallDate
Descending. Add a text box to your report's detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No
Add code to the On Format event of the detail section:
Cancel = Me.txtCount>1
 
I think a non-code method would be to place all of your fields into the
ProbItemNo group header. This would assume the report is sorted by CustID,
ProbItemNo, and CallDate (Desc). Then hide the detail section.
 
Elegant! Clear, and easy to maintain. Report already had OnOpen and OnClose
Events, so adding OnFormat was a snap. Thanks!
 
This code works great, thank you. However, when there's no data it gives me
an Run-Time Error Code "2427". Is there a way to fix this?
 
I am surprised that a report with no data would run the On Format event code
of the detail section. Try change the code to:
If Me.HasData Then
Cancel = Me.txtCount>1
End If
 
Thank you Duane, I got that from your earlier posts and did the trick. Works
like a charm.
 
It might be best to start a new thread with complete information about your
situation.
 
Back
Top