VBA code to update tables through check box?

[robtech4]

Hello,

I have this form in my database.

https://lh4.googleusercontent.com/DZ...J3wWgo7mjU8rpA

I need to figure out how to write the code for a button at the bottom of the checkboxes so that when clicked...

1) the check boxes that are selected will update in the master table, delete out of the audit table, and remain the same in the replica table

2) the check boxes that are not selected will still have the current field data and will not make a change to the tblMaster but will revert tblMasterReplica back to what it was prior to the change



Additional Background Info:

- I have a form to change view the records out of the tblMaster that is locked from editing. This is used by all the other non-Admin users.

- I have another form that can be used to submit a change request to one of the records (looks like the display form except it is not locked).

This information is then moved into tblMasterReplica (does not show in the view form). It is also displayed in the audit table to show who made the change, what time it was made, & what it was changed from and changed too (new record is on top of the other in table).

- The audit form is what is displayed on the "Updated" column in the screenshot and the tblMaster is what is displayed in the "Current" column of the screenshot.

- The screenshot is only one tab from the form, I used the tab control because there are about 65 fields for each record.


I have really been struggling with this code for quite a while and would really appreciate any additional help. I am very new to access so my background with code is extremely limited. I am very thankful for any of the help I can get.

With Appreciation,

Rob

[Seth Schrock]

Your link doesn't work.

I believe that what you need is an update query. You would just have the criteria be that the check box is checked and then you would need a field that would identify which records lined up to which records in the two tables.

Without more detailed information of the table designs, it is hard to be more specific.

[zmbd]

Good Morning Rob:
As noted, your link comes up as 403-forbidden.

Building on what Seth has pointed out...
What I would do is use a "For Each" statament to step thru the controls in the form either checking for type or name with a "Select-Case" to build my SQL string. Alternatively, if you only have two or three controls, you could just check the controls in question... just depends on the form design.

Once the SQL is built, use it as the basis for an "dbs.execute UPDATE...(remainder of SQL)"

Finally, so that you can really get the help you need, and the other experts, mod, and users can follow what you’re asking in the future, the following are really a must read:
How to ask good questions
Posting guidelines

I also highly recomend the following site as it has a great basic tutorial on RDMS design as most textbooks really do a poor job... A Tutorial for Access

Of particular note is tables page within the tutorial - notice the information that is given, the table name, the field name, the field type, key or index, etc... AND the format used to present that data. You don't need a fancy gridded table to do this... just a line by line layout. This same information is needed when talking about forms etc...

Most Respectfully
-Z
What do you mean it's still 95 outside... it's 3am!>.... oh... time for a nap :)

[robtech4]

Try clicking on the link a second time after the error comes up, for some reason that seems to work. Also, I am working hard to get through tutorials so I can better understand the answers and post better answers, zmbd. I apologize if the question was poorly written.

Thanks,

rob

[Seth Schrock]

The link still didn't work for me.

[robtech4]

Seth,

Please tell me what type of detailed information would help you help me. I don't want to type a bunch of useless information and have you waste your time reading it but would really appreciate the advice.

Thanks

[robtech4]

Hopefully this is not too hard to see

[Seth Schrock]

Okay, that helps a lot! I wasn't picturing each field having its own checkbox. So would I be correct in assuming that you would only be looking at one record when you clicked the button to make the changes?

[robtech4]

That would be correct. Everything in the left column comes from the same table and everything in the right column comes from a seperate table (they are updated changes made my non-admin users.

[Seth Schrock]

Okay. For ease of explanation, what is the name of the upper left-hand textbox control and the checkbox to its right? I will be able to give you the code for that one control and you can then expand it to do the rest of the controls.

[twinnyfo]

Rob,

The basics for the approval should work with code below. Modified to fit the controls on your form....

Option Compare Database
Option Explicit
 
Private Sub cmdApprove_Click()
    If Me.chkPlantManager Then
        Me.txtPlantManager = Me.txtReplicaPlantManager
    End If
End Sub
 

Hope this helps.

[robtech4]

So I was able to get the code going to compare old data with new data thanks to your help! My new and what seems to be my last issue (hopefully) is approving the addition of a new supplier.

I have a seperate form in which you can add additions (new supplier) and then once you select "Submit Request" it updates into the replica table.

Then, when I go to the form to approve/deny the changes that others have made...the New Supplier does not populate for approval. I would suppose its because it has no record with an identical primary key in the Master List.

Is there a solution to this or will new suppliers have to be added manually into the Master Table by the Admin?

Thank you!

[twinnyfo]

Rob,

Create a separate form for approving new suppliers. The replica table could also have a Yes/No field flagging whether it is a new supplier. The new form would filter out only the new records.

Hope this helps!

[robtech4]

Greg,

I have a seperate form for the approval process but the master table fields are coming up as #Name? rather then blank.

--When we check the chkbox to approve each field and then select the Approve button...the record is NOT being written into the fields and therefore into the Master Table.

Please advise :)

[twinnyfo]

Rob,

Perhaps.... Use nearly an identical form as for the update approvals. However, for the master form, set the Data Entry Property to True, this way, it will automatically go to a new record. The subform would just filter the New records. Remove the Master/Child relationship between the two forms, so the master is not looking for some sort of relationship.

The same code should work to add the data to the master table. However, be sure that after you complete the update that the master form moves to a new record, otherwise any other updates will be overwritten.

I hope this helps.....