Solved

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

Posted on 2016-08-24
12
36 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 37

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 37

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
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.

 
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 37

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 37

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 37

Expert Comment

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

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

752 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