Solved

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

Posted on 2016-08-24
12
31 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 35

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 35

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 35

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 35

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 35

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

861 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