BeforeUpdate issue

[Snowman74]

I'm using Access 2000 and attempting to allow for a automatic search of the database so that I don't duplicate date when entering data from month to month. but I keep getting a Data Type Mismatch. Here's the code:
Private Sub AcctNo_BeforeUpdate(Cancel As Integer)
Dim AcctNo As String
Dim stLinkCriteria As String
Dim rsc As ADODB.Recordset

Set rsc = Me.RecordsetClone

AcctNo = Me.AcctNo.Value
stLinkCriteria = "[AcctNo]=" & "'" & AcctNo & "'"

'Check Pt table table for duplicate PtAcct Number
If DCount("AcctNo", "tblPt", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Patient Number " _
& AcctNo & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.", vbInformation _
, "Duplicate Information"
'Go to record of original Patient
rsc.Find stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing
End Sub

Any help would be great!!

 

[Eupher]

Hiya, Snowman,

Is AcctNo a number or is it text? If it's numeric, I would change your variable declaration to a numeric data type and remove the single quotes from your stLinkCriteria statement. Also, as a general rule I would not name a variable with same name as an object on my form - for readability AND to prevent any compiler ambiguity.

HTH,

Ken S.

 

[PHV]

Unless you are in an ADP instead of a MDB, replace this:
Dim rsc As ADODB.Recordset
with this:
Dim rsc As DAO.Recordset

BTW, saying you get a Data Type Mismatch error without pointing the line of code is not so helpful ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886