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

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

LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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
FordraidersAuthor Commented:
error type mismatch on line:
set qd = MyDB.OpenRecordset("Query_FOR_HTML")

fordraiders
0
PatHartmanCommented:
Sorry, I copied your code and forgot to change the relevant part.

set qd = MyDB.Querydefs("Query_FOR_HTML")
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

FordraidersAuthor Commented:
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
FordraidersAuthor Commented:
tried this also :
qd.Parameters![RBP_MASTER_ID] = [Forms]![Main]![dbo_t_pricing_escalation_detail_subform]![RBP_MASTER_ID]
0
PatHartmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
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
FordraidersAuthor Commented:
Pat, That worked!   Consider looking at the last comment from me please. just curious.
0
FordraidersAuthor Commented:
Thanks very much !
0
PatHartmanCommented:
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
FordraidersAuthor Commented:
Aaah. Duh.  Thanks
0
PatHartmanCommented:
Been there, done that.  Got the dent in forehead to prove it:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.