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

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



More information about the AccessD mailing list