Datasheet Validation - Add/ Edit records 1

[rsch]

I have built a datasheet subform that holds user login information. I added validation to ensure that a user cannot be entered twice (code follows).

My problem is that now I can't seem to edit the records because of my validation code. Any thoughts on how I can do this?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varCheck As Variant

varCheck = DLookup("[ClientID]", "tblUserInfo", "[ClientID]= " & Me!ClientID & "")

If IsNull(varCheck) Then
Exit Sub
Else
MsgBox ("The client is already in the system.")
DoCmd.CancelEvent
Me.ClientID.SetFocus
End If

End Sub

 

[RoyVidar]

Hi!

One thing to do, would be to use a check for newrecord:

[tt]If Not IsNull(varCheck) Then
if me.newrecord then
MsgBox ("The client is already in the system.")
cancel=true
Me.ClientID.SetFocus
end if
End If[/tt]

But then, this would have the possibility of bombing if the user is allowed to also alter the primary key value.

Roy-Vidar

 

[rsch]

Exactly what I needed. Thanks much.