• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1396
  • Last Modified:

rs.movenext and docmd.gotorecord,,acnext

In looping through a list of client emails to send a unique report, I have to use both:


I can't figure out why both are necessary.

If I remove "docmd.gotorecord,,acnext" each report is only sent to the first email on the list.
If I remove "rs.movenext" each person on the list gets only the first report (not there data).

Logical conclusion;
the docmd is changing the email address
the movenext is changing the report filter

The code gets the job done but I'd really like to understand why this is doing what its doing.
Side note.  If I stop the procedure w/ the real database (100's of records) and then restart, the email address starts over but the reports pickup where they left off.

To avoid the unpardonable sin of sending the wrong information to a client, I kill the form on an error.

Can someone explain why I need acnext and movenext?  I need more confidence in this code before I allow others to use it.  It just doesn't feel error proof.

Thank you.

  • 3
  • 2
1 Solution
The GotoRecord Method is use to make the specified record  the current record  in an open table, form, or query result set, whereas, the works on your recordset.

Therefore I assume you are both walking through the records of the form as well as a recordset in your code.

Cheers, Andrew

Jeffrey CoachmanCommented:

I was just waiting for you accept Andrew's post, so there would be no confusion...

Let's clean up your code.


ShawnGrayAuthor Commented:
Very nice.  "Clean" is an understatement. :O
Not sure I can or should trouble you with a follow up question; I'll post anyway.

The line
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Phone Book]")
Phone Book is a table that holds lots of clients, most unrelated to the query in this form.

The filtered list comes from:  PaymentHistoryQryList
When I insert that query to the openRecordset line I get to few parameters.
Is it possible to select this query?
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Jeffrey CoachmanCommented:
Shawn, there are many ways to do this.

The reason why you get the message is that this queryneeds a parameter.
The parameter is not available when you run the code so it tells you so.

This is why many developer opt to open the report with a filter rather than running the report from a parameter.
If you run the Report from a parameter then if you try to run the report on it's own, you will get the parameter prompt.
You have to do something special with the parameter to get it to show you "All" if no parameter is present.

Here is the revised sample,

Study it "carefully"

Did you know that you can send tha email automatically with out seeing them?
            DoCmd.SendObject acSendReport, "Collection History All Receipts", acFormatHTML, rst!Email, , , "email to:" & rst!Email, "test msg", , FASLE

You will need to use this program to avoid the security pop-up:


ShawnGrayAuthor Commented:

One 10th!  If I had but one 10th of your ability. :)
Not only do I appreciate the help and samples, I really appreciate you taking the time to educate.
I recognize this is inactive but I really value your insight.   You're awesome.

Thank you,
Jeffrey CoachmanCommented:

And if I had one 10th of TextReport's ability...

Like I said there are many waysto do this.

What attracted me to this was the fact that you were Looping the "Form's" records(docmd.gotorecord,,acnext), instead of looping only through the recordset.

Happy Birthday!


Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now