Steve Turner
sturner at mseco.com
Wed Feb 27 17:17:11 CST 2013
Need some help. I'm not a programmer but get around a little in VBA. I have a linked table in my database Access 2010 linked to a sql Express file called dbo_Employee. There are 50 fields or more in the file. 20 of the fields are charge rates. I want to read one employee EmpNo just for the 20 fields and write the data to another employee in the table. I got this code online and tried to modify it but it won't work. I'd like to do it with code that I can attach to a button. I think I need some input boxes for the EmpNo I want to read and one for the new employee I want to write to but I haven't gotten that far. Here's the code I have so far. EmpNo is a text field also. The BW000 to BW020 are the fields I want to read and write. Oh and I have been following some of the talk here and you guys are great to help out guys like me so keep up the good work. Private Sub btnCmdNewEmp_Click() On Error GoTo Err_btnCmdNewEmp_Click Screen.PreviousControl.SetFocus 'DoCmd.RunCommand acCmdFind Dim rs As dao.Recordset Dim ValueList() Dim OldEmpId As String Dim NewEmpId As String ' Set rs = [dbo_Employee] CurrentDb.OpenRecordset ("Select * from [dbo_Employee] WHERE [Empno] = " & [OldEmpId]) ValueList(0) = rs("BW000") ValueList(1) = rs("BW001") ValueList(2) = rs("BW002") ValueList(3) = rs("BW003") ValueList(4) = rs("BW004") ValueList(5) = rs("BW005") ValueList(6) = rs("BW006") ValueList(7) = rs("BW007") ValueList(8) = rs("BW008") ValueList(9) = rs("BW009") ValueList(10) = rs("BW0010") ValueList(11) = rs("BW0011") ValueList(12) = rs("BW0012") ValueList(13) = rs("BW0013") ValueList(14) = rs("BW0014") ValueList(15) = rs("BW0015") ValueList(16) = rs("BW0016") ValueList(17) = rs("BW0017") ValueList(18) = rs("BW0018") ValueList(19) = rs("BW0019") ValueList(20) = rs("BW0020") rs.Close CurrentDb.OpenRecordset ("Select * from [dbo_Employee] WHERE [Empno] = " & NewEmpId) rs.Edit rs("BW000") = ValueList(0) rs("BW001") = ValueList(1) rs("BW002") = ValueList(2) rs("BW003") = ValueList(3) rs("BW004") = ValueList(4) rs("BW005") = ValueList(5) rs("BW006") = ValueList(6) rs("BW007") = ValueList(7) rs("BW008") = ValueList(8) rs("BW009") = ValueList(9) rs("BW0010") = ValueList(10) rs("BW0011") = ValueList(11) rs("BW0012") = ValueList(12) rs("BW0013") = ValueList(13) rs("BW0014") = ValueList(14) rs("BW0015") = ValueList(15) rs("BW0016") = ValueList(16) rs("BW0017") = ValueList(17) rs("BW0018") = ValueList(18) rs("BW0019") = ValueList(19) rs("BW0020") = ValueList(20) rs.Update rs.Close Set rs = Nothing Exit_btnCmdNewEmp_Click: Exit Sub Err_btnCmdNewEmp_Click: MsgBox Err.Description Resume Exit_btnCmdNewEmp_Click End Sub Steve A.Turner Controller Mid-South Engineering Co. Inc. P.O. Box 1399 1658 Malvern Ave. Hot Springs, AR 71902 Phone: 501-321-2276 Fax 501-321-4750 Cell 501-282-7751 Email sturner at mseco.com