Avoid duplicate Entry in the textbox

[aman]

Hi Guys

I have a form in which there are diff controls. What I want to do is when the user enters duplicate barcode in the textbox then an error message showing duplicate value should appear. I can't apply primary key on the table field barcode as in the table there is autonumber field present which is unique. I have wriiten the following code on the textbox lost focus event and I have used dlookup function to check for duplicate values but its not working .

Private Sub Text17_LostFocus()
Dim Same As String
If Same = DLookup("Barcodevalue", "[Completed_table]", "Barcodevalue = Text17") Then
MsgBox "Duplicate Entry of Barcode is not allowed"
Text17.SetFocus
Exit Sub
Else
sSQL = "Insert into Completed_table(Date1,Location,Department,Barcodevalue,Signature) values (#" & Format(Me.Text4.Value, "mm-dd-yy") & "#,'" & Me.Combo12.Value & "','" & Me.Combo15.Value & "','" & Me.Text17.Value & "','No')"
   DoCmd.SetWarnings False
   DoCmd.RunSQL sSQL
 
end if
end sub

Thanks in advance for your help.

Aman

 

[Poppa Smurf]

You are setting Same as string, but Same has no value i.e. it is null.

Alternatively you could use a DCount as follows
If Dcount("Barcodevalue", "[Completed_table]", "Barcodevalue = Text17") >0 then
Display your message
Else
Add your record
End if

 

[aman]

Thanks a lot for your help. dcount worked fine.

Cheers

 

[DCrake]

Can't see how either worked if you are using the same syntax as you posted

If Dcount("Barcodevalue", "[Completed_table]", "Barcodevalue = Text17") >0 then

it should be

If Dcount("Barcodevalue", "[Completed_table]", "Barcodevalue = [B]'" & Me.Text17 & "'") [/B]>0 then

 

[aman]

Hi Dcrake

Thanks a lot. The other dcount statement was also working fine although there was some problem as you mentioned earlier.

I can't understand why that statement was giving me the right result.

But thanks for your help.It worked gr8.

Cheers

 

[Magster]

I see you solved your problem, but maybe another way would have been to set a unique alternate key on that field. Then, the database would automatically stopped the duplicate entry.