Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 60
  1. #16
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Can you take a look at this picture?
    When I put my cursor over it, it shows the email address the report is going to. This is the first email in the T_Inspectors.


    It shouldn't go to the first email in Table. But to the email in query. Does this help?

    Click image for larger version. 

Name:	test.png 
Views:	5 
Size:	165.3 KB 
ID:	14028

  2. #17
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Correction It should be going to the email address FROM the T_Inspectors, however only for the names that are in the query ran from the R_WeeklyDispatch_Today report. Hope this is clearer

  3. #18
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Can you take a look at this pic? Notice its actually saying its pulling the email address from the T_Inspectors table. This is email is the first record in the table and has nothing to do with the query link to the "R_WeeklyDispatch report?
    Click image for larger version. 

Name:	test.png 
Views:	5 
Size:	165.3 KB 
ID:	14029

  4. #19
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I am not convinced your .SendObject is doing anything. I do understand it is looking at the rs recordset and retrieving an Email address. It is placing this email address into the TO


    .SendObject does not have an object name. You could give it a name like the report name. Not sure it is going to respect looping through the rs recordset.

    I was thinking it would be a good idea to test the earlier .openReport

    If that is working then just below you could add a line to select object "Report name"

    then with the report name included in the .SendObject you could give it another go.

  5. #20
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862

    Example

    I would just like to be sure it is opening the correct report. Still a little confused if this is happening.

    Code:
    DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Last Name]='" & rs![Last Name] & "'"
    DoCmd.SelectObject acReport, "R_WeeklyDispatch_Today", False
    DoCmd.SendObject acSendReport, "R_WeeklyDispatch_Today", acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
    DoCmd.Close acReport, "R_WeeklyDispatch_Today"

  6. #21
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Ok I use this below. Its still doing the same thing. Sending a blank report even though this report has a query of 4 records Date(). Not sure where its pulling from.
    Also, it keeps sending it to the first email in the T_Inspectors table. Has nothing to do with the query setup. SO confuse...

    Private Sub Command5_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT [Last Name], Email FROM T_Inspectors;")

    While Not rs.EOF


    DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Employee]='" & rs![Last Name] & "'"
    DoCmd.SelectObject acReport, "R_WeeklyDispatch_Today", False
    DoCmd.SendObject acSendReport, "R_WeeklyDispatch_Today", acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
    DoCmd.Close acReport, "R_WeeklyDispatch_Today"
    rs.MoveNext
    Wend


    End Sub

  7. #22
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Notice pic again...at RS!Email that is not the email it should pull up. ITs pulling up the first record in the T_Inspectors.
    I want it to send only to email address link in the query report.
    Click image for larger version. 

Name:	test.png 
Views:	5 
Size:	81.1 KB 
ID:	14030

  8. #23
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Right, it is looking at the table rs is the table.

    Quote Originally Posted by wnicole View Post
    This is email is the first record in the table and has nothing to do with the query link to the "R_WeeklyDispatch report?
    That is what your SELECT is doing. it is looking at the table. It will go to the first record and move down until it finds a match with EMPLOYEE.

    When EMPLOYEE (within the query) matches the Last Name in the table it will grab that record. It will open the report where it finds a match. It will look for a match again. Looking for duplicates is what your loop is doing, as far as I can tell.

  9. #24
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    If there are only four records, how difficult would it be to upload a clean copy? Just grab the query and the tables and the report that matter.

  10. #25
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    I don't think this is what its doing however.
    The email that is coming up in OUtlook is not a part of the query at all. Its as if it didnt find anything so grab the first record in the table.

    If its suppose to do what you say above, why does isn't pulling the correct match? Is my code wrong?

  11. #26
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    4 records are what I'm testing with my email address. There are about 20 records that need to be sent.

  12. #27
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    One of my suggestions was to test your recordset and see what record it was returning.

    Post 15 kinda describes this. I like to take things one step at a time. That way you know for sure before starting a loop.

    If you need some help with some code to test, let me know.

  13. #28
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    I do...I couldnt run the sample you sent. Not quite understood...
    Can you send me test code?

  14. #29
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    I will work something up. You may need to do a nested loop. Is there a saved Access query that the report uses?

    What is the query name?

    What does the field [Employee] represent? Is there a way you can match PK and FK?

    How are you determining who gets an email?

    What is the PK for the query?

    What is the PK for the table?

  15. #30
    wnicole is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Sep 2013
    Posts
    118
    Is there a saved Access query that the report uses? Yes

    What is the query name? "Q_WeeklyEmployeeDispatch_Today"

    What does the field [Employee] represent? Employee and Last Name go hand in hand. In the Employees Table we have Last Name First Name. In the Job Tables we Employee (which pull from the Employee Table)
    Is there a way you can match PK and FK? THe Employees Table has ID1 as primary key and the JObs Tables have ID1 as primary key. They both have the same name.

    How are you determining who gets an email? Based on the query. All who has a job Date().

    What is the PK for the query?

    What is the PK for the table?

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Code Problems With Variables
    By bidbud68 in forum Programming
    Replies: 6
    Last Post: 01-30-2013, 01:37 PM
  2. SendEmail CC
    By cbrsix in forum Programming
    Replies: 4
    Last Post: 11-08-2012, 03:03 PM
  3. AfterUpdate SendEMail Macro
    By gdgonzal in forum SharePoint
    Replies: 3
    Last Post: 04-13-2012, 01:49 PM
  4. Problems Executing SavedQuery in Code
    By RMittelman in forum Programming
    Replies: 4
    Last Post: 06-18-2011, 02:49 PM
  5. Merge Code problems
    By kfergus in forum Access
    Replies: 1
    Last Post: 06-01-2006, 03:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums