One click will do the job !!! BUT HOW ?

[Westside1st]

Hello everyone,

I have a form that my company working on .. and it looks great but we missing one thing.
I want it when I approve the request it will get all the data from the query but the query is not the record source :

this is what I have now

Dim msg As String

msg = "Dear " & Created_By & ", <P>" & _
"Your tickets no. <P>" & _
((" I want to get the tickets no. from (void requests query) and the field name is tkt_no ))
there is a relationship between (tblVoid Requests) and (tblVoid_Tkt_No) the field (REQ_NO)

I hope that I explained it well

 

[welshgasman]

I hope that I explained it well

Sadly not to me?
I believe you will need to give much more info than that.
I always concentrate on getting something working correctly, before I try and make it pretty.

 

[Micron]

Me neither, but can suggest that DLookup might be what you need.

 

[Westside1st]

I figured it out but still having an issue in the same email sending.

Now ... this is what I'm having in my VBA:

Dim Msg As String

Msg = "Dear Mr." & Me.[Created_By] & ", <P>" & _
"Ticket no. " & Tkt_No & " changed to void as you requested in request no. " & REQ_NO & ". <P>" & _
"Regards <P>" & _
"--------------------------- <P>" & _
"--------------------------- <P>" & _
"--------------------------- <P>" & _
"--------------------------- <P>" & _
"<P>" & _
"Dear Mr.Ahmed Bahattab, <P>" & _
"Please adjust the above voided tkts in request no. " & REQ_NO & " in SAP to void <P>" & _
"Regards"


Dim O As Outlook.Application
Dim M As Outlook.Mailitem

Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

With M
.BodyFormat = olFormatPlain
.HTMLBody = Msg
.To = Me.[Created_By] & ";" & E_N
.CC = "ahbahattab@saudia.com"
.Subject = "VOID REQUEST NO." & REQ_NO
.Display

End With

Set M = Nothing
Set O = Nothing


End Sub


The problem in this code is when I'm in request no.29 as you can see in the uploaded image and if I click the send email button.... The below response is what happen:

Dear Mr.ALBALAWI, HANI S,

Ticket no. 0654206921221 changed to void as you requested in request no. 29.

Regards


The issue here is that request no.29 had three tickets number but my code only showing the first ticket ........ How can I make it to add the three tickets under the request no.29 ????



Great Community
Thanks in Advance

 

[welshgasman]

You would need to read every record for whatever number you are using and concatenate into a single string, pehaps seperated by a ;

Use DCount() to see if concatenation is required. No point doing it if not.

 

[Westside1st]

Thanks welshgasman for your reply.

Could you check my access file and give me your opinion about it and maybe the solution please !!

I upload it in another thread.

Go to next (UNIQUE) record

Hello experts I'm having an issue solving this puzzle I have a query which include linked tables (please check the uploaded Image) In the form I had a command button that had a VBA code On Click DoCmd.GoToRecord , , acNext My issue is that the REQ_NO field contain a duplicate...

www.mrexcel.com

I appreciate it
Thanks

 

[welshgasman]

No, I do not do that. Mainly for the reason that you do not learn anything.

I will however tell you the steps I would take.
I issue a Dcount() for the criteria you require ie "Req_No = " & Req_No 'not sure where you get the second req_no from?, I would be using Me.Reqno from a form, more than likely?
If the result of that Dcount is > 1, then you need to get the rest.

For that I would probably create a function, but for now you can just code with your other code.
Open a recordset for the above criteria selecting tkt_no as the field to retrieve.
Walk through that recordset until EOF, concatenating the field with ","

At the end you should have multiple ticket numbers separated by the comma.
Now you can remove the last comma which is not needed, by using Left() and Len() functions on your string variable.
Close the recordset
Then you concatenate string variable into your message.

That is how I would approach it, and have done so in the past.
I actually took count of how many records I processed and edited the subject to indicate how many items were involved.

 

[Westside1st]

Thank you welshgasman.
I'll do my best and follow your instructions and learn it.

 

[welshgasman]

Well post back with your attempt, within code tags as well please.
Take it one step at a time. F8 and the immediate window with debug.print are your friends, F8, the biggest helper to see where things go wrong.