troubleshooting Question

Sending email using a query based on a value from a subform produces error

Avatar of Fordraiders
FordraidersFlag for United States of America asked on
Microsoft Access
12 Comments1 Solution238 ViewsLast Modified:
access 2010 vba

I'm trying to send an email in the body of outlook. adding the results from a query.
The query is based on an ID in the subform.

the code is working fine on just a regular query. without "WHERE" clause.

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Query_FOR_HTML")

But when using the following query. I get an error " too few parameters expected 1"
Trying to use value from subform field  RBP_MASTER_ID

SELECT dbo_t_pricing_escalation_detail.RBP_MASTER_ID, dbo_t_pricing_escalation_detail.SKU, dbo_t_pricing_escalation_detail.QTY, dbo_t_pricing_escalation_detail.TARGET_PRICE, dbo_t_pricing_escalation_detail.TARGET_GP
FROM dbo_t_pricing_escalation_detail
WHERE (((dbo_t_pricing_escalation_detail.RBP_MASTER_ID)=[Forms]![Main]![dbo_t_pricing_escalation_detail_subform]![RBP_MASTER_ID]));

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Query_FOR_HTML")

With rst
  Do While Not .EOF
    Mailbody = Mailbody & ![RBP_MASTER_ID] & " | " &   ![Sku] & " | " & ![Qty] & " | " & ![TARGET_PRICE] & vbCrLf
End With

Dim appOutLook As Outlook.Application
   Dim MailOutLook As Outlook.MailItem
   Dim strPath As String
   Dim strFileName As String
   Set appOutLook = CreateObject("Outlook.Application")
   Set MailOutLook = appOutLook.CreateItem(olMailItem)
   With MailOutLook
     .BodyFormat = olFormatRichText
     .To = ""
     '.CC = ""
     '.bcc = ""
     .Subject = "Testing" & Date
     .Body = "UK Backorder Report " & vbCrLf & " " & vbCrLf & Mailbody
End With
Set rst = Nothing
  MsgBox "done"
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros