[AccessD] Read some fields from a record in a table and write the data to another recorded in the table

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