loop to avoid inserting duplicate data

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.

VB Code:

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

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:

Code:

---

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.