warn before entering duplicate records

[brisp]

Hi i am trying to write something that would alert an inputter to the fact that a record for a id no already exists, i only want to make a warning as sometimes the same id no may have mulitple events

the field name is hospno and the table "personal details" an id no would be along the lines of v653

I found this code to put into the before update event on the field in an access form but it just give me a runtime error. sorry if it seems simple i've been writting DB's for years but trying to put more advacne error checking in and learning code!
Can nyone suggest what I am doing wrong or anything else i could try??

Private Sub hospno_BeforeUpdate() 
Dim rslt As Integer   
   If nz(DLookup("[hospital number]", "personal details", "[hospno]=" & Me.hospno),0) <> 0 Then 
      rslt = Msgbox ("This number has already been entered. Do you wish to continue?", vbYesNo) 
      If rslt = vbNo Then 
        Me.RecNo = Null 
         Me.RecNo.SetFocus 
      End If 
   End If 
End Sub

[patjones]

What line are you getting the runtime error on? I can suggest a couple things to improve this code, but I just want to know first where VBA is complaining. Thanks.

Pat

[NeoPa]

Let's start by sending you off on a quick detour to When Posting (VBA or SQL) Code. If you're getting into more code then I'm sure we can help you. This will help you to get more help more quickly and easily in future.

Now that's out of the way I would say (and bear in mind I'm working with too little info here) that you seem to have missed out part of the declaration for the BeforeUpdate event procedure. Typically they have a Cancel parameter. The way to handle cancelling the update is to set that parameter to True (which needs to be set between lines #5 and #8).

PS. By the looks of things you're very close to the logic you need for what you describe. A good sign at such an early stage.

[neelsfer]

i use something like this with beforeupdate - i replaced my field and table with yours. Hope it helps

 Answer = DLookup("[hospno]", "personal details", "[hospno] = '" & Me.hospno & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Identity Number Found" & vbCrLf & "This ID No will now be deleted", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.Undo
 
 End If

hospno