DoCmd.OpenReport with Criteria Question: Please Help

[emjohnson7]

Hi there,

I am working on some reports – I need to replace the “name”with a query that goes through 67 names in a loop.

DoCmd.OpenReport "Report - Change Month in Title eachtime", acViewPreview, "", "

![Send ReportTo]=""name""", acNormal

Below is my entire code thus far. I can get the email portion to work with thequery, but I cannot with the opening the report. Please help.

[FONT=Calibri][SIZE=3][COLOR=#000000]Function SendEmails()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Dim strSQL AsString[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   Dim SendToEmail AsString[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]   DimReportRecipient As String[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    Set db = CurrentDb[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    ' Use the nextlines to define and open the query that identifies all unique "SendTo" values.  Replace the SQL I havebelow with the proper SQL[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    strSQL ="SELECT DISTINCT Ownership - VP Level].[Send Report To], UserInfo.[Workemail] FROM UserInfo INNER JOIN [Ownership - VP Level] ON UserInfo.Name = [Ownership- VP Level].[Last, First];"[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    Set rst =db.OpenRecordset(strSQL)[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    ' Scroll thru therecordset[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    With rst[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]        Do Until .EOF[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]           ReportRecipient = ![Send Report To][/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]          SendToEmail = ![Work email][/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]            ' Storethe value in the Send To field of the current record[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]           DoCmd.OpenReport "Report - Change Month in Title each time",acViewPreview, "", "[Table]![Send Report To]=""name""",acNormal[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]           DoCmd.SendObject acReport, "Report - Change Month in Title eachtime", "PDFFormat(*.pdf)", SendToEmail, "","", "REPORT TEST EMAIL", "This is a test to see if theemails will be sent properly.", True, ""[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]           DoCmd.Close acReport, "Report - Change Month in Title eachtime"[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]            .MoveNext[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]        Loop[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]        .Close[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    Set rst = Nothing[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    Set db = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Function[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

Thank you!!

 

[mole999]

As a new user you cannot post without approval if the subject gets moderated, so please don't post the same question multiple times to force a bypass. The information is in the guides you agreed to when joining, also code needs to be wrapped within CODE TAGS

 

[emjohnson7]

As a new user you cannot post without approval if the subject gets moderated, so please don't post the same question multiple times to force a bypass. The information is in the guides you agreed to when joining, also code needs to be wrapped within CODE TAGS

Thank you, I did not know how to put the code in quotes. I also did not know that I submitted the question twice. Thanks for helping me.

 

[welshgasman]

If I understand you correctly, you would concatenate your name to the WHERE criteria?
You need single quotes if it is text, none if numeric.

          DoCmd.OpenReport "Report - Change Month in Title each time",acViewPreview, , "[Table]![Send Report To]= '" &name & "'",acNormal

 

[emjohnson7]

If I understand you correctly, you would concatenate your name to the WHERE criteria?
You need single quotes if it is text, none if numeric.

          DoCmd.OpenReport "Report - Change Month in Title each time",acViewPreview, , "[Table]![Send Report To]= '" &name & "'",acNormal

Thank you for replying! I'm still having issues. When I try that code, I am getting an error that says missing operator in query expression

[Table]![Send Report To] = Joe Smith'

so it is working to pull the name, which is what I couldn't figure out before, (thank you!!) but where is the missing operator??

 

[welshgasman]

You are missing the single quote at the start of the name.
You could also probably just get away with [Send Report To]

 

[emjohnson7]

I got it to partially, thank you much!!! Now though, it makes it through several records then quits on the last name O'Connell. Here is what my code looks like now:

DoCmd.OpenReport "Report - Change Month in Title each time", acViewPreview, , "[Table]![Send Report To]= '" & [ReportRecipient] & "'", acNormal

Any ideas???

 

[welshgasman]

Yes, strings with single quotes like O'Connell cause problems.

In which case you can use ""[ReportRecipient]""

That is, you use two double quotes together instead of single quotes.

 

[emjohnson7]

Worked like a charm! Thank you from a person *brand new* to coding, your help is extremely appreciated!

 

[welshgasman]

You are welcome.
I thought I'd perhaps got it wrong as well, as if you want the same for a sql statement you would need as below

strName = "D'Costa"
strSQL = "SELECT * from Crew WHERE Surname = """ & strName & """"