Solved

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

Posted on 2016-08-24
12
39 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 38

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 38

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 38

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 38

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 38

Expert Comment

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

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

636 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