Avoid creating duplicates of records that are already exist in DB (1 Viewer)

Suli88

New member
Local time
Today, 11:14
Joined
Apr 5, 2022
Messages
2
I have inherited an excel spreadsheet that users use to record customer quotes that gets inserted to an access DB table. Multiple quotes generate separate records, one each (max 4 per form)

If a user adds extra quotes to a spreadsheet with quotes that have been processed previously & runs the routine it creates duplicates of the records in the DB

I'm looking for a method to only add the quotes that have been newly entered and ignore the ones that are in the database already.


Below is an extract of the relevant code:

'''The piece that determines the number of quotes on the form'''

If .Range("E16").Value <> "" Then
No_Quotes = 5
ElseIf .Range("E15").Value <> "" Then
No_Quotes = 4
ElseIf .Range("E14").Value <> "" Then
No_Quotes = 3
ElseIf .Range("E13").Value <> "" Then
No_Quotes = 2
Else
No_Quotes = 1
End If

'''Array load'''

ReDim Arr(1 To 23, 1 To No_Quotes + 1) As Variant
Arr(1, 1) = "Quote Number"
Arr(2, 1) = "Line Number"
Arr(3, 1) = "Quantity Requested"
Arr(4, 1) = "Customer"
Arr(5, 1) = "Material"
Arr(6, 1) = "Quantity"
Arr(7, 1) = "Steps"
Arr(8, 1) = "Labour"
Arr(9, 1) = "Waste"

Set MyRange = .Range("C11")

For j = 1 To No_Quotes
Arr(1, j + 1) = MyRange.Offset(j, 0).Value
Arr(2, j + 1) = MyRange.Offset(j, 1).Value
Arr(3, j + 1) = MyRange.Offset(j, 2).Value
Arr(4, j + 1) = MyRange.Offset(j, 3).Value
Arr(5, j + 1) = MyRange.Offset(j, 5).Value
Arr(6, j + 1) = MyRange.Offset(j, 6).Value
Arr(7, j + 1) = MyRange.Offset(j, 7).Value
Arr(8, j + 1) = MyRange.Offset(j, 10).Value
Arr(9, j + 1) = MyRange.Offset(j, 11).Value
Next j

'''DB interaction'''

Set cn = New ADODB.Connection
cn.Open sTarget

Set rs = New ADODB.Recordset
rs.Open "QuotesTBL", cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rs
For j = 1 To No_Quotes
.AddNew
For i = 1 To 9
.Fields(Arr(i, 1)) = Arr(i, j + 1)
Next i
.Update
Next j
End With

Many thanks for any advice!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:14
Joined
May 7, 2009
Messages
19,169
Code:
..
..
    rs.Open "QuotesTBL", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    With rs
        For j = 1 To No_Quotes
            .Find "[Quote Number] = '" & arr(1, 2) & "'"
            If .EOF Then
                .AddNew
                For i = 1 To 9
                    .Fields(arr(i, 1)) = arr(i, j + 1)
                Next i
                .Update
            End If
        Next j
    End With
 

Suli88

New member
Local time
Today, 11:14
Joined
Apr 5, 2022
Messages
2
Thanks! I made progess, however it only registers & works with the first entry from the spreadsheet and skips the others. The unique info of the quotes are the "Quote Number"s and DB holds up to 5 records for each so the "Line Number" field could be 1 to 5 with the same "Quote Number". The additional quotes entered on the spreadsheet get the next free "Line Number" in the sequence.

I tried to filter for that field but my method doesn't increase the value of the "Line Number" as I guess would be desired for a successful filtering:

Code:
..
..
    rs.Open "QuotesTBL", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    With rs
        For j = 1 To No_Quotes
            .Find "[Quote Number] = '" & arr(1, 2) & "'"
Code:
            .Find "[Line Number] = '" & Arr(2, 2) & "'"
            If .EOF Then
                .AddNew
                For i = 1 To 9
                    .Fields(arr(i, 1)) = arr(i, j + 1)
                Next i
                .Update
            End If
        Next j
    End With

Any ideas?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:14
Joined
Feb 19, 2002
Messages
42,970
You need to add a unique index to the table to prevent the duplicates from being added.

Also, why are the users entering data into a spreadsheet instead of directly into the database?
 

Users who are viewing this thread

Top Bottom