Paul Hartland
paul.hartland at googlemail.com
Thu Feb 28 03:22:09 CST 2013
im getting too slow at typing, was just sending a very similar solution.
On 27 February 2013 23:47, Stuart McLachlan <stuart at lexacorp.com.pg> wrote:
> 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
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
Paul Hartland
paul.hartland at googlemail.com