strSQL error in code

[DomFino]

Hi All,
I am trying to do something similar to post thread705-1137518. My problem is the sName returns "MyReportName" but the strSQL returns 'MyReportName'". Therefore, I receive an error that it cannot find MyReportName due to the punctuation. How do I make the strSQL statment retuen "MyReportName"

Thanks
Dom

Public Function SendReport(sName As String) As Boolean
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM EmailTable WHERE eReport = [COLOR=red]'" & sName & "'"[/color]

Set rs = CurrentDb.OpenRecordset[COLOR=red](strSQL)[/color]

If rs.EOF Then 'no record for that report
   Set rs = Nothing
   MsgBox "No email entry for that report.", vbOKOnly, "No Email"
   Exit Function
End If

With rs
   .MoveFirst

   DoCmd.SendObject acSendReport, sName, , !eTo, !eCC, _
                    !eBCC, !eSubject, !eMessage, False


MsgBox "Email sent to " & !eTo & vbCrLf & !eCC & _
       vbCrLf & !eBCC & ".", vbOKOnly, "Email Sent"
End With

SendReport = True

Set rs = Nothing

End Function

 

[Remou]

I do not quite get your point. Are you saying that reports are stored in the table with single quotes?

 

[DomFino]

Thans for the reply Remou.

No, the table has MyReportName, no punctuation at all. The problem is this line of code displays MyReportName correctily when I step through the code:

strSQL = "SELECT * FROM EmailTable WHERE eReport = "MyReportName"

However, the next line of code displays the (strSQL) as 'MyReportName'"

I would have expected the (strSQL) to return "MyReportName

 

[Remou]

You must use single quotes when referring to a text field, so you would say:

[tt]strSQL = "SELECT * FROM EmailTable WHERE eReport = [red]'[/red]" & "MyReportName" & "[red]'[/red]"[/tt]

If there is nothing returned, perhaps MyReportName is not being passed correctly? It may be as well to put in:

[tt]Debug.Print strSQL[/tt]

This will print strSQL to the immediate window. You can cut and paste it into the Query Design screen, SQL view, to see how it works out.

 

[DomFino]

Remou,
I wanted to post back with my findings and the working code. The problem turned out to be that if I had no eCC or eBCC entry in my table it sent a NULL to the DoCmd.SendObject line of code and Access did not like that. If I put an entry into those fields, the code worked fine. I decided to remove the two fields from the table since they will most likely not be used, and modified the code. The code below works fine. However, I do get a screen that pops up saying someone is using your email to send stuff to Access. The message requires me to click YES before the email is actually sent. I have not figured out how to bypass that message yet but will do some searching.

Thanks for your help
Dom

Public Function SendReport(sName As String) As Boolean
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM EmailTable WHERE eReport = '" & sName & "'"

Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.EOF Then 'no record for that report
   Set rs = Nothing
   MsgBox "No email entry for that report.", vbOKOnly, "No Email"
   Exit Function
End If

With rs
   .MoveFirst

    DoCmd.SendObject acSendReport, sName, acFormatSNP, !eTo, , , !eSubject, !eMessage, False

    MsgBox "Email sent to " & !eTo & vbCrLf & ".", vbOKOnly, "Email Sent"
End With

SendReport = True

Set rs = Nothing

End Function

 

[Remou]

Be aware that a null or blank email will also cause errors, so it is worth checking.
I think the answer to the pop-up may be to search for 'Outlook redemption'.

 

[DomFino]

Thanks, I will check out Redemption.