Fordraiders
asked on
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_d etail.RBP_ MASTER_ID, dbo_t_pricing_escalation_d etail.SKU, dbo_t_pricing_escalation_d etail.QTY, dbo_t_pricing_escalation_d etail.TARG ET_PRICE, dbo_t_pricing_escalation_d etail.TARG ET_GP
FROM dbo_t_pricing_escalation_d etail
WHERE (((dbo_t_pricing_escalatio n_detail.R BP_MASTER_ ID)=[Forms ]![Main]![ dbo_t_pric ing_escala tion_detai l_subform] ![RBP_MAST ER_ID]));
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_
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_d
FROM dbo_t_pricing_escalation_d
WHERE (((dbo_t_pricing_escalatio
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"
ASKER
error type mismatch on line:
set qd = MyDB.OpenRecordset("Query_ FOR_HTML")
fordraiders
set qd = MyDB.OpenRecordset("Query_
fordraiders
Sorry, I copied your code and forgot to change the relevant part.
set qd = MyDB.Querydefs("Query_FOR_ HTML")
set qd = MyDB.Querydefs("Query_FOR_
ASKER
the first part of the parameters statement is saying "item not found in this collection".
so i change it to:
qd.Parameters("dbo_t_prici ng_escalat ion_detail .RBP_MASTE R_ID"), but still did not like it ?
Thanks
fordraiders
so i change it to:
qd.Parameters("dbo_t_prici
Thanks
fordraiders
ASKER
tried this also :
qd.Parameters![RBP_MASTER_ ID] = [Forms]![Main]![dbo_t_pric ing_escala tion_detai l_subform] ![RBP_MAST ER_ID]
qd.Parameters![RBP_MASTER_
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_su bform].For m.Recordse tClone
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 ?
Dim dQuery As String
Dim rs As DAO.Recordset
Set rs = Me![dbo_t_redbook_pricing_
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 ?
ASKER
Pat, That worked! Consider looking at the last comment from me please. just curious.
ASKER
Thanks very much !
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.
ASKER
Aaah. Duh. Thanks
Been there, done that. Got the dent in forehead to prove it:)
Open in new window