Steve Turner
sturner at mseco.com
Thu Feb 28 09:29:39 CST 2013
Thanks Stuart,
With some slight modifications to the code It works great. Had some
errors pop up but help got me thru.
Here's what I came up with. My other question would be should I set the
rs1 and 2 to Nothing as the other code I had did? Its commented out
here. Also should I close the rs1 after the close on rs2?
Help is much appreciated.
Private Sub btnCmdNewEmp_Click()
On Error GoTo Err_btnCmdNewEmp_Click
Screen.PreviousControl.SetFocus
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 strSQL As 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_Employee where
empno = " & strSQL & strEmplOld & strSQL)
Set rs2 = CurrentDb.OpenRecordset("select * from dbo_Employee where
empno = " & strSQL & strEmplNew & strSQL)
rs2.Edit
For x = 0 To 20
strFieldName = "BW0" & Format$(x, "00")
rs2(strFieldName) = rs1(strFieldName)
rs2.Update
rs2.Edit
Next
rs2.Close
'Set rs = Nothing
Exit_btnCmdNewEmp_Click:
Exit Sub
Err_btnCmdNewEmp_Click:
MsgBox Err.Description
Resume Exit_btnCmdNewEmp_Click
End Sub
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Wednesday, February 27, 2013 5:48 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Read some fields from a record in a table and
writethe 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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com