Is there a way to get a field [Approval] to store response based on email reply

[Abouya]

Hello,

I'm implementing this simple database where users will fill in a form, them they click save and a report appears to summarize their info, then they send this i have another button in the report that gives a list of emails to choose from. This database is used for orders, sometimes, a user will need approval first. Is there a way to set up [Approval] field to autopopulate depending on the recipient reply and store his response in the database?

Thanks in advance for your help.

 

[sneuberg]

Hello,

I'm implementing this simple database where users will fill in a form, them they click save and a report appears to summarize their info, then they send this i have another button in the report that gives a list of emails to choose from. This database is used for orders, sometimes, a user will need approval first. Is there a way to set up [Approval] field to autopopulate depending on the recipient reply and store his response in the database?

Thanks in advance for your help.

Autopopulate with what? What do you mean by "recipient reply"? What determines whether approval is required?

Please give us a more detail explanation of what you want to happen.

 

[Abouya]

Hello Sneuberg,

this field will auto populate with email recipient reply. for example, I'm filling an order and the total of this order exceeds my spending limit, so i need an approval so that my order will change from pending to approved. i don't know how this could be possible by changing the status from pending to approved by just getting an email from the supervisor saying approved.

you always come to my rescue. Thank you so much.

 

[sneuberg]

I don't know how you could make Access the recipient of emails albeit it's an interesting idea. Maybe some other forum member can contribute something for this.

What I'd do is give the supervisor access to the database with a level of access (you'd need to develop this) that would allow him (and others with that level of access) to change the field from Pending to Approved. The the code that detects that an approval is needed would set the field to Pending and then email the supervisor telling him and there is an order than needs to be approved He would run the Access application and based on his UserId (being that he's a supervisor) a form would open with the order(s) needing his approval. He'd click a button to approve the order(s) and this would change the status to Approved. Maybe you would want that button to send an email back to the originator (guy seeking approval) of the order and do other things.

 

[Abouya]

I don't know how you could make Access the recipient of emails albeit it's an interesting idea. Maybe some other forum member can contribute something for this.

What I'd do is give the supervisor access to the database with a level of access (you'd need to develop this) that would allow him (and others with that level of access) to change the field from Pending to Approved. The the code that detects that an approval is needed would set the field to Pending and then email the supervisor telling him and there is an order than needs to be approved He would run the Access application and based on his UserId (being that he's a supervisor) a form would open with the order(s) needing his approval. He'd click a button to approve the order(s) and this would change the status to Approved. Maybe you would want that button to send an email back to the originator (guy seeking approval) of the order and do other things.

sounds like a good solution, the only issue i see here is how it will be possible for the supervisor to review only the pending requests, what if there is more than one supervisor! each employee reports to different supervisor! what if the supervisor himself fill out orders too, is this shouuld be a new form other than the one everyone will be using to fill in forms?

 

[sneuberg]

sounds like a good solution, the only issue i see here is how it will be possible for the supervisor to review only the pending requests

The record source query of the form would have Status = 'Pending' as part of the criteria."

what if there is more than one supervisor! each employee reports to different supervisor! what if the supervisor himself fill out orders too,

These are management questions? I don't have an opinion. Once you have decided on the rules you will just need to implement a solution.

is this shouuld be a new form other than the one everyone will be using to fill in forms?

I'd think it would have to be if you want to limit who can approved orders. Maybe even a different application. If your requirements are simple it might be easier to distribute different frontends than to develope a traditional security system.

 

[Abouya]

i looks like it will be difficult to implement. I'm thinking about adding an attachment field that will save emails from supervisors and attach it to their designated OrderID. Is this something possible to automate in access?

 

[sneuberg]

How would you want that to work? Would the Access user access his Outlook Folders, e.g. Inbox and select an Email to attach? That's certainly possible but not so easy to implement either. If you want to pursue that approach let me know and I'll give you some links for Outlook Automation.

 

[Abouya]

How would you want that to work? Would the Access user access his Outlook Folders, e.g. Inbox and select an Email to attach? That's certainly possible but not so easy to implement either. If you want to pursue that approach let me know and I'll give you some links for Outlook Automation.

yes, i have a send email button in a report and it does attach the report to the email. outlook opens and he can send it to others. but how access can save email attachment after he replies as approved and save it into access table corresponding order ID?

 

[The_Doc_Man]

Here is my take on this:

In order to use an e-mail to approve a message, you need to have a VERY trusted environment OR you need to be able to examine the user's digital signature on the e-mail OR you will possibly be "spoofed" if someone decides to inject a fake approval e-mail in the chain. FURTHER, you would need to learn about Outlook Application objects so that you could open your user's InBox and start reading e-mails to check for the presence of a particular content - a string or a number or something - to identify what it is that is being approved. Manipulating Outlook Application objects is not trivial.

The idea of using a different form or some other way of getting approvals from your supervisors by letting them directly use the database might be better. Doing it this way involves having a table of users who can log in to your system and who have the role required for approvals. Search this forum for recent posts on implementing user roles to see hot that is done.

looks like it will be difficult to implement.

Abouya, either way you go (Outlook or let approvers have access), it will be tricky. We can't make the choice for you, though. It's your environment and your requirements, which we shouldn't critique. Once you choose your path, you can CERTAINLY come back with questions.

 

[sneuberg]

but how access can save email attachment after he replies as approved and save it into access table corresponding order ID?

I think we are back to the situation where I pled ignorance. I don't know how you could get Access to receive email no less save it somewhere. You might be able write a Outlook macro (that's really VBA) that fires on the NewMail Event, looks at the incoming mail for some flag (maybe OrderID = XXX in the subject line), and then updates the table in Access. But this macro would have to be installed in the Outlook of all users that would receive these emails and I'm pretty sure this would only function when they had Outlook open. That might not be a problem. I think most people leave it open. Again if this is what you want to do I'll post some links.

 

[Abouya]

I think we are back to the situation where I pled ignorance. I don't know how you could get Access to receive email no less save it somewhere. You might be able write a Outlook macro (that's really VBA) that fires on the NewMail Event, looks at the incoming mail for some flag (maybe OrderID = XXX in the subject line), and then updates the table in Access. But this macro would have to be installed in the Outlook of all users that would receive these emails and I'm pretty sure this would only function when they had Outlook open. That might not be a problem. I think most people leave it open. Again if this is what you want to do I'll post some links.

That sounds like something i will like to give it a try. The vb code needs to run just in one computer since tgey will always CC the controller. Ease post any link related to implementing such funtionality. Thanks a lot.

 

[Abouya]

Here is my take on this:

In order to use an e-mail to approve a message, you need to have a VERY trusted environment OR you need to be able to examine the user's digital signature on the e-mail OR you will possibly be "spoofed" if someone decides to inject a fake approval e-mail in the chain. FURTHER, you would need to learn about Outlook Application objects so that you could open your user's InBox and start reading e-mails to check for the presence of a particular content - a string or a number or something - to identify what it is that is being approved. Manipulating Outlook Application objects is not trivial.

The idea of using a different form or some other way of getting approvals from your supervisors by letting them directly use the database might be better. Doing it this way involves having a table of users who can log in to your system and who have the role required for approvals. Search this forum for recent posts on implementing user roles to see hot that is done.



Abouya, either way you go (Outlook or let approvers have access), it will be tricky. We can't make the choice for you, though. It's your environment and your requirements, which we shouldn't critique. Once you choose your path, you can CERTAINLY come back with questions.

Thank you so much for helping out. I believe that user role is an excellent solition, i will try to create a form that relates to a query table in which just supervisors can access to approve orders of their employees. Each employee has a supervisor so, i should find a way to on load event only employees orders that are pending and relates to their them. I have a login with password and i have already set up userLevels: Admin and normal user.

Donyou have any idea how i can design this user role feature?

Thank you so much for helping out. I really appreciate.

 

[sneuberg]

That sounds like something i will like to give it a try. The vb code needs to run just in one computer since tgey will always CC the controller. Ease post any link related to implementing such funtionality. Thanks a lot.

Sound like you are going the other way on this and I think that's the easier approach but in case you change your mind here's some links that might help.

Update Access from Outlook:

http://www.utteraccess.com/forum/Outlook-VBA-Access-t1993848.html
https://www.experts-exchange.com/questions/28289787/Gather-Information-from-Access-accdb-Using-Outlook-VBA-then-Insert-new-Record.html

General Outlook Automation:
Outlook Object Model Reference

 

[The_Doc_Man]

Donyou have any idea how i can design this user role feature?

See this thread regarding the issue of identifying who is using the application.

http://www.access-programmers.co.uk/forums/showthread.php?t=290208

As I discussed in the thread, if you are in a domain-login environment, the odds are that your domain authentication will be far more rigorous than whatever password-based scheme you implement in Access. That is not meant to disparage any password-based system you designed, but it is a way to assure that you don't re-invent too many wheels at one time.