Application with subform updating data in table

[Jerry8989]

I'm working on a access application and would like the best way to achieve what I'm trying to do.

I have 3 tables that have different types of data so I want to have 3 radio buttons on my main form and an ID search textbox and search button. The subform will be populated with the form based on the radio button with the data that matches the ID entered by the user.

One of my tables has columns:
FirstName
MiddleName
LastName
Age
City
State

I'm going to show this data in the form as disabled so the user can see but not change it directly. Next to each field will be a checkbox and a disable textbox. If the table data is incorrect the user can click the checkbox and the textbox next to it will be enabled so they can put the correct information.

My question is how can I update the data field in the table with the value in the other textbox? If I update the table will the value in the field textbox change?

Thank you

 

[Uncle Gizmo]

I don't understand why you mention three tables? It doesn't seem to be relevant to the actual question you posted.

So for clarity let's reiterate your question.

You have some customer details that you want to verify.

You don't want to allow the user to edit the existing data. You want to provide an extra field for them to provide the correction they have identified.

My guess is you will then have someone else authorise this change, otherwise it's pointless! You might as well let the user update the entry directly.

If authorisation is necessary, then you will need the "correction" text box linked to an extra field in the table to store the correction.

You would then need another field, a boolean field which the supervisor would check if they agree with the suggested update. Once the supervisor has checked the data, then you run an update query which would transfer the information from the correction text field into the actual field. This would be non-reversible so you might want to make a backup of the table before you do this...

That's the simplest way to do it, the more correct way would be to have an extra table storing the correction, then have a subform window ( subform / subreport control) displaying a subform linked to the correction table and just make changes in that subform.

 

[Mark_]

Alternate way of tracking changes (as that seems what you are intent upon) would be to save the value of each field when you enter the form. If the user changes one, let them. If the "saved" copy of the field does not match the real value when they leave, write it to an audit file that says WHEN WHO changed WHAT from VALUE to NEWVALUE.

If this happens seldom, this type of system makes perfect sense. If it happens often you get change tables far larger than your data tables.

Uncles approach is best if you need to approve changes FIRST. Audit logs are best when only authorized individuals make changes and you want to track who did what.

 

[Jerry8989]

Thank you both for your replies.

I meant to show the correlation between the 3 tables and the 3 radio buttons.

This is a administrator level application so there will only be 2 or 3 people using it. The user clicking the check box and checking it is an indicator for me to update that field with the contents of the textbox that the user has put the correction.

The reason I can't edit the data within the field textbox is that they want to see what the original value is before the update and once they update the value and click save that field in the database will be updated.

I was going to loop through the checkboxes to see which ones are checked and then take the values of the textboxes and update them in the original table. Is this possible or will there be some type of lock on that table or row?

Thank you for your help

 

[Pat Hartman]

I still don't understand what you are trying to do but it sounds way more complicated than it needs to be.

Forms are bound to tables or queries. A form cannot be bound to three tables so why you mention three tables this way is confusing. Obviously a query can join multiple tables but that doesn't seem to be what you are trying to tell us. If you are saying that you have three identical tables and the combo is being used to choose which table the subform will be bound to, that makes sense. It is poor design but the concept makes sense.

I think what you are trying to do is so outside the norm of standard design that we are having trouble envisioning it.

Because of the way a form works people always see what they are updating before they modify it so the following just doesn't make sense.

The reason I can't edit the data within the field textbox is that they want to see what the original value is before the update and once they update the value and click save that field in the database will be updated.

Perhaps if you could post the database (without any sensitive data) and gave us instructions on how to get the the form we are talking about, we might be able to understand the question.