Re: Looping through Records question

Thanks for your reply. In answer to your question, RptName cannot
contain a string.
It contains only one report name. Each row in the table has the
following:
ID ReportName EmailtoName CCName
1 Report1 mbrown@xxxxxxxxxx jjohnson@xxxxxxxxxx

What I want to do is send the Report1 to the EmailtoName and the
CCName listed in the same row. And then move on to the next one.
I followed your advice but I am still getting the same report (Report1)
over and over.

On Jan 24, 4:56 pm, "Douglas J. Steele"
<NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote:
Do you mean that RptName can contain a string like
"Report1,Report2,Report3"? If so, you really should read up on database
normalization: storing multiple values in a single field is a bad idea.

If RptName is a single report name, then you need to take advantage of the
fact that you've opened a recordset. Using DLookup is going to return the
same value every time.

The 3 lines

Emailto = DLookup("EmailtoName", "tbl_ReportList")
CC = DLookup("CCName", "tbl_ReportList")
RptName = DLookup("ReportName", "tbl_ReportList")

should be

Emailto = rst!EmailtoName
CC = rst!CCName
RptName = rst!ReportName

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

"Kimberley" <ksmi...@xxxxxxxxx> wrote in messagenews:1169658283.200316.142520@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



Hi all,
I have a table in access (tbl_ReportList) that has four fields (ID,
Emailto, CC, and RptName). The email and CC fields both have e-mail
addresses in them and the RptName field is a list of reports in the
database. For now I only have three: Report1,Report2,Report3.
When I run the code below, the same report "Report1" keeps getting
sent. Can someone please tell me what I'm doing wrong in the loop. For
now, I only have three reports but by the end it will be about 150.
Thanks in advance for any help,

Private Sub Command0_Click()
Dim db As database
Dim rst As DAO.Recordset
Dim x As Integer
Dim Emailto As String
Dim CC As String
Dim RptName As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_ReportList", dbOpenDynaset)

With rst
If Not (.EOF And .BOF) Then
rst.MoveFirst
Do Until rst.EOF
For x = 1 To 140
Emailto = DLookup("EmailtoName", "tbl_ReportList")
CC = DLookup("CCName", "tbl_ReportList")
RptName = DLookup("ReportName", "tbl_ReportList")

DoCmd.SendObject acReport, RptName, "SnapshotFormat(*.snp)", _
Emailto, CCto, "", "Attention Required Report", "", False,
""
rst.MoveNext
Next
Loop
End If
End With

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub- Hide quoted text -- Show quoted text -

.

Relevant Pages
Loading