Solved

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

Posted on 2016-08-24
12
33 Views
Last Modified: 2016-08-24
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
      .MoveNext
  Loop
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 = "xxxell@xxxxxger.com"
     '.CC = ""
     '.bcc = ""
     .Subject = "Testing" & Date
     .Body = "UK Backorder Report " & vbCrLf & " " & vbCrLf & Mailbody
            
     .Send
End With
rst.Close
Set rst = Nothing
  
  MsgBox "done"

Open in new window

0
Comment
Question by:fordraiders
  • 7
  • 5
12 Comments
 
LVL 36

Expert Comment

by:PatHartman
ID: 41769039
You have to expressly set the parameter values.
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim qd as DAO.QueryDef
  
Set MyDB = CurrentDb
set qd = MyDB.OpenRecordset("Query_FOR_HTML")
    qd.Parameters("[Forms]![Main]![dbo_t_pricing_escalation_detail_subform]![RBP_MASTER_ID]") = [Forms]![Main]![dbo_t_pricing_escalation_detail_subform]![RBP_MASTER_ID]
Set rst = qd.OpenRecordset 

Open in new window

0
 
LVL 3

Author Comment

by:fordraiders
ID: 41769058
error type mismatch on line:
set qd = MyDB.OpenRecordset("Query_FOR_HTML")

fordraiders
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41769204
Sorry, I copied your code and forgot to change the relevant part.

set qd = MyDB.Querydefs("Query_FOR_HTML")
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:fordraiders
ID: 41769260
the first part of the parameters statement is saying "item not found in this collection".
so i change it to:
qd.Parameters("dbo_t_pricing_escalation_detail.RBP_MASTER_ID"), but still did not like it ?

Thanks
fordraiders
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41769283
tried this also :
qd.Parameters![RBP_MASTER_ID] = [Forms]![Main]![dbo_t_pricing_escalation_detail_subform]![RBP_MASTER_ID]
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41769370
When I pass parameters this way, I don't use the form reference in the query.  I modify the query.

So, I would change the query to:
WHERE (((dbo_t_pricing_escalation_detail.RBP_MASTER_ID)=[EnterMasterID]));

That way, I have no spaces or special characters to mess up the code.  Then

qd.Parameters!EnterMasterID = [Forms]![Main]![dbo_t_pricing_escalation_detail_subform]![RBP_MASTER_ID]

Or, even simpler if you are running the code from the subform:

qd.Parameters!EnterMasterID = Me.RBP_MASTER_ID
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41769375
pat, just curious, trying your suggestion now...but may try a different approach:  looping directly in subform and getting the data.
Dim dQuery As String
Dim rs As DAO.Recordset

Set rs = Me![dbo_t_redbook_pricing_escalation_detail_subform].Form.RecordsetClone

With rs
Do While Not .EOF
     Mailbody = Mailbody & rs![Sku].Value & vbTab & rs![Qty].Value & vbTab & rs![TARGET_PRICE].Value & vbCrLf
Loop

Set rs = Nothing
End With

now the loop  just keeps going on forever ?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41769382
Pat, That worked!   Consider looking at the last comment from me please. just curious.
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41769385
Thanks very much !
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41769418
You're welcome. The loop goes forever because it doesn't include a rs.MoveNext to move to the next record so it never gets to eof.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41769422
Aaah. Duh.  Thanks
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41769485
Been there, done that.  Got the dent in forehead to prove it:)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question