ADO + Check For Duplicate Records 1

[sk1hotpepr]

I want to have the ability to check for a duplicate entry - if duplicate, go to the existing record, else allow new entry. It seems that every entry I type in is "duplicate" even when it is not. How can I fix this - or is there a better way? any help/guidance is much appreciated. Thanks!
--------------
Code:
Private Sub SDist_BeforeUpdate(Cancel As Integer)
'if new record, check to see if School District is listed. If yes, go to record, Else allow new entry.

Dim rst As Object
Dim myStr As String
Dim stLinkCriteria As String

Set rst = Me.Recordset.Clone

myStr = Replace(Me![SDist], "'", "''")
rst.MoveFirst
rst.Find "[SDist] = '" & myStr & "'"

If Not (rst.EOF) Then 'found the record looking for
MsgBox "record exists"
Me.Undo
Me.Bookmark = rst.Bookmark
End If

rst.Close
Set rst = Nothing

End Sub
----------------

 

[ianataqsl]

Believe your problem is due to the Recordset.Clone being updated.
Try getting the data directly from the server, thus

Dim rst As ADODB.recordset
Dim myStr As String
Dim stLinkCriteria As String

Set rst = New ADODB.recordset


myStr = Replace(Me![SDist], "'", "''")
rst.Open ("Select [TableName].SDist, [TableNAme].[UniqueID] From [TableName] WHERE SDist='" & myStr & "'"), CurrentProject.Connection, adOpenDynamic, adLockReadOnly


If Not (rst.EOF) Then 'found the record looking for
MsgBox "record exists"
Me.Undo
Me.recordset.Find "[UniqueID]=" & rst![UniqueID]
End If

rst.Close
Set rst = Nothing

 

[sk1hotpepr]

Ianatagsl,
Sorry it took so long to reply - thank you for helping me figure this out, you solved my problem (don't worry I'm sure I'll have many more)! Now I can fix a couple other forms I've been wanting to do this to. Have a star, I hope that your reply helps others too...


Enjoy your holidays...

Sherry