Recordsets

Question

How do used two tables to update data into master from staging table using .edit and .addnew function in same vba function.

This what I have so far, but it's not working when record found in staging table is not in master (rs2).

Function AddUpdRec()
On Error GoTo AddUpdRec_ERR:

Dim RS1, RS2 As Recordset
Dim DB As Database


Set DB = CurrentDb()
Set RS1 = DB.OpenRecordset("STG_COMP_DASHB_MASTER", dbOpenDynaset)
Set RS2 = DB.OpenRecordset("TBL_COMP_DASHB_MASTER", dbOpenDynaset)

'Set RS1 = DB.OpenRecordset("STG_COMP_DASHB_MASTER")
'Set RS2 = DB.OpenRecordset("TBL_COMP_DASHB_MASTER")

RS1.MoveFirst
RS2.MoveFirst

Do Until RS1.EOF

    If RS2!ISSUE_ID <> RS1!ISSUE_ID Then
  'If RS1.Index <> PrimaryKey Then
 
        RS2.AddNew
        RS2!DATAASOFDATE = RS1!DATAASOFDATE
        RS2!ISSUE_ID = RS1!ISSUE_ID
        RS2!ISSUE_NAME = RS1!ISSUE_NAME
        RS2!SEVERITY = RS1!SEVERITY
        RS2!ISSUE_OWNER_MANAGED_SEGMENT = RS1!ISSUE_OWNER_MANAGED_SEGMENT
        RS2!ASSIGNED_MANAGED_SEGMENT = RS1!ASSIGNED_MANAGED_SEGMENT
        RS2!GRC_RISK_LEVEL_0 = RS1!GRC_RISK_LEVEL_0
        RS2!GRC_RISK_LEVEL_1 = RS1!GRC_RISK_LEVEL_1
        RS2!GRC_RISK_LEVEL_2 = RS1!GRC_RISK_LEVEL_2
        RS2!ISSUE_DESCRIPTION = RS1!ISSUE_DESCRIPTION
        RS2!PRIMARY_ROOT_CAUSE = RS1!PRIMARY_ROOT_CAUSE
        RS2!ASSIGN_SEG = RS1!ASSIGN_SEG
        RS2.Update
       
    Else
   
        RS2.Edit
        RS2!SEVERITY = RS1!SEVERITY
        RS2.Update
         
 
    End If
   
        Debug.Print RS1!ISSUE_ID
        Debug.Print RS2!ISSUE_ID

        RS1.MoveNext
        RS2.MoveNext

Loop
     
MsgBox ("TBL_COMP_DASHB_MASTER has been updated.")

RS1.Close
RS2.Close
DB.Close
   
AddUpdRec_EXIT:

    Exit Function
   
AddUpdRec_ERR:

    MsgBox Err & ": " & Err.Description
    'Resume AddUpdRec_EXIT
    Resume Next
   
End Function

Answer 1

If I understand correctly what you are attempting, you want to update the value in the Severity column in TBL_COMP_DASHB_MASTER with the value in the Severity column in STG_COMP_DASHB_MASTER where there is a match on IssueID.  Otherwise you want to insert rows from STG_COMP_DASHB_MASTER into TBL_COMP_DASHB_MASTER where there is no match on IssueID.

If so, why not simply execute an 'update' query (UPDATE statement) in which the tables are joined on IssueID, then execute an 'append' query (INSERT INTO statement) of those rows from STG_COMP_DASHB_MASTER where no matching rows on IssueID exist in TBL_COMP_DASHB_MASTER?

---

Ken Sheridan, Stafford, England

Answer 2

Try this version:

Function AddUpdRec()
    Dim RS1 As DAO.Recordset
    Dim RS2 As DAO.Recordset
    Dim DB As DAO.Database

    On Error GoTo AddUpdRec_ERR:

    Set DB = CurrentDb
    Set RS1 = DB.OpenRecordset("STG_COMP_DASHB_MASTER", dbOpenDynaset)
    Set RS2 = DB.OpenRecordset("TBL_COMP_DASHB_MASTER", dbOpenDynaset)

    Do While Not RS1.EOF
        RS2.FindFirst "ISSUE_ID=" & RS1!ISSUE_ID
        If RS2.NoMatch Then
            RS2.AddNew
            RS2!DATAASOFDATE = RS1!DATAASOFDATE
            RS2!ISSUE_ID = RS1!ISSUE_ID
            RS2!ISSUE_NAME = RS1!ISSUE_NAME
            RS2!SEVERITY = RS1!SEVERITY
            RS2!ISSUE_OWNER_MANAGED_SEGMENT = RS1!ISSUE_OWNER_MANAGED_SEGMENT
            RS2!ASSIGNED_MANAGED_SEGMENT = RS1!ASSIGNED_MANAGED_SEGMENT
            RS2!GRC_RISK_LEVEL_0 = RS1!GRC_RISK_LEVEL_0
            RS2!GRC_RISK_LEVEL_1 = RS1!GRC_RISK_LEVEL_1
            RS2!GRC_RISK_LEVEL_2 = RS1!GRC_RISK_LEVEL_2
            RS2!ISSUE_DESCRIPTION = RS1!ISSUE_DESCRIPTION
            RS2!PRIMARY_ROOT_CAUSE = RS1!PRIMARY_ROOT_CAUSE
            RS2!ASSIGN_SEG = RS1!ASSIGN_SEG
            RS2.Update
        Else
            RS2.Edit
            RS2!SEVERITY = RS1!SEVERITY
            RS2.Update
        End If
        RS1.MoveNext
    Loop

    MsgBox "TBL_COMP_DASHB_MASTER has been updated."

    RS1.Close
    RS2.Close
    Exit Function

AddUpdRec_ERR:
    MsgBox Err & ": " & Err.Description
End Function

---

Regards, Hans Vogelaar (https://www.eileenslounge.com)