Setting approval/denial for changes made to forms to be implemented in a table?

[robtech4]

I have a database created with a master client table and forms to view the client information and currently changes can be made via the forms. I need to lock the database so that others can still view all the forms but if they make a change, an email will be sent to me through outlook – so I can either approve or deny the changes. Essentially I don’t want anyone to be able to finalize the changes in the “master list” until I approve them.

Please help me in finding the easiest way to do this. I have very limited experience in VBA but have been working very hard at it in the past week.

Much thanks in advance.

[Rabbit]

Because of the nature of Access being both the server and client, and because of it's poor security features, you can't really lock the user out of the data. You can make the form read only but they can always change it in the underlying tables.

[twinnyfo]

robtech4,

One potential option would be to have the user be able to view the data, and any changes they made would be added to another table with identical fields. Then, after the changes, the e-mail goes to you. You pull up an adminstrator level of the form which compares the two entries. You could be able to then approve all, some or none of the changes via command buttons. Either way, this sounds pretty involved.....

Another option would be to simply have a memo field in the record, which is the only updateable field on the form. Then, when anyone updates that field, an e-mail is sent and you could review as necessary from an adminstrator form.

That might get you pointed in the right direction. There may be multiple ways to skin this cat, some more efficient than others.

[robtech4]

Twinnyfo,

Wow, great solution! If I were to create a replica form and below each field insert another box (updateable field), what would be the process for setting up the "approve/deny" button so that once the "approve" button is clicked, the master list would automatically change out the old information for the new information? Also, could you please advise on the most convenient way to set up the email button.

Again, your time and expertise are much appreciated.

@twinnyfo

[twinnyfo]

robtech4,

The simplest solution would be to have code associated with either the AfterUpdate event (which would send you an e-mail after EVERY update to a record), or, better yet, after the user makes their recommended changes, they click a "Submit" Command Button which would run code behind the scenes.

If you are thinking a replica form, I would also suggest that you have two tables that are identical (with additional fields for the user requesting changes and a reason), with the primary key of the original table serving as a key for the second. So that when you set the record source for the forms, a subform with the secondary table's data would be linked Master/Child. Your approval would update the original table. Your choice on what to do with the request after you're done.

For sending the e-mail, DoCmd.SendObjec t works pretty easily, with acSendNoObject as the first argument. It's really easy to create a canned response for the body. I'm just very familiar with that method because I usually send reports as attachments, but there are other methods.

Experiment a bit with these tables/forms/code and let us know what you come up with. If you hit serious road blocks, I am glad to continue to assist.

Warmest regards

[robtech4]

Twinnyfo,

I am working on splitting the database and using two seperate passwords for the front and back ends of it in order to eliminate visibility of other users. I think it may work, or I am heading straight into a death trap...not too sure. Worst case scenario I can probably hide the navigation pane for the other users since they all have just about no access experience.

As far as the email capability I am just about to get started on that part.

Thank you for your pointers as they have been very helpful thus far.

-Rob

[twinnyfo]

Rob,

Also keep in mind that splitting a database has to do with functionality and protecting your data, not security. If you establish two passwords, your users will still need to access that back end in order to update data. No mater where you put your BE, your users must have access to it, and if they have access to it, they can find your data and ultimately mess with it. We just hope, as programmers, that they know little enough to just open the database FE that WE give them and use the forms provided.

I know this is going away from the original question, but I hope the mods will allow this brief discussion, because I don't want you to think that the FE/BE split solves security issues. It just prevents you from losing data if the FE crashes (which happens far more often for the FE than the BE).

You should still hide the navigation pane, as long as YOU know how to access the data tables and their properties safely.

I hope this gives a bit of insight into why you may be doing what you are doing. It is a good practice... just be aware of why you do it.

Regards,
twinnyfo

[robtech4]

Twinnyfo,

To say the least you were correct, as I tested the database after I found you would need both passwords.

Back towards my original question...
Now that I have been able to create a form to update my master list and a table that records everychange I have encountered a new issue. Although I can see what changed, I can't stop it or revert it back without going into the master table and doing it manually. Is there an easy way to create a form to show me the original data vs. the changed data (currently have this in a table) but I want a form to view each change one-by-one with a button to approve--move new data into the master table, or deny--disregard the changed information and do nothing to the master table.

Again, you have been of great help! Thank you!

-Rob

[twinnyfo]

Rob,

For clarity, I will refer to the data that you have control over (the "real" data) as the master table and the changes requested as the "replica" table.

In your case, I would never allow the user the ability to modify the actual data in the master table. When they want to modify a record, create a new record in the replica table based on data in the master table. This pops up in a form that allows them full access to edit it, because it is the replica table and never touches the master table.

When they complete their updates, they click submit, which saves the data to the replica table, sends you and e-mail and closes the form.

YOUR form would list BOTH sets of data, from the master table and from the replica table. You could even set up conditional formatting so that any entries that differ from the master table show up in red or yellow. A rather advanced way to do it woul dbe to have command buttons associated with every field, which would have their captions updated with the replica table's data and simply clicking those buttons would update the master table.

As you see, you could really allow your imagination to play with this one. I think you are on the right track here. Glad to assist in your project!

-Greg

[robtech4]

Greg,

I now have the audit set up to record changes in the replica table without altering the master table and it is working great.

As I attempt to create a form to list both sets of data to show the changes made I am having much trouble. The sheet has 63 columns which each contain about 15 characters of data. Everytime I try to create a form it says it is too big. Also, I love the idea of the conditional formatting and will try to figure out how to implement the command buttons for each field for the approve/deny feature.

I know I have asked a lot already but I am also struggling with the code for sending the email when clicking the 'submit' button. Any advice on this would be very helpful.

Thank you! Thank you! Thank you!

-Rob