Substituting a record into a DoCmd (1 Viewer)

[Agnister]

I have a query (qry_receipts) which contains the members email addresses. I can create an individual receipt (rpt_receipts) for each member and then I want to be able to email their receipt as a PDF to only that member. This means that the members email address created by the query (tempQry) should be substituted into strEmail each time it is run for a different member.
Can someone help me understand where my logic is incorrect.



Private Sub cmd_CGN_pick_Click()
Dim strQueryCGN As String 'Select CGN from list
Dim strReport As String 'Create report based on CGN selection
Dim strText As String 'prompt to create report
Dim strTitle As String
Dim strSQL As String
Dim qdf As QueryDefs
Dim strEmail As String

strQueryCGN = "qry_receipts"
strReport = "rpt_receipts"
strText = "Do you want to create a receipt"
strTitle = "Receipt of Payment"
strEmail = "tempQry"

DoCmd.OpenQuery strQueryCGN, acViewNormal, acReadOnly

If MsgBox(strText, vbCritical + vbYesNo, strTitle) = vbYes Then
DoCmd.OpenReport strReport, acViewPreview
End If
DoCmd.SetWarnings False ' turns off popup warnings

strSQL = "SELECT qry_receipts.Email FROM qry_receipts;"

On Error Resume Next
DoCmd.DeleteObject acQuery, "tempQry"
' On Error GoTo 0
Set qdf = CurrentDb.CreateQueryDef("tempQry", strSQL)
DoCmd.OpenQuery ("tempQry")

DoCmd.SendObject acReport, strReport, acFormatPDF, strEmail, "", "", "NQCG Payment Receipt", "This Receipt is for 2019 fees", False, ""

DoCmd.SetWarnings True ' turns pop up warning back on
End Sub

 

[Cronk]

Where does the code get the memberID for the particular person.
Easiest would be to pass the MemberID to the function and use DLookup ie

strEmail=dLookup("Email","qry_receipts", "MemberID=" & MemberID)

If the query is already filtered by the MemberID, you don't need to pass the MemberID to the function and use

strEmail=dLookup("Email","qry_receipts")

It is unnecessary to create/open any queries.

 

[arnelgp]

...
...
Set qdf = CurrentDb.CreateQueryDef("tempQry", strSQL)
dim rs as DAO.Recordset
Set rs=qdf.Openrecordset(dbOpensnapshot)
'DoCmd.OpenQuery ("tempQry")
With rs
   If Not (.Bof And .Eof) Then .MoveFirst
   While Not .Eof
     strEMail = !EMail
     DoCmd.SendObject acReport, strReport, acFormatPDF, strEmail, "", "", "NQCG 
       Payment Receipt", "This Receipt is for 2019 fees", False, ""
     .MoveNext
   Wend
End With
rs.Close
set rs=Nothing
set qdf=Nothing
DoCmd.SetWarnings True ' turns pop up warning back on
End Sub

 

[Micron]

Two suggestions that I'd make:
1) repeatedly creating/deleting a table or query increases the risk of db corruption, IMHO. In this case, the suggestion would be to create a temporary (in the truest sense of the word) query def as in
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
Providing an empty string for the name will enable you to use it, but once the sub ends, it's gone because it never becomes a member of the query defs collection.


2) turning off errors without an error trap is also asking for trouble. If you code bombs out before it gets to the point where you turn them on, they remain off. At least insert an error handler that ensures they turn back on. A better approach is to use the .Execute method of the CurrentDb object so that you don't need to turn warnings off. What I'm not getting here is why you turn them off for a Select query anyway.

 

[Agnister]

Thank you everyone for your replies. I am about to work through your suggestions and I will get back to you shortly

 

[Agnister]

Cronk
I got the DLookup to work for a simple field like "Last_Name" in another script but could not get it to work for an email address. When I substituted the Dlookup .... for a real email address it worked. Is there something different about email addresses in a Dlookup?

' Email report to each club member *********
Dim strEmail As String
strEmail = DLookup("Email", "qry_receipts")
' strEmail = "resval@iinet.net.au"

DoCmd.SendObject acReport, strReport, acFormatPDF, strEmail, "", "", "NQCG Receipt", "Your Receipt for 2019 fees", False

 

[Agnister]

Cronk
I am using Outlook as my email host

 

[Agnister]

Anyone
Will this Dlookup work if the field "Email" is in the table as a hyperlink field
' Email report to each club member *********
Dim strEmail As String
strEmail = DLookup("Email", "qry_receipts")
' strEmail = "resval@iinet.net.au"

DoCmd.SendObject acReport, strReport, acFormatPDF, strEmail, "", "", "NQCG Receipt", "Your Receipt for 2019 fees", False

 

[Cronk]

If Email is a hyperlink field, then what is returned from DLookup would be in the format using this example

jsmith@xxx.com#mailto:jsmith@xxx.com#

You'll need to strip out the first bit for using in the Send command.

 

[Agnister]

Thank you all for your help especially Cronk.
I have solved my problem. It took me a while to realise that the Hyperlinks were causing my problem.
Thanks again

 

[Cronk]

What you see isn't always what you get.