Stuart McLachlan
stuart at lexacorp.com.pg
Thu Feb 28 13:37:43 CST 2013
Hi Steve,
The code I posted was a minimal solution, Yes, set rs1 and rs2 to Nothing and Close them.
(Access garbage collection should do it automatically when the procedure exits, but it is
good practice to always explicitly destroy objects as soon as you have finished with them.
It's also good to see you have included error handling.
One comment. Your solution is very inefficient. You are updating the record once for each
field, You only need to do the Update after setting all the field values. You should change
For x = 0 To 20
strFieldName = "BW0" & Format$(x, "00")
rs2(strFieldName) = rs1(strFieldName)
rs2.Update
rs2.Edit
Next
to
For x = 0 To 20
strFieldName = "BW0" & Format$(x, "00")
rs2(strFieldName) = rs1(strFieldName)
Next
r2.Update
--
Stuart
On 28 Feb 2013 at 9:29, Steve Turner wrote:
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>