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

Westside1st

New Member
Joined
Oct 17, 2022
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
Me neither, but can suggest that DLookup might be what you need.
 
Upvote 0
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
 

Attachments

  • Records.JPG
    Records.JPG
    52 KB · Views: 8
Upvote 0
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.
 
Upvote 0
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.


I appreciate it
Thanks
 
Upvote 0
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.
 
Upvote 0
Thank you welshgasman.
I'll do my best and follow your instructions and learn it.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top