Loop through tblClient & tblValuation to produce report (1 Viewer)

Hi...I have tblClient table and a tblValuationData, the tables are linked by Policy number. Each client has several lines of valuation data, categorised by either cash accounts or fund holdings. I have a report with record source tblClient, this report has 2 sub reports with recordsource tble ValuationData detailing the current cash account values and the fund holding values. I can produce a pdf of the report and attach to an email.
What I'd like to do is produce a run of valuations, which I think can be done by looping through the records....
I have used the code below from a previous thread explaining recordsets, and when run it produces a list of all the relevant policy numbers,,,,could anyone help me with the next steps producing a run of valuations using the data contained in the 2 tables? or recommend a better solution ....any help much appreciated....thanks
Fi

Option Compare Database

Sub DAOexample1()
On Error GoTo ErrorHandler

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "tblClient"

Set rs = CurrentDb.OpenRecordset(strSQL)

With rs

If Not .BOF And Not .EOF Then

.MoveLast
.MoveFirst

While (Not .EOF)

Debug.Print rs.Fields("ID") & " " & rs.Fields("Policy No")
.MoveNext

Wend

End If

.Close

End With

ExitSub:
Set rs = Nothing

Exit Sub
ErrorHandler:
Resume ExitSub
End Sub

Explain your application more in detail. What do you mean by run and produce a run of validations. What do you want to put on the report (structure).

Recordset looping is not necessary. Just set the Link Master Field and Link Child Fields in the sub reports to [Portfolio No]

Hi
so10070 on the report I want to see the client name and portfolio number form tblClients eand a list of the funds cash accounts allocated to his portfolio from tblValuationData these tables are linked by PolicyNumber
There are 3000 clients on the database each of them has a portfolio and each portfolio has a list of assets held within their respective portfolios, this portfolio data is updated on a monthly basis at which point I need to produce a report (valuation) for each client detailing the current value of their respective portfolios. I can produce the reports individually at the moment and attach them to an email, ideally I would want to create/produce an individual valuation for each client in one go rather than producing each client valuation individually.
CRONK the links you mention are set up like this and I can produce a report (valuation) with all the data required for the report (valuation), could you help me understand how to produce an individual report (valuation) for all clients on the database?

Thanks in advance
Fi

Hi
The code below was on a related thread and it seems to be working for my scenario in that it creates a batch of PDF'd reports (valuations) one for each client on the database and stores them on the desktop ( I have added criteria to the underlying query limiting it to produce only 2 valuations for testing, rather than the full 3000....!) I would like to add in code that will also attach the emails individually to the email address on the underlying query, which I have tried with the line DoCmd.SendObject with limited success, this code will produce the email and attach the pdf if I add an unbound text box (txtEmail) to the form the button is on and if I add the email address of the client to this text box.
Could anyone help me with producing individual emails with the relevant pdf attached and have them stored in drafts !....thanks for any help given…Fi

Private Sub Report_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String


mypath = "C:\Users\jbloggs\Desktop\PDF Docs"

Set db = CurrentDb()

Set rs1 = db.OpenRecordset("SELECT distinct [Policy No] FROM [qryQUERY]", dbOpenSnapshot)

Do While Not rs1.EOF

temp = rs1("Policy No")
MyFileName = rs1("Policy No") & ".PDF"

DoCmd.OpenReport "rptREPORT", acViewReport, , "[Policy No]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
''''''''''DoCmd.SendObject acReport, "rptREPORT", acFormatPDF, Me.txtEmail, , , , "Dear" & Chr(13) & Chr(13) & "Please find attached your annual valuation." & Chr(13) & Chr(13) & "" & Chr(13) & Chr(13) & "By all means do let me know if I can be of any assistance should you have any queries." & Chr(13) & Chr(13) & "Kind regards," & Chr(13) & Chr(13), True

DoCmd.Close acReport, "rptREPORT"
DoEvents

rs1.MoveNext
Loop


rs1.Close
Set rs1 = Nothing
Set db = Nothing

End Sub

Have a look on here for outlook automation it will do what you need. Replace your Send Object with the automation code.

Great example here : http://www.access-programmers.co.uk/forums/showthread.php?t=283494

Hi Minty ...many thanks for your reply and the link, I have added the Public Function SendOutlookEmail, below the rest of the code, could you help me with where to place the code to call the function,,,getting a bit over my head now !....thanks Fi