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

[Ecniv]

You don't need two variables to hold the same recordset, waste of resources

Also your top loop will only return a as the last value in the recordset...

Use a blnFound variable to say whether the value entered is already in the list.

something like:

Private Sub List2_DblClick(Cancel As Integer)
    Dim cmd As ADODB.Command
    
    Dim a As String
    
    Dim blnFound As Boolean, aryData() As Long
    Dim lngLoop As Long, lngMaxRecs As Long

    On Error Resume Next

    rst.Open "select * from ss_table", CurrentProject.Connection, adOpenStatic, adLockOptimistic, adCmdText
    If Err.Number = 0 Then
        If Not rst.EOF Then

            rst.MoveLast
            rst.MoveFirst
            lngMaxRecs = rst.RecordCount
            ReDim aryData(rst.RecordCount)

'------ loops through the recordset (rst2) ------
            For lngLoop = 0 To lngMaxRecs
               aryData(lngLoop) = rst("ansID") 'assumes that the ansid is a long/number
               rst.MoveNext
            Next
        End If
        rst.Close
    End If

    If Err.Number = 0 Then
        rst.Open "select * from ss_table", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic, adCmdText
        rst1.Open "SELECT sID from autonumber", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic
    End If
    
'-----prevent adding duplicate items------
    If Err.Number = 0 Then
        
'---- HERE ----
        blnFound = False
        For lngLoop = 0 To lngMaxRecs
            If List2.Column(0, List2.ListIndex) = aryData(lngLoop) Then blnFound = True
        Next
        
        If Not blnFound Then
            rst.AddNew
            rst("sID") = "ss" & Format(CInt(rst1("sID")) + 1, "000000")
            rst("qnsID") = Me.List1.Column(0, List1.ListIndex)
            rst("ansID") = Me.List2.Column(0, List2.ListIndex)
            rst.Update

'---- List Box Insert
            List3.AddItem List2.Column(0, List3.ListIndex) & ";" & List2.Column(1, List3.ListIndex)
        
        End If

        rst1.Close
    
'---- run the update query!
'----   which btw you need a table reference??!
        cmd.CommandText = "update autonumber set sID = sID + 1"
        cmd.ActiveConnection = CurrentProject.Connection
        cmd.Execute
        
        rst.Close
    End If

    If Err.Number <> 0 Then
        MsgBox "Error : " & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbExclamation, "Error"
        Err.Clear
    End If
End Sub

The above is NOT going to work but is closer to what it should be.
I do not know how you are looping through a listbox to check but that bit needs to be added in to the section marked '---- HERE ----

I used rst as static because I am unsure whether recordcount works with it when it is dynamic, which is why I reopened it dynamic for the updating part later on.

HAve a read through and see whether you can use bits of this, the whole lot and tweak it to your needs.

BTW read up on naming conventions.