473,836 Members | 1,438 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 4584
robtech4
33 New Member
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!
Aug 1 '12 #21
twinnyfo
3,653 Recognized Expert Moderator Specialist
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....
Aug 1 '12 #22
robtech4
33 New Member
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!
Attached Images
File Type: jpg screenshot1.jpg (29.1 KB, 187 views)
Aug 3 '12 #23
robtech4
33 New Member
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
Aug 3 '12 #24
twinnyfo
3,653 Recognized Expert Moderator Specialist
Let me know if you run across any other obstacles. Glad I've been able to help so far.....
Aug 3 '12 #25
robtech4
33 New Member
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."
Aug 3 '12 #26
twinnyfo
3,653 Recognized Expert Moderator Specialist
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblMaster.*
  2. FROM tblMaster INNER JOIN tblReplica ON tblMaster.ID = tblReplica.ID
  3. WHERE tblReplica.Reviewed=False;
  4.  
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).

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdApprove_Click()
  5. On Error GoTo EH
  6.     Dim I As Integer
  7.     For I = 1 To 10
  8.         If Me.fsubReplica.Form("chkField" & I) Then
  9.             Me("Field" & I) = Me.fsubReplica.Form("Field" & I)
  10.         Else
  11.             Me.fsubReplica.Form("Field" & I) = Me("Field" & I)
  12.         End If
  13.         Me.fsubReplica.Form("chkField" & I) = False
  14.     Next I
  15.     Me.fsubReplica.Form.Reviewed = True
  16.     Me.fsubReplica.Form.Refresh
  17.     Me.Requery
  18.     Exit Sub
  19. EH:
  20.     MsgBox Err.Number & " " & Err.Description
  21.     Exit Sub
  22. End Sub
  23.  
I hope we are getting closer. Let me know if you need anything else.
Aug 3 '12 #27
robtech4
33 New Member
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.
Aug 3 '12 #28
twinnyfo
3,653 Recognized Expert Moderator Specialist
I can certainly upload what I have modified......

Let me know if this helps.
Attached Files
File Type: zip Audit.zip (85.6 KB, 98 views)
Aug 3 '12 #29
robtech4
33 New Member
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?
Aug 3 '12 #30

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

Similar topics

0
1298
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
4331
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
2015
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
4932
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
4806
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
9665
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
10541
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...
0
10249
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7785
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
6976
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
5818
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4447
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4007
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3108
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.