Duplicate Checks on a Text Field

[chrisc]

Hello,

I'm using the following code on an add new record button on a form in
my database:

Dim rs As Recordset
Set rs = Me.RecordsetClone
Serial_No.SetFocus
rs.FindFirst "[Serial_No] = " & [Serial_No].Text
If Not rs.NoMatch Then
MsgBox "Serial: " & [Serial_No] & " Is Already In Use, This Item
Does Not Need To Be Re-Entered!", vbOKOnly + vbCritical
Else
DoCmd.GoToRecord , , acNewRec
End If
End If
End Sub

on line 4 I get a runtime error 3464 which is a datatype mismatch.

The reason it has to be text is as our serial numbers are not just
numbers, otherwise I'd use a datatype of number and change my code to
..value, which I have tested and does work.

Does anyone have a solution to this problem? Would be much
appreciated.

Chris.

 

[Ron Weiner]

Chris

Change the Code to:

rs.FindFirst "[Serial_No] = '" & [Serial_No].Value & "'"

Loose the Serial_No.SetFocus line and you should be good to go.

The problem was you were not comparing Serial_No to a string. Notice the
Single quotes I have concatenated before and after Serial_No.Value. If
there is a chance that a serial number could contain an embedded single
quote change the code to:

rs.FindFirst "[Serial_No] = '" & Replace([Serial_No].Value,"'","''") & "'"

This will double up the embedded double quotes thereby escaping them.

Ron W

 

[ClarCE]

Thanks Ron, much appreciated all working well now

Now for my next issue! I'll make a new post...