subreport not displaying data

  • Thread starter Thread starter CDM
  • Start date Start date
C

CDM

I have a main form that shows aged receivables for each customer acct. I also
have a subreport embedded in the detail section of the main form that is
supposed to show the customer's four most recent payments. The subreport is
based on a temporary table that is repopulated during the detail_format event
of the main form for each new customer using a SQL statement. I have linked
the forms using Customer_Id in the child/master fields. None of the payments
appear when I run the report.
Here is the code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb
db.Execute "DELETE FROM tblPayments"

strSQL = "INSERT INTO tblPayments(Customer_Id,Pmt_Date,Pmt_Amount)" & _
" SELECT TOP 4 Customer_Id,Pmt_Date,Pmt_Amount" & _
" FROM ltblCustomerPaymentHistory" & _
" WHERE ltblCustomerPaymentHistory.Customer_Id =" & "'" &
Me!Customer_Id & "'" & _
" ORDER BY ltblCustomerPaymentHistory.Pmt_Date DESC;"
db.Execute strSQL
Me!srptCustomerPayments.Requery

(srptCustomerPayments is the name of the subreport)
Any ideas would be appreciated!
 
I don't think you will have success in displaying records from a table/query
that you are updating from an event in your report.

You should create a query of the last 4 payments for each customer and use
the Link Master/Child properties of the subreport.

You could also include all payments but cancel the printing of payment
records after the 4th.
 
Back
Top