Variable is crossing different events-CodePudding

Apologies for the vague title, but here is my issue. I have a form that has several select lists and associated text boxes. Basically the way it works is if you select a name from the first list, an AfterUpdate event is triggered to query the DB to see if the Eng_ID and Person_ID already exist in the table. If so, then delete that row then insert the updated row. If there is not any records, then just insert the data. The problem is that when I click a name in the first list, then move to the second list, what's happening is that the the Person_ID of the first list is used for the DLookup query, then it delets the record, then inserts the record of the new person I selected in a different listbox. The code is below: Thanks in advance

    ' Add/Remove Participant 1
Private Sub lstPar1_AfterUpdate()
    Dim n As Integer
    Dim strCriteria As String
    Dim strSQL As String
        
     With Me.lstPar1
        For n = .ListCount - 1 To 0 Step -1
            strCriteria = "Eng_ID = " & Nz(Me.Eng_ID, 0) & " And Person_ID = " & .ItemData(n)
                If .Selected(n) = False Then
                ' If a person has been deselected, then delete row from table
                If Not IsNull(DLookup("Eng_ID", "tblEngParRole", strCriteria)) Then
                    strSQL = "DELETE * FROM tblEngParRole WHERE " & strCriteria
                    CurrentDb.Execute strSQL, dbFailOnError
                    
                End If
                Else
                ' If a person has been selected, then insert row into the table
                If IsNull(DLookup("Eng_ID", "tblEngParRole", strCriteria)) Then
                   strSQL = "INSERT INTO tblEngParRole (Eng_ID, Person_ID, ParticipantNumber, Role)" & "VALUES(" & Me.Eng_ID & "," & .ItemData(n) & "," & 1 & ",'" & Me.txtParRole1.Value & "' )"
                    CurrentDb.Execute strSQL, dbFailOnError
                End If
            End If
        Next n
    End With
    
End Sub

' Add/Remove Participant 2

Private Sub lstPar2_AfterUpdate()
    Dim n As Integer
    Dim strCriteria As String
    Dim strSQL As String
    
    With Me.lstPar2
     For n = .ListCount - 1 To 0 Step -1
            strCriteria = "Eng_ID = " & Nz(Me.Eng_ID, 0) & " And Person_ID = " & .ItemData(n)
                If .Selected(n) = False Then
                ' If a person has been deselected, then delete row from table
                If Not IsNull(DLookup("Eng_ID", "tblEngParRole", strCriteria)) Then
                    strSQL = "DELETE * FROM tblEngParRole WHERE " & strCriteria
                    CurrentDb.Execute strSQL, dbFailOnError
                End If
                Else
                ' If a person has been selected, then insert row into the table
                If IsNull(DLookup("Eng_ID", "tblEngParRole", strCriteria)) Then
                    strSQL = "INSERT INTO tblEngParRole (Eng_ID, Person_ID, ParticipantNumber, Role) " & "VALUES(" & Me.Eng_ID & "," & .ItemData(n) & "," & 2 & ",'" & Me.txtParRole2.Value & "' )"
                    CurrentDb.Execute strSQL, dbFailOnError
                End If
            End If
        Next n
    End With
End Sub

Using this image, if I select Daniel and enter his role, then the eng_ID, Person_ID, ParticipantNumber and Role are entered into the database as 130, 118, 1, Collaborator. If I select Kristin, it deletes Daniel becuause it's still using Person_ID of 118 instead of hers which is 134, and since there is a corresponding record, it delets Daniel then adds Kristin.

CodePudding user response：

I don't have Access to test this with, but it seems like you need to separate Participant1 records from Participant2 records when you perform your DLookups.

Also you can generalize your code by pulling the common parts into a separate sub.

Private Sub lstPar1_AfterUpdate()
    CheckParticipant Me.lstPar1, 1, Me.txtParRole1.Value
End Sub

Private Sub lstPar2_AfterUpdate()
    CheckParticipant Me.lstPar2, 2, Me.txtParRole2.Value
End Sub

Sub CheckParticipant(objList As Object, participantNum As Long, role As String)
    Dim n As Integer
    Dim strCriteria As String
    Dim strSQL As String
        
    With objList
        For n = .ListCount - 1 To 0 Step -1
            strCriteria = "Eng_ID = " & Nz(Me.Eng_ID, 0) & " And Person_ID = " & .ItemData(n) & _
                          " And ParticipantNumber=" & participantNum
            strSQL = ""
            If Not .Selected(n) Then
                ' If a person has been deselected, then delete row from table
                If Not IsNull(DLookup("Eng_ID", "tblEngParRole", strCriteria)) Then
                    strSQL = "DELETE * FROM tblEngParRole WHERE " & strCriteria
                End If
            Else
                ' If a person has been selected, then insert row into the table
                If IsNull(DLookup("Eng_ID", "tblEngParRole", strCriteria)) Then
                   strSQL = "INSERT INTO tblEngParRole (Eng_ID, Person_ID, ParticipantNumber, Role)" & _
                             " VALUES(" & Me.Eng_ID & "," & .ItemData(n) & "," & 1 & _
                                      ",'" & role & "' )"
                End If
            End If
            If Len(strSQL) > 0 Then CurrentDb.Execute strSQL, dbFailOnError
        Next n
    End With
End Sub