MS Access VBA update table on click of a button from a form

[arizwan]

Hi everyone,

I have 2 tables Sheet1 and Sheet2. They both have identical data and contain Employee Info like name, address, ID, Division,etc. HR wants to update the info in Sheet2 using a form "Sheet2 Form" where they find the employee using ID and then update their info.

Now I have created a button that updates the Sheet1 employee info with the current record that we are viewing in Sheet2 Form. So I created a button in the form with the name "Update Record in Sheet1". On click event I wrote the following vba code

Private Sub Command198_Click()
Dim strSQL As String
strSQL = "UPDATE Sheet1 SET Sheet1.DIVISION = " & Sheet2_subform.DIVISION & " WHERE Sheet1.Empl_ID = " & Empl_ID & ""
DoCmd.RunSQL strSQL
End Sub 

However this is not really working any ideas?
Thanks

[Rabbit]

Why do you have 2 identical tables?

[arizwan]

I need it to be there because the process is that the changes need to be approved by the manager before they move into sheet1 but it needs to be there in sheet2 for them to be able to see what the changes are. Any ideas why my VBA code isn't running properly?

[Rabbit]

Hard to say what's wrong, there's not enough detail.

I don't know if you're getting any error codes. I don't know what the table definition is. I don't know if your SQL runs outside of the VBA.

Without any of this info, all I can do is take a shot in the dark. It could be that a field is mispelled or a table name is mispelled. Or maybe you have conflicting data types. Or maybe a form reference is incorrect.

[arizwan]

So exactly whats happening is that when i click the button it gives a pop-up box asking for Sheet1.DIVISION and then another for Sheet1.Empl_ID. Even after I give those the it is still not updating anything in sheet1 i get a message stating "you are about to update 0 rows".

2nd problem is that I do not want it to give pop-up boxes I want sheet1.division to be picked up as Sheet2_subform.DIVISION and sheet1.Empl_ID as the Empl_ID on the form

[Rabbit]

When it gives you those popups, that means it couldn't find the table or the field you're referencing.

[rwalle]

If I got it right you have a employee table where you have all the employee related data in fields like Name LastName Address,StartDate,Dept. and you need to update one or more fields like position or salary rate ,as if someone will be promoted lets say from front desk to sales? is this what you want to do ?

[twinnyfo]

Are you certain that Sheet1 has fields called "DIVISION" and "Empl_ID"? Are these both numerical values? If they are text, then try this:

strSQL = "UPDATE Sheet1 SET Sheet1.DIVISION = '" & Sheet2_subform.DIVISION & "' WHERE Sheet1.Empl_ID = '" & Empl_ID & "';"

Grasping at straws here.....