rs.movenext and docmd.gotorecord,,acnext

Posted on 2009-02-24
Last Modified: 2013-11-29
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.

Question by:ShawnGray
    LVL 28

    Accepted 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

    LVL 74

    Expert Comment

    by:Jeffrey Coachman

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

    Let's clean up your code.



    Author Comment

    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?
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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:



    Author Comment


    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,
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    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

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now