Problems creating unique records in a table

[Howie]

Hello

I am trying to create a table which includes unique style criteria
based on 10 different criteria held in array. The criteria are built
into a DFirst string and if not found in the StyleTable should add a
new record to it. If found it updates the source file with the Style
number (intstyle). The following code extract should work but it seems
to add duplicate records with identical criteria to the style table.
Do I need to update the table in any way after a record has been
added ?Any thoughts ?

intStyle = 150
Do While Not rstImp.EOF
For Y = 1 To NumCols
StrCriteria = ""
For Z = 1 To 10
HVars(Z, 2) = GetColVar(rstImp, HVars(Z, 1), Y)
Next Z

For Z = 1 To 10
StrCriteria = StrCriteria & HVars(Z, 1) & "=" & QUOTE & HVars
(Z, 2) & QUOTE
If Z < 10 Then StrCriteria = StrCriteria & " AND "
Next Z
FirstRow = Nz(DFirst("FirstRow", "StyleTable", StrCriteria),
"Not Found")
If FirstRow = "Not Found" Then
StrCriteria = ""
For Z = 1 To 10
StrCriteria = StrCriteria & HVars(Z, 1) & ","
Next Z
StrCriteria = StrCriteria & "FirstRow) VALUES ("
For Z = 1 To 10
StrCriteria = StrCriteria & QUOTE & HVars(Z, 2) & QUOTE &
","
Next Z
SQLstring = "Insert INTO StyleTable (" & StrCriteria & QUOTE
& "f" & Format(intStyle) & QUOTE & ");"
DoCmd.RunSQL SQLstring
rstImp.Edit
rstImp!StyleNum = rstImp!StyleNum & "f" & Format(intStyle) &
"|"
rstImp.Update
intStyle = intStyle + 1
Else
rstImp.Edit
rstImp!StyleNum = rstImp!StyleNum & FirstRow & "|"
rstImp.Update
End If
Next Y
rstImp.MoveNext
Loop

 

[Tom van Stiphout]

On Wed, 23 Dec 2009 16:39:27 -0800 (PST), Howie

I didn't study your code, but if you don't want duplicates, put a
unique index over those 10 columns.
Then simply always try to insert. Add an error handler. If it works,
the item did not yet exist; if it generates a runtime error, you can
capture it and you know that it already existed.

Curious why in your code you INSERT using one technique (RunSql), and
UPDATE using another (Recordsets).

-Tom.
Microsoft Access MVP