Duplicates in Database

[Froto]

Need a little help with some code. I have a textbox
called SampID which users enter their sample ID. The
sample ID is linked to the main table in a field called
sampleid. What I would like to have happen is when a user
enters a sample id, in the textbox afterupdate event it
would check the database to see if a duplicate exists
with that sample id and would thus alert the user with a
message.

Thanks for your help

 

[yakule]

Code
-------------------

'in after update sub of textbox
Dim rs as ADODB.Recordset
Dim sSql as string

'create a recordset of the main table where sample id is equal to
the id entered by the user and count how many records match.
Set rs = New ADODB.Recordset

'if sampleid is a number field use this SQL
sSQL = "SELECT Count(sampleid) AS CountOfsampleid FROM <Main Table Name> WHERE sampleid=" & Me.SampID.value

'if sampleid is a text field use this SQL
sSQL = "SELECT Count(sampleid) AS CountOfsampleid FROM <Main Table Name> WHERE sampleid='" & Me.SampID.value & "'"

With rs
.Open sSql, CurrentProject.Connection
If .Fields("CountOfsampleid").value > 1 Then
'if the count field is greater than 1, show the message box
MsgBox "More than one record with this ID"
End If
'close the recordset
.Close
End With

'clean up
Set rs = Nothing