loop to avoid inserting duplicate data

[Astro]

Hello there,

I'm creating a loop to read the records in the recordset, and compare it to the items in a listbox where it determines if the user is trying to add a duplicate data.

Here are the codes.

Private Sub List2_DblClick(Cancel As Integer)
Rem Insert Answers Into Question
Dim a As String
 
rst.Open "select * from ss_table", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic
rst2.Open "select * from ss_table", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic
 
If Not rst.EOF Then
 
    rst2.MoveFirst
 
'------loops through the recordset (rst2)------
     Do While Not rst2.EOF
        a = rst2!ansID
        rst2.MoveNext
        Loop
rst2.Close
 
rst1.Open "SELECT sID from autonumber", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic
    
'-----prevent adding duplicate items------
If List2.Column(0, List2.ListIndex) = a Then
        MsgBox "Invalid"
        Else
        
        rst.AddNew
        rst!sID = "ss" & Right("000000" & CStr(CInt(rst1!sID) + 1), 6)
        rst!qnsID = Me.List1.Column(0, List1.ListIndex)
        rst!ansID = Me.List2.Column(0, List2.ListIndex)
        Rem List Box Insert
        Me.List3.AddItem List2.Column(0, List3.ListIndex) & ";" & List2.Column(1, List3.ListIndex)
        
        End If
 
    rst.Update
    rst1.Close
    rst1.Open "update autonumber set sID = sID +1", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic
    rst.Close
End If
End Sub

The problem im experiencing now is that, whenever i add a new item twice, it will prompt 'invalid' on the second attempt,

but if i add a different item and then return to add the original item (which already exists in the recordset)

it will be added despite being a duplicate.

I believe the mistake is within my Do While Not rst2.EOF loop.

Need some help.

Thank you very much

Astro