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.

Who is Participating?
TextReportConnect With a Mentor Commented:
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 CoachmanMIS LiasonCommented:

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?
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Jeffrey CoachmanMIS LiasonCommented:
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 CoachmanMIS LiasonCommented:

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!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.