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

I notice you are uupdating 21, not 20 fields.  However, one solution:

DIm strEmplOld as string
Dim strEmplNew as String
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim x As Long
Dim strFieldName As String
Dim strSQ string
strSQL =  "'" ' Single quote

strEmplOld = InputBox("Enter Employee Number to copy FROM")
strEmplNew = InputBox("Enter Employee Number to copy TO")

Set rs1 = CurrentDb.OpenRecordset("select * from dbo_Employees where EmpNo = " & 
strSQ & strEmplOld & strSQ)

Set rs2 = CurrentDb.OpenRecordset("select * from dbo_Employees where EmpNo = " & 
strSQ & strEmplNew & strSQ)

rs2.Edit
For x = 0 To 20
   strFieldName = "BW0" & Format$(x, "00")
   rs2(strFieldName) = rs1(strFieldName)
Next
rs2.Update



On 27 Feb 2013 at 17:17, Steve Turner wrote:

> 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
> 
>  
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>