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!