473,883 Members | 1,528 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

33 New Member
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.
Jul 24 '12
29 4593
twinnyfo
3,653 Recognized Expert Moderator Specialist
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:

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Private Sub cmdSubmit_Click()
  5. On Error GoTo EH
  6.     Dim strSubject As String
  7.     Dim strAddressees As String
  8.     Dim strEMailBody As String
  9.     strSubject = "Changes Requested"
  10.     strAddressees = "your.name@company.com"
  11.     strEMailBody = "Test Message"
  12.     DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddressees, , , strSubject, strEMailBody, True
  13.     Exit Sub
  14. EH:
  15.     MsgBox "There was an error sending this note!  Please contact your Database Administrator.", vbCritical, "Error!"
  16.     Exit Sub
  17. End Sub
  18.  
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
Jul 27 '12 #11
robtech4
33 New Member
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
Jul 27 '12 #12
twinnyfo
3,653 Recognized Expert Moderator Specialist
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...
Jul 27 '12 #13
robtech4
33 New Member
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
Jul 27 '12 #14
twinnyfo
3,653 Recognized Expert Moderator Specialist
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:
txtReplicaField 1
lblReplicaField 1
chkReplicaField 1 (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....

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Private Sub cmdSubmit_Click()
  5. On Error GoTo EH
  6.     Dim I As Integer
  7.     For I = 1 To 63 'You have 63 controls to go through
  8.         If Me("chkReplicaField" & I) Then
  9.             Me("txtField" & I) = Me("txtReplicaField" & I)
  10.         End If
  11.     Next I
  12.     Exit Sub
  13. EH:
  14.     MsgBox Err.Number & " " & Err.Description
  15.     Exit Sub
  16. End Sub
  17.  
Hope this helps!
Jul 27 '12 #15
robtech4
33 New Member
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.
Jul 27 '12 #16
robtech4
33 New Member
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
Jul 30 '12 #17
twinnyfo
3,653 Recognized Expert Moderator Specialist
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.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Private Sub cmdSubmit_Click()
  5. On Error GoTo EH
  6.     Dim I As Integer
  7.     For I = 1 To 63 'You have 63 controls to go through
  8.             If Me("chkField" & I) Then
  9.             Me.Parent.Form("txtField" & I) = Me("txtField" & I)
  10.         End If
  11.     Next I
  12.     Exit Sub
  13. EH:
  14.     MsgBox Err.Number & " " & Err.Description
  15.     Exit Sub
  16. End Sub
  17.  
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.
Jul 30 '12 #18
dsatino
393 Contributor
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.
Jul 30 '12 #19
twinnyfo
3,653 Recognized Expert Moderator Specialist
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.
Attached Files
File Type: zip Audit.zip (47.7 KB, 118 views)
Jul 31 '12 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

0
1299
by: Chuck | last post by:
Good Morning: Has anyone in this newsgroup had experience with emailing changes made on a xml datagrid using ASPEmail? I currently have the table loading correctly with the correct data. When I press the submit button however I only get the table no data in the received email. Any suggestions would be appreciated. Chuck
6
2339
by: mike11d11 | last post by:
I'm trying to create an application that will have multiple users working off a table on a SQL server. Since multi users will be updating different records at any given moment, how can i get those changes and merge them into my current Dataset. I've been playing around with the GetChanges method and Acceptchanges but they just dont seem to be pulling over the changes and updating my Dataset. Maybe if someone could give me a sample of...
1
3510
by: mankolele | last post by:
Hi all Is there someway to track changes made to data in a mysql database? Like someone changes a phone number in a record on a form -Is there a way to find out what changes were made to what fields for what records and when they were changed and what the record looked like before the change? And also record the logged in user that made the change? I know that there is supposed to be a seperate table for that. Thanks
6
4333
by: lucyh3h | last post by:
Hi, In one of my pages, I use javascript (AJAX) to populate one mulitple select field based on user's click event on another control. I noticed that when I navigate back to this page by clicking browser's "Back" button, the changes made to the multiple select is not preserved. (It shows the initial value). While all the other user selections are preserved. Firefox seems fine. How do I workaround this issue for IE?
14
2016
by: smugcool | last post by:
HI all, I wanted to monitor changes made in any excel file of each and every cell by various users in networking system. kindly provide some guidelines how to create the software. Regards Anup kumar
3
4945
by: =?Utf-8?B?QnJhbmRvbg==?= | last post by:
Hi, I have an aspx page that has the "include" code in it which includes another page that displays information. I also have an upload page that allows users to upload a simple html document onto the server. The include code calls that html page to display the text on that page. The problem is when the user uploads that html page, the aspx page does not refresh to reflect those changes, even if the user hits refresh in their browse....
1
2389
by: =?Utf-8?B?QnJhbmRvbg==?= | last post by:
Hi, I have an aspx page that contains an include section which calls an html page for text to be displayed on the aspx page. I also have an upload page that allows users to upload a simple html page with the same name everytime and places it on the server. My problem is that the aspx page does not update to reflect the changes made to the uploaded html page. However, if I log into the server and open the aspx page that contains the code...
3
1242
by: tabakaka | last post by:
how do i tell the user if the changes made in an open window or form on vb.net are updated or not after accidentally closing the window?
5
5187
by: vovan | last post by:
I have set of controls (Textboxes, checkboxes etc) along with the Grid on Windows Form. I use BindingSource to populate both Grid and the set of Controls. User selects the record in the grid and all controls are populated with data from the selected row. The grid is going to be read only. Textboxes, checkboxes are going to be read/write. What event and how do I need to use to catch any change in any of the textboxes? I tried to use...
8
4808
by: jmarcrum | last post by:
Hello all, i have a continuous form that displays about 1000 records and opens when I click a button. When the form opens, the user has the option of checking a checkbox that is located beside each record. The checkbox is labeled "Move" (for moving multiple records at a time to another year). Of course, the checkbox is set as a control "moveRecord" on a table that the query is pulling from. So if the user checks one record, then clicks to...
0
9932
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9777
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10728
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10833
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7959
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5980
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4200
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3227
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.