Looping through Records question

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

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 DeleteThis @public.com jjohnson DeleteThis @public.com

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"
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" wrote in messagenews:1169658283.200316.142520@v45g2000cwv.googlegroups.com...
>
>
>
> > 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 -