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.SendObject 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

[twinnyfo]

Rob,

I think right now, your question has split into two distinct issues: The amount of data that you can comfortably fit on a form (which is sounds like there is a lot), and the e-mail issue. There are several creative ways to solve the first, but we can address later if you still have issues. I'd be glad to help on that.

Concerning the e-mail, the first step is to simply set up a command button and slowly test your settings, then gradually expand on it. Of course the place to start is the beginning:

Option Explicit
Option Compare Database
 
Private Sub cmdSubmit_Click()
On Error GoTo EH
    Dim strSubject As String
    Dim strAddressees As String
    Dim strEMailBody As String
    strSubject = "Changes Requested"
    strAddressees = "[e-mail]"
    strEMailBody = "Test Message"
    DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddressees, , , strSubject, strEMailBody, True
    Exit Sub
EH:
    MsgBox "There was an error sending this note!  Please contact your Database Administrator.", vbCritical, "Error!"
    Exit Sub
End Sub
 

When you test this, your default e-mail program (probably Outlook) will generate an e-mail that will pop up and you can edit (the True argument at the end of the SendObject method causes the e-mail to pop up for editing first, otherwise, if you set this to False, the user will not see it at all (which is probably what you will want).

This is bare bones, but you can modify as necessary. There are other methods for sending e-mail, but I have grown comfortable with SendObject, although there are some limitations.

Please let me know how this progresses. If you need additional assistance with the data on the forms, let me know, and we can try to work through that as well....

Cheers,
Greg

[robtech4]

Greg,

I literally cannot thank you enough, email code worked like a charm.

Regarding the form issue, it really is a lot of data but all of the information is vital. I am thinking if I can make the table columns the rows in the comparison form, it will not look too bad. I will never be comparing more than two rows at a time so it would involve some scrolling down on the form to view but that's the only way I can think of doing this. Please advise as your advice is ALWAYS beyond helpful.

With Appreciation,

Rob

[twinnyfo]

Wow, 63 columns, 15 characters each!

I wonder if it would be prudent to use a Tab Control, grouping similar items. For example, if the data were on a person, you could group data for personal identification info, then address info, company info, etc. Each type of similarly grouped data would be on a separate tab. Best practices would actually have these data in separate tables. I think about 40 fields is about my limit.

I know the form wizardsd can create table based forms very quickly for you in various designs, and then you can just modify them by hand. This might be the quickest way for your to start, rather than hand placing and naming 63 controls and their labels (not to mention on two separate forms.

The forms could, however, essentially be duplicates, as the data tables "should" have the same field names. Then, you simply change the record source ofr one form to read from the master table, the other form to read from the replica table.

I envision a very large, wide form, so font size may be a constraint, depending on your monitors, etc.

Let me know how you progress through this, and attach either a screen shot or sample database to show us what you've got...

[robtech4]

Hey,

I figured how to show the data of both tables in one form (inner join).

Now what I have been stuck on for most of the day is:

1) How do I implement the conditional formatting for data which has changed
2) How do I implement the button to deny a change (delete it from the audtable) or accept (delete it from audtable and write it to the master table). My idea was to make check boxes behind every comparison, and all comparisons with a check get updated!

(screen shots are soon to come!)

Regards

[twinnyfo]

Rob,

1) For conditional formatting, find the control you want to apply it to (i.e. the text box) and from the Form Design Tools, Design Tab, Font Group, select Conditional formatting. You will probably have to play with the formats and syntax for a bit, but you need to set check to see if that text box is not equal to its corresponding text box from the other table. Play with some sample syntax first (i.e. = "Smith") to get it working forst, then refer to the other control.

2) The option for updating, which you suggested, would probably not be too difficult. Add those check boxes, then an Update button that would cycle through the Replica Controls, and every time it finds a check box set to True, it will copy those values to the master table. The easiest way to do this is to have very exacting naming conventions for your controls (for both tables) on the form. Here is an example of what I mean:

Master table, Field1 has the following controls on the form:
txtField1
lblField1

Replica table fields on the form:
txtReplicaField1
lblReplicaField1
chkReplicaField1 (this does not need to be tied to the record source, as long as you reset them with each record.

Below is sample code of how you might go about this....

Option Explicit
Option Compare Database
 
Private Sub cmdSubmit_Click()
On Error GoTo EH
    Dim I As Integer
    For I = 1 To 63 'You have 63 controls to go through
        If Me("chkReplicaField" & I) Then
            Me("txtField" & I) = Me("txtReplicaField" & I)
        End If
    Next I
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub
 

Hope this helps!

[robtech4]

This helps tremendously, I will be sure to get working on it and let you know about the progress. Again, all the help has been awesome!

Thank you.

[robtech4]

Greg,

Do you think you could provide some additional sample code for the check box. I am having trouble making the previous code you provided work.

I am looking to have it set so once the check box is clicked and the update button is pressed, the data will be deleted out of the audit table and updated in both master table and the replica table (display forms feed from master table only--as you reccommended in the past!).

Thank you for your patience with me! This is my first time utilizing access so I hope my questions have not become too agitating.

Thank you,

Rob

[twinnyfo]

Rob,

I am quite certain I did not make myself clear much earlier in this post, that the two forms you create (one for master data and one for replica data) would be nested. The master form would have a subform housing the replica data. They would look essentially identical, except that the replica form would have check boxes next to each text box and a submit button. These should have Master/Child relationship based on the Primary Key of the Master table (should also be in the Replica Table).

I also apologize for leading you a bit astray on this one, because for some reason, I was having all data on one form--which would just not work based on anything we've discussed--has to be a main form and a sub form.

After my mistake, I will also help with fixing.... The two forms, then, should have identical control names (you can essentially copy and paste the entire form that looks at the Master table and simply change the record source to the replica table. Then, add the checkboxes with appropriate control names.

The code for this would then change slightly also. This would be on the subform's Submit Button On Click event.

Option Explicit
Option Compare Database
 
Private Sub cmdSubmit_Click()
On Error GoTo EH
    Dim I As Integer
    For I = 1 To 63 'You have 63 controls to go through
            If Me("chkField" & I) Then
            Me.Parent.Form("txtField" & I) = Me("txtField" & I)
        End If
    Next I
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub
 

I think, perhaps, I must thank you for patience with me, as I am sure I confused you terribly in previous posts. This one should get you much closer to your desired outcome. Hopefully, the correction will be rather quick, though.

Let me know how this works for you.

[dsatino]

Not to intrude, but this all seems overly elaborate. Why not just have a table that stores only the proposed changes? This table would only need 3 fields: key,field, and data. The 'key' field would of course be the primary key of the record to change, the 'field' field would be the name or index of the field to change, and the 'data' field would be the proposed new value.

In this way, you don't need to duplicate an entire table, your Accept/Reject form could be automatically filtered by using a simple join, and you only need to view the field(s) for possible edits.

[twinnyfo]

Rob,

Here is a very brief example. This only uses ten fields, but should give you the idea. Coding-wise, it is very simple. I think dsatino's recommendation would work well, but may be beyond your VBA skills for right now. This will get you started in understanding a bit about the forms and the code behind them.

Your forms will probably be huge, because you have 63 fields. It is also not reasonable to have all the fields called Field1, Field2, etc. They should have some form of recognizable name, although the controls that house the data may have any name you like, in order for this code to work.

I am still unsure what you desire with your "Audit" table, as two tables should be all you need. Also, this set up, right now, also cycles through all records, whether they have changes or not. You may want to play with filters or queries behind the record sources to limit that. You can also have a field in tblReplica that identifies whether a change has been acknowledged, so you won't see that record again. There are lots of ways to do this, and we can work through those details if you wish.

Let me know if this attachment helps. It is in MS Access 2007.

[robtech4]

Slowly but surely I am making my way through this, without the attached file I would be getting nowhere as it is very helpful.

1) Can you explain what you mean by the second paragraph? Do my columns have to be Field1...etc or are you saying that my rows have to?
--currently my rows are per company and the columns are information about the company.

2) The reason I want to use the audit table instead of the replica table is because there are a large number of companies and cycling through them all would take a long time. So I have my audit table set to show only information that was entered into the replica table. Hopefully this will still force the cycle through all of them but it will only be relevant changes since the audit table only shows changes.

And yes, I would love some assistance in working through these details!

[twinnyfo]

1) To clarify and standardize, Fields are in tables (i.e. "columns") and controls are on forms. Controls may refer to a specific field in a table. Normally, one would NEVER want to name their table's field names as "Field1" and "Field2" because there's no way of knowing what is in that data field, so names like "Name" and "Address" are more appropriate.

On the form, though, because we are cycling through the controls, it may be more efficient to name the CONTROLS "txtField1" or "chkField1", so that we can use the code I attached. Sounds like your table should be OK for what we are doing.

2) I "think" what you want to do is only cycle through those companies that have requested changes to them, correct? If this is the case, I would still only recommend two tables. However, I would make the underlying record source a JOIN query in which only records in tblMaster that have a corresponding record in tblReplica will be displayed. It is the simplest query you will ever build, simply add both tables to the Query builder, double click the * from tblMaster and drag and drop the primary Key from tblMaster to the primary key in tblReplica (this JOINs the tables). Save it and then set your record source in the master form as this query. Now, only record with requested changes will show up.

In the replica table, if you wanted to maintain a long term record of the requested changes, add two more fields: A Yes/No field for Audited and a Date/Time field for DateAudited. After you hit the Approve or Submit button, those fields would be updated automatically. If you have no need for maintaining the requests, then simply delete the underlying record. If you maintain the records long term, then the Replica form should either have a filter on it for Audited = False or have a query based on the same criteria.

Hope this helps some more. Willing to assist again if you get stuck....

[robtech4]

So I have been working on this just about all day. The part that I am all of a sudden having trouble with is getting my form to populate with data. I am assuming my recordsource could be wrong but am not sure. It was working fine earlier today then as I continued to add more and more subforms (one to each tab on the tab control...for a better appearance as you suggested in the past) nothing shows! In form view the form is literally blank! :( I have attempted to add a screen shot to this post but I am not sure how readable it will be.

You're help through this project thus far has been awesome!

[robtech4]

I realized why the form was not populating. I still had my form set to compare the tblAud with the tblMaster rather than the tblMaster with the tblReplica. The record I was making the "test" changes on was in the Replica table so in turn was in the tblAud but not in the tblMaster. Once again, as you suggested I am switching the database to two tables in order to avoid this error and the many others that coincide with it.

Much Appreciation,

Rob

[twinnyfo]

Let me know if you run across any other obstacles. Glad I've been able to help so far.....

[robtech4]

I made the join query, or at least I thought I did but I am still showing all 275 records to click through rather then just the ones that have changes.

With the Yes/No Field and the Date/Time field...does that require code for it to autofill?

I hesitate to ask but if it is not too much work for you to throw together, do you think you could add the following into the sample database you provided. The database you provided has brought me a long way and I think that is the best way for me to learn!

"2) I "think" what you want to do is only cycle through those companies that have requested changes to them, correct? If this is the case, I would still only recommend two tables. However, I would make the underlying record source a JOIN query in which only records in tblMaster that have a corresponding record in tblReplica will be displayed. It is the simplest query you will ever build, simply add both tables to the Query builder, double click the * from tblMaster and drag and drop the primary Key from tblMaster to the primary key in tblReplica (this JOINs the tables). Save it and then set your record source in the master form as this query. Now, only record with requested changes will show up.


In the replica table, if you wanted to maintain a long term record of the requested changes, add two more fields: A Yes/No field for Audited and a Date/Time field for DateAudited. After you hit the Approve or Submit button, those fields would be updated automatically. If you have no need for maintaining the requests, then simply delete the underlying record. If you maintain the records long term, then the Replica form should either have a filter on it for Audited = False or have a query based on the same criteria."

[twinnyfo]

Rob,

I've made a few changes. First, I've added a Reviewed Yes/No field to the Replica table. and placed that check box on the Replica form. It is not necessary to have it there, as the code in the Master form will update it, but sometimes Access can't find the field underneath a form until you put the control for it on the form and then remove it (Don't ask---it's just an annoying quirk that Access has).

Second, I've created a new query. It's a super easy INNER JOIN, which will only list records that have corresponding records on both sides. But since this is the Master table we are concerned about, we include only records from the Master table. We also limit the records to those that have not been reviewed.

SELECT tblMaster.*
FROM tblMaster INNER JOIN tblReplica ON tblMaster.ID = tblReplica.ID
WHERE tblReplica.Reviewed=False;
 

Of course, remember to change the Record Source of the Master form to this query! I forgot during my test....

Finally, I tweaked the VBA lying under the Master form, to set the Reviewed field to True, Refresh the Subform (so save the change to the Reviewed control to the Table) and requery the form (so that any reviewed records are removed).

Option Compare Database
Option Explicit
 
Private Sub cmdApprove_Click()
On Error GoTo EH
    Dim I As Integer
    For I = 1 To 10
        If Me.fsubReplica.Form("chkField" & I) Then
            Me("Field" & I) = Me.fsubReplica.Form("Field" & I)
        Else
            Me.fsubReplica.Form("Field" & I) = Me("Field" & I)
        End If
        Me.fsubReplica.Form("chkField" & I) = False
    Next I
    Me.fsubReplica.Form.Reviewed = True
    Me.fsubReplica.Form.Refresh
    Me.Requery
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub
 

I hope we are getting closer. Let me know if you need anything else.

[robtech4]

Just wanted to check and be sure you did not forget to upload that sample database...wasn't sure if you did end up making it based on the first paragraph.

Thanks.

[twinnyfo]

I can certainly upload what I have modified......

Let me know if this helps.

[robtech4]

With the "Reviewed: checkbox on the form that edits the tblReplica, I am noticing that unless it is unchecked the record will not show up in the frmApproval (with approve/deny buttons). In otherwords it will not automatically realize that two records with the same primary key in the tblMaster and tblReplica have other fields that do not match. Is there a way for it to automatically pick up on this and feed the records into the frmApproval to be looked over?