Steve Turner
sturner at mseco.com
Thu Feb 28 16:26:15 CST 2013
Thanks again Stuart, I changed the code to your suggestion but had to
put the Next and rs2. Update on different lines for the syntax to work
as shown below. Kept giving me errors.
I would never have figured out the for next loop. I see that stepping
though the code that I guess before it gets to the update line that if
fills the record set with all the variables and then updates it.
That's something I didn't know and also how the Format$ will change the
strFieldName each time it runs thru it. May be minimal code for you but
huge for me cause it works.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Thursday, February 28, 2013 1:38 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Read some fields from a record in a table
andwritethe data to another recorded in the table
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
rs2.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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com