Coding to prevent duplicates

[rkaria]

Hi all

Does any know the coding to to prevent users entering the same data twice in a field on a form?

If they enter the same data I want a warning message saying you can not enter this data as data already exists.

Thanks
R

 

[ajetrumpet]

private sub mycontrol_lostfocus

if dcount( _

"field", "table", "[field] = '" & me.mycontrol & "'" _

) > 0 then _

msgbox "The field " & _

me.mycontrol.name & _

" already contains this data.  " & _

"Please reenter."

 

[rkaria]

Thank you for that,

I have entered the data as I think is right but nothing happens, I can add the data like normal. can you confirm whether I have entered the data correctly below. The Text102 is the name of the field where users input data. warranty is the table name and claimno is the name of the field which the text102 refers to.

Private Sub mycontrol_lostfocus()
If DCount("text102", "warranty", "[claimno] = '" & Me.mycontrol & "'") > 0 Then _
MsgBox "The field " & Me.mycontrol.Name & " already contains this data."
End If
End Sub

Thanks in advanced

 

[ajetrumpet]

is your control name called "MY CONTROL"? is not, put your own name in it. it was not expected to be literal, letter by letter.

 

[rkaria]

Thank you for your help, I know its not suppose to be letter by letter, but Im not to great with access, thats why I have posted this thread.

what do you mean by the control?

THanks
again

 

[rkaria]

Thank you for your time.

I used the below and it works a treat

Private Sub text102_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.Text102.Value
stLinkCriteria = "[claimno]=" & "'" & SID & "'"
If DCount("claimno", "warranty", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Warranty" & SID & " has already been entered." & vbCr & vbCr & "", vbInformation, "Duplicate Information"
MsgBox "Warning Number has already been entered.Duplicate Information"
End If
end sub

R

 

[ajetrumpet]

Thank you for your help, I know its not suppose to be letter by letter, but Im not to great with access, thats why I have posted this thread.

what do you mean by the control?

THanks
again

upload your database karia, and i will help you out. this concept is way too basic for me to write anything about it. My knowledge is just far more superior to it, and I don't want to explain. Note that my statement means nothing to you or me. It just says that I most likely have much more experience with this particular program.

 

[ajetrumpet]

Thank you for your time.

I used the below and it works a treat

good. happy for you