Re: Looping through Records question

Your answer is confusing!!

the word 'report1' IS a string of eight characters, yet you state RptName cannot contain a string. (You've even defined it as a container for strings)

Are you referring to the database 'ReportName' (is it string or numeric?)

Bugger, just had a callout - Douglas will continue with this

Argusy


Kimberley wrote:
I also had a couple of things named incorrectly. But removing the
DLOOKUP and using recordset worked perfectly.

Thanks again for your help

On Jan 24, 5:48 pm, "Kimberley" <ksmi...@xxxxxxxxx> wrote:

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 mbr...@xxxxxxxxxx jjohn...@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 -- Hide quoted text -- Show quoted text -



.

Relevant Pages

  • Re: Looping through Records question
    ... I believe that "RptName cannot contain a string" was a slightly misword ... the word 'report1' IS a string of eight characters, ... Dim rst As DAO.Recordset ...
    (microsoft.public.vb.database.dao)
  • Re: Display report based on selected Customer
    ... combo Is Null, and if not, start the Where string using the Number example. ... Your OrderDate is a date, so use the Date type example. ... DoCmd.OpenReport "Report1", acViewPreview,, strWhere ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.reports)
  • Re: Str where and muliple criteria problem
    ... Dim RptName As String ... Dim strWhere As String ... DoCmd.OpenReport RptName, acViewPreview ...
    (comp.databases.ms-access)
  • Re: Str where and muliple criteria problem
    ... Dim RptName As String ... Dim strWhere As String ... DoCmd.OpenReport RptName, acViewPreview ...
    (microsoft.public.access.modulesdaovba)
  • Re: Str where and muliple criteria problem
    ... Dim RptName As String ... Dim strWhere As String ... DoCmd.OpenReport RptName, acViewPreview ...
    (microsoft.public.access.modulesdaovba)
Loading