Avoid duplicate entries

  • Thread starter Thread starter Chris Nebinger
  • Start date Start date
C

Chris Nebinger

Dim strSQL as String
StrSQL = "Select * from [Drawing History Tbl] Where "
strSQL = StrSQL & " [Drawing Number] = '" & Forms![Drawing
Management Frm]![Drawing Number].Value & "' "
strSQL = strSQL & " AND [Drawing Issue] = '" &
Me.Combo2.Value & "'"


'Note, if [Drawing Number] is a number, remove the '
'Same for [Drawing Issue]

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strSQL)
'If .EOF, then that record doesn't exist
if rst.EOF then
rst.AddNew
rst("Drawing Number") = Forms![Drawing Management Frm]!
[Drawing Number].Value
rst("Drawing Issue") = Me.Combo2.Value
rst("Associated Project") = Me.Combo4.Value
rst.Update
rst.Close
-----Original Message-----
Hello

I've created a "pop-up" form that load where a user
Click to expand...
specifies a drawing number and issue and then presses the
run button which runs the following code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Drawing History Tbl")
rst.AddNew
rst("Drawing Number") = Forms![Drawing
Click to expand...
Management Frm]![Drawing Number].Value
rst("Drawing Issue") = Me.Combo2.Value
rst("Associated Project") = Me.Combo4.Value
rst.Update
rst.Close

I would like to include this code in an if statement
Click to expand...
that would first verify that there isn't already a
recordset with those values to avoid duplicate entries.
Could someone guide me a bit.
 
BAH, hit return to soon:

Code should be:

Dim strSQL as String
StrSQL = "Select * from [Drawing History Tbl] Where "
strSQL = StrSQL & " [Drawing Number] = '" & Forms![Drawing
Management Frm]![Drawing Number].Value & "' "
strSQL = strSQL & " AND [Drawing Issue] = '" &
Me.Combo2.Value & "'"


'Note, if [Drawing Number] is a number, remove the '
'Same for [Drawing Issue]

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strSQL)
'If .EOF, then that record doesn't exist
if rst.EOF then
rst.AddNew
rst("Drawing Number") = Forms![Drawing Management Frm]!
[Drawing Number].Value
rst("Drawing Issue") = Me.Combo2.Value
rst("Associated Project") = Me.Combo4.Value
rst.Update
Else
'REcord Exists
MsgBox "This action has been stopped bacause
it would create a duplicate entry of drawing " & Forms!
[Drawing Management Frm]![Drawing Number].Value & " " &
Me.Combo2.Value & "." & vbCrLf & _
"Please validate the drawing issue that you
are trying to create, verify the existing entries in the
database and try again if neccessary.",
vbExclamation, "Duplicate entry warning"
End If

rst.Close


The method you proposed would be very slow on a large
table, especially since it is looping through every record
and checking the value. For future reference, consider
using .Seek or .FindFirst methods, as well as only opening
Forward-Only recordsets.

Chris Nebinger
-----Original Message-----
Dim strSQL as String
StrSQL = "Select * from [Drawing History Tbl] Where "
strSQL = StrSQL & " [Drawing Number] = '" & Forms! [Drawing
Management Frm]![Drawing Number].Value & "' "
strSQL = strSQL & " AND [Drawing Issue] = '" &
Me.Combo2.Value & "'"


'Note, if [Drawing Number] is a number, remove the '
'Same for [Drawing Issue]

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset(strSQL)
'If .EOF, then that record doesn't exist
if rst.EOF then
rst.AddNew
rst("Drawing Number") = Forms![Drawing Management Frm]!
[Drawing Number].Value
rst("Drawing Issue") = Me.Combo2.Value
rst("Associated Project") = Me.Combo4.Value
rst.Update
rst.Close
-----Original Message-----
Hello

I've created a "pop-up" form that load where a user
Click to expand...
specifies a drawing number and issue and then presses the
run button which runs the following code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Drawing History Tbl")
rst.AddNew
rst("Drawing Number") = Forms![Drawing
Click to expand...
Management Frm]![Drawing Number].Value
rst("Drawing Issue") = Me.Combo2.Value
rst("Associated Project") = Me.Combo4.Value
rst.Update
rst.Close

I would like to include this code in an if statement
Click to expand...
that would first verify that there isn't already a
recordset with those values to avoid duplicate entries.
Could someone guide me a bit.
Thanks

Daniel
.
Click to expand...
.
Click to expand...
 
Back
Top