Update Recordset Using Multiple Recordsets

[Gutierrez]

What I need is a snippet of code (using MS Acess) that will allow me to update one recordset based on another ... here is what i have so far

IDNumbMDRs = Replace(IDNumbs, "US", "00")
rsSQL.Open "select PersonnelNbr, PrimarySectorCode, SecondarySectorCode,PrimaryFocusCode from Employee " & vbCrLf & _
"where PersonnelNbr IN(" & IDNumbMDRs & ") ", con

IDNumbsAsString = Replace(IDNumbs, " ", "")
IDNumbsAsString = "'" & Replace(IDNumbsAsString, ",", "','") & "'"
Set rsEmp = DataDB.OpenRecordset("select EmpID, IndustryID,PayrollKey, IndSegmentID from Employees where PayrollKey in (" & _
IDNumbsAsString & ")")

Set rsFocus = CurrentDb.OpenRecordset("select Long1, Long2, Int1 from ParameterValues where PName = 'USFo' order by Long1, Long2")

ok so the first recordset will bringback personnel number and primaryfocuscode
the second will allow me to match the personnelnumber and the payrollkey the record i want to update is in the second recordset so now if the personnel number and the payrollkey match ... then i would like to look at the PrimaryfocusCode and match that to the third recordset and if they match then update rsSQL!MajorTypeID with rsFocus!Long2.

Any help would be appreciated.

 

[JerryKlmns]

Have you tried an INNER JOIN of the tables on the matching field?
i.e.

UPDATE Employee As E INNER JOIN Employees As Es ON
E.PersonnelNbr = Es.EmpID
SET Es.IndustryID=E.PrimarySectorCode,
Es.IndSegmentID =E.SecondarySectorCode;

I guess it 's an 1-1 relationship.

 

[Gutierrez]

tell me if you think this would work??


IDNumbMDRs = Replace(IDNumbs, "US", "00")
rsSQL.Open "select PersonnelNbr, PrimarySectorCode, SecondarySectorCode,PrimaryFocusCode from Employee " & vbCrLf & _
"where PersonnelNbr IN(" & IDNumbMDRs & ") ", con

'Updated to capture MajorTypeID 08/06/07 FRG

IDNumbsAsString = Replace(IDNumbs, " ", "")
IDNumbsAsString = "'" & Replace(IDNumbsAsString, ",", "','") & "'"
Set rsEmp = DataDB.OpenRecordset("select EmpID, IndustryID,PayrollKey, IndSegmentID, MajorTypeID from Employees where PayrollKey in (" & _
IDNumbsAsString & ")")

'for each SectorCode, look up the matching GSS code
Set rs = CurrentDb.OpenRecordset("select Long1, Long2, Int1 from ParameterValues where PName = 'SAPM' order by Long1, Long2")
MatchCount = 0

'for each PrimaryFocusCode, look up the matching MajorTypeID
Set rs = CurrentDb.OpenRecordset("select Long1, Long2, Int1 from ParameterValues where PName = 'USFo' order by Long1, Long2")

FileNo = FreeFile

Open GetThisAppPath() & "SAPIND.txt" For Output As #FileNo

'Updated 08/06/07 FRG
Do Until rsEmp.EOF
rsEmp.FindFirst "PayrollKey='US" & Right(rsSQL!PersonnelNbr, 6) & "'"
If Not rsEmp.NoMatch Then
Select Case rsSQL!primaryfocuscode
Case 1 = 50297562
If Not IsNull(rsEmp!MajorTypeID) Then
rsEmp.Edit
rsEmp!MajorIdType = 1
rsEmp.Update
End If
Case 2 = 50297563
If Not IsNull(rsEmp!MajorTypeID) Then
rsEmp.Edit
rsEmp!MajorIdType = 3
rsEmp.Update
End If
Case 3 = 50297563
If Not IsNull(rsEmp!MajorTypeID) Then
rsEmp.Edit
rsEmp!MajorIdType = 2
rsEmp.Update
End If
End Select
End If
Loop