Using Access 2003
I am looping through table values and creating a separate report for each name. The report is then emailed with a TO: and CC: address. That works fine. What I would like to do is put that value (someone's name) in the header of the report. The field containing the name can have up to 5 names in it. I use the LIKE filter to determine if the name is present. The only time the unique name exists is in the loop as it cycles through the existing names. How do I get that fleeting variable into the report as it is opened??
Code:-
I am looping through table values and creating a separate report for each name. The report is then emailed with a TO: and CC: address. That works fine. What I would like to do is put that value (someone's name) in the header of the report. The field containing the name can have up to 5 names in it. I use the LIKE filter to determine if the name is present. The only time the unique name exists is in the loop as it cycles through the existing names. How do I get that fleeting variable into the report as it is opened??
Code:-
Code:
Private Sub Loopy(rs As Recordset)
Dim name1, Name2, name3 As String
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True
sContactName = rs!WorkCName
ReportHeaderValueDesired = sContactName
name3 = "[qryOpFile]![Resource Total] Like ""*" & sContactName & "*"""
sContactName = rs!Email1
Name1 = sContactName
sContactName = rs!Email2
Name2 = sContactName
DoCmd.OpenReport "OpReport", acViewPreview, "", [qryOpFile]![Resource Total] & "Like sContactName, acNormal"
DoCmd.OpenReport "OpReport", acViewPreview, "", name3, acNormal
DoCmd.SendObject acReport, "OpReport", "SnapshotFormat(*.snp)", Name1, "Name2", "", sContactName, "", False, ""
DoCmd.Close acReport, "OpReport"
rs.MoveNext
Loop
Else
MsgBox "There are no records in the recordset."
End If
MsgBox "End of staff list"
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
End Sub
Comment