retrieve multiple data for one criteria - urgent help.

G

Guest

Hi all

I need help for idea how i can send out or list off the records if it met
certain criteria. my code below only can retrieve only 1 current record with
criteria for JPL1. Sometimes it has more than 1.

I know i can use report as an attachement but that will take so much space
which my customer cannot handle. they prefer in text format.

appreciate the help very much.

Best regards
Norhaya

Private Sub JPL1_DblClick(Cancel As Integer)
Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim varbcc As Variant '-- bcc address for sendobject
Dim stText As String '-- E-mail text
Dim sttext1 As String
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stTicketID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim stHelpDesk As String '-- Person who assigned ticket
Dim STCPO As String '-- Customer PO from form
Dim stJPO As String '-- Janco PO from form
Dim stjpl1 As String '-- Janco PO from form
Dim stKEYACCOUNTHOLDER As String '-- Key account holder
Dim stpn, stdesription, stsn As String
Dim sttypeofcert As String
Dim stmatcertno As String
Dim stawb As String
Dim stshipqty1 As Integer
Dim stfltno1 As String
Dim stshipfltdate1 As String
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error
'-- Combo of names to assign ticket to
stWho = Format(Me.BUYER)
varTo = Format(Me.emailadd)
varcc = IIf(Format(Me.SG_SALES) = "AJAY", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "ERIC", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "JOE", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "ERIC", "(e-mail address removed)", ""))))
varbcc = "admin" & ";" & "management"
STCPO = Format(Me.C_PO_NO)
stJPO = Format(Me.J_PO_NO)
stjpl1 = Format(Me.JPL1)
stKEYACCOUNTHOLDER = Format(Me.KEY_ACCOUNT_HOLDER)
stpn = Format(Me.PN)
stdescription = Format(Me.DESCRIPTION)
stsn = Format(Me.SN)
sttypeofcert = Format(Me.[TYPE OF CERTS])
stmatcertno = Format(Me.MATERIAL_CERT_NO)
stawb = Format(Me.AWB1)
stshipqty1 = Format(Me.SHIP_QTY1)
stfltno1 = Format(Me.FLT_NO1)
stshipfltdate1 = Format(Me.SHIP_FLT_DATE1)



stSubject = "Shipping Advice for your PO Ref: " & STCPO & " / Our Ref:
" & stJPO & " for Part # " & stpn

stText = "Dear " & stWho & "," & Chr$(13) & Chr$(13) & Chr$(13) & _
"This is to advise shipping details for following Purchase
Order; " & Chr$(13) & Chr$(13) & _
"Your PO #: " & STCPO & Chr$(13) & _
"Our Ref. : " & stJPO & Chr$(13) & _
"Part # : " & stpn & " Qty " & stshipqty1 & Chr$(13) & _
"Serial # : " & stsn & Chr$(13) & _
"Mat. Cert: " & sttypeofcert & "." & stmatcertno & Chr$(13) &
Chr$(13) & _
"Our PL # : " & stjpl1 & Chr$(13) & _
"HAWB/MAWB: " & stawb & Chr$(13) & _
"Flight # : " & stfltno1 & Chr$(13) & _
"Date Ship: " & stshipfltdate1 & Chr$(13) & Chr$(13) &
Chr$(13) & _
"Best regards " & Chr$(13) & _
stKEYACCOUNTHOLDER & Chr$(13) & Chr$(13) & _
"JANCO Aviation Pte Ltd" & Chr$(13) & _
"10 Anson Road" & Chr$(13) & _
"#24-07 International Plaza" & Chr$(13) & _
"Singapore 079903" & Chr$(13) & _
"Tel: +65 63243248" & IIf(stKEYACCOUNTHOLDER = "norhaya", " x
229", IIf(stKEYACCOUNTHOLDER = "Tammy", " x 213", IIf(stKEYACCOUNTHOLDER =
"mark", " x 230", IIf(stKEYACCOUNTHOLDER = "sally", " x 210",
IIf(stKEYACCOUNTHOLDER = "jennifer", " x 211", IIf(stKEYACCOUNTHOLDER = "ting
bee", " x 228", "")))))) & Chr$(13) & _
"Fax: +65 63243249" & Chr$(13) & _
"email: " & IIf(stKEYACCOUNTHOLDER = "norhaya",
"norhaya.saibi", IIf(stKEYACCOUNTHOLDER = "tammy", "tammy.tan",
IIf(stKEYACCOUNTHOLDER = "mark", "mark.oh", IIf(stKEYACCOUNTHOLDER = "sally",
"sally.chia", IIf(stKEYACCOUNTHOLDER = "jennifer", "jenniferliew",
IIf(stKEYACCOUNTHOLDER = "ting bee", "kwa.tingbee", "")))))) &
"@jancogroup.com" & Chr$(13) & Chr$(13) & _
"For sales enquiries or critical requirement after office
hours/on weekends/public" & Chr$(13) & _
"holidays, please contact us at mobile tel # +65 9796-6435;
email: (e-mail address removed)" & Chr$(13) & _
"This is an automated message. Please do not respond to this
e-mail."
'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, stSubject,
stText, -1
End Sub
 
J

John Nurick

Hi Norhaya,

Instead of getting the various values from fields on your form, you need
to use a recordset containing all the relevant records.

I can't tell from your message whether you have (a) multiple records
each of which needs to be sent to a different recipient, or (b) a single
recipient who needs to get a single email containing information from
multiple records.

For (a), a good starting point is
http://www.granite.ab.ca/access/email/recordsetloop.htm . There's a lot
of useful information on emailing from Access generally at
http://www.granite.ab.ca/access/email.htm


Hi all

I need help for idea how i can send out or list off the records if it met
certain criteria. my code below only can retrieve only 1 current record with
criteria for JPL1. Sometimes it has more than 1.

I know i can use report as an attachement but that will take so much space
which my customer cannot handle. they prefer in text format.

appreciate the help very much.

Best regards
Norhaya

Private Sub JPL1_DblClick(Cancel As Integer)
Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim varbcc As Variant '-- bcc address for sendobject
Dim stText As String '-- E-mail text
Dim sttext1 As String
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stTicketID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim stHelpDesk As String '-- Person who assigned ticket
Dim STCPO As String '-- Customer PO from form
Dim stJPO As String '-- Janco PO from form
Dim stjpl1 As String '-- Janco PO from form
Dim stKEYACCOUNTHOLDER As String '-- Key account holder
Dim stpn, stdesription, stsn As String
Dim sttypeofcert As String
Dim stmatcertno As String
Dim stawb As String
Dim stshipqty1 As Integer
Dim stfltno1 As String
Dim stshipfltdate1 As String
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error
'-- Combo of names to assign ticket to
stWho = Format(Me.BUYER)
varTo = Format(Me.emailadd)
varcc = IIf(Format(Me.SG_SALES) = "AJAY", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "ERIC", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "JOE", "(e-mail address removed)",
IIf(Format(Me.SG_SALES) = "ERIC", "(e-mail address removed)", ""))))
varbcc = "admin" & ";" & "management"
STCPO = Format(Me.C_PO_NO)
stJPO = Format(Me.J_PO_NO)
stjpl1 = Format(Me.JPL1)
stKEYACCOUNTHOLDER = Format(Me.KEY_ACCOUNT_HOLDER)
stpn = Format(Me.PN)
stdescription = Format(Me.DESCRIPTION)
stsn = Format(Me.SN)
sttypeofcert = Format(Me.[TYPE OF CERTS])
stmatcertno = Format(Me.MATERIAL_CERT_NO)
stawb = Format(Me.AWB1)
stshipqty1 = Format(Me.SHIP_QTY1)
stfltno1 = Format(Me.FLT_NO1)
stshipfltdate1 = Format(Me.SHIP_FLT_DATE1)



stSubject = "Shipping Advice for your PO Ref: " & STCPO & " / Our Ref:
" & stJPO & " for Part # " & stpn

stText = "Dear " & stWho & "," & Chr$(13) & Chr$(13) & Chr$(13) & _
"This is to advise shipping details for following Purchase
Order; " & Chr$(13) & Chr$(13) & _
"Your PO #: " & STCPO & Chr$(13) & _
"Our Ref. : " & stJPO & Chr$(13) & _
"Part # : " & stpn & " Qty " & stshipqty1 & Chr$(13) & _
"Serial # : " & stsn & Chr$(13) & _
"Mat. Cert: " & sttypeofcert & "." & stmatcertno & Chr$(13) &
Chr$(13) & _
"Our PL # : " & stjpl1 & Chr$(13) & _
"HAWB/MAWB: " & stawb & Chr$(13) & _
"Flight # : " & stfltno1 & Chr$(13) & _
"Date Ship: " & stshipfltdate1 & Chr$(13) & Chr$(13) &
Chr$(13) & _
"Best regards " & Chr$(13) & _
stKEYACCOUNTHOLDER & Chr$(13) & Chr$(13) & _
"JANCO Aviation Pte Ltd" & Chr$(13) & _
"10 Anson Road" & Chr$(13) & _
"#24-07 International Plaza" & Chr$(13) & _
"Singapore 079903" & Chr$(13) & _
"Tel: +65 63243248" & IIf(stKEYACCOUNTHOLDER = "norhaya", " x
229", IIf(stKEYACCOUNTHOLDER = "Tammy", " x 213", IIf(stKEYACCOUNTHOLDER =
"mark", " x 230", IIf(stKEYACCOUNTHOLDER = "sally", " x 210",
IIf(stKEYACCOUNTHOLDER = "jennifer", " x 211", IIf(stKEYACCOUNTHOLDER = "ting
bee", " x 228", "")))))) & Chr$(13) & _
"Fax: +65 63243249" & Chr$(13) & _
"email: " & IIf(stKEYACCOUNTHOLDER = "norhaya",
"norhaya.saibi", IIf(stKEYACCOUNTHOLDER = "tammy", "tammy.tan",
IIf(stKEYACCOUNTHOLDER = "mark", "mark.oh", IIf(stKEYACCOUNTHOLDER = "sally",
"sally.chia", IIf(stKEYACCOUNTHOLDER = "jennifer", "jenniferliew",
IIf(stKEYACCOUNTHOLDER = "ting bee", "kwa.tingbee", "")))))) &
"@jancogroup.com" & Chr$(13) & Chr$(13) & _
"For sales enquiries or critical requirement after office
hours/on weekends/public" & Chr$(13) & _
"holidays, please contact us at mobile tel # +65 9796-6435;
email: (e-mail address removed)" & Chr$(13) & _
"This is an automated message. Please do not respond to this
e-mail."
'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, varcc, varbcc, stSubject,
stText, -1
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Send email message from Access 1

Top