Access Table/Query (Sending email notification upon change)

[NeroMaj]

I have an access database set up with a main table and several queries. This database is on a server at my work and all of the employees constantly update the status of certain things in it.

I want to write some type of VBA that will automatically send me a report whenever something is changed that is related to me. For instance, when status changes, it will check that value and if its a certain value, then it will check to see if its associated with me, and if it is, then I want it to email me.

I have a general idea how to do the checks and the emailing, but I am having trouble setting up the "detect change" part of it.

I know there is the OnChange method, but i'm not sure how to use it or if it is even possible to use it in this instance.

Any thoughts would be great.

 

[DJkarl]

If your users are entering/updating data via a bound form look into the AfterUpdate event. If they are directly editing the tables then you are out of luck.

 

[NeroMaj]

This is how it is set up. There is a main table with all of the information. Then there are 10-12 queries, each specific to the user that is using the database. There is a form, but it is only used for putting in new entries. Typically, a user will go into the database, pull their query, and change the status there.

Does this still make me SOL?

 

[DCrake]

Users should be uing forms to add,edit & delete data. Going directly to the query is not the answer. You can base the forms underlying rcordsource on the users own query, but again you need to ask yourself the question why have a different query for each user? have one query with parameters that filter out records based on the user. What is stopping the uer from opening up someone else's query and amendig that? Not very secure is it?

 

[NeroMaj]

Security isn't really an issue in this case. It is a group of 6 or 7 people and everybody is allowed to edit all fields (all trusted users). The problem with forms is that you either have to scroll through the records to find the one you want to change, or add some find/lookup procedure to change it. By changing it through the query, each user can quickly switch a status without having to look it up.

So, I guess the real question is: Without using Forms AfterUpdate method, is it possible to detect a change (something similar to WorksheetChange Event in Excel) in a table entry, and then have that change trigger an email to the user describing the change?

 

[DCrake]

In short NO, Acces is Access Excl is Excel. Form were designed to do what you are puproprting to do in a query. A query is a way of presenting data derived from tables including caclulations and determining orders.