BeforeUpdate event

[Guest]

I have created a before update event listed below. What I would also like it
to do is go to the record that is in the database. I thought that was what I
was doing but it isn't working. Can someone please help.

Private Sub SSN_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[SSN]", "Security", "[SSN]= '" & Me![SSN] & "'")) Then
MsgBox "This SSN already exists."
Cancel = True
Me.Undo
DoCmd.GoToRecord , , acFirst
DoCmd.FindRecord SSN

End If

End Sub

 

[Rick Brandt]

I have created a before update event listed below. What I would also
like it to do is go to the record that is in the database. I thought
that was what I was doing but it isn't working. Can someone please
help.

Private Sub SSN_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[SSN]", "Security", "[SSN]= '" & Me![SSN] &
"'")) Then MsgBox "This SSN already exists."
Cancel = True
Me.Undo
DoCmd.GoToRecord , , acFirst
DoCmd.FindRecord SSN

End If

End Sub

You cannot navigate in the middle of an update event. Your code needs to cancel
the update, issue an Undo on the form, and then it might work to move to another
record. I'm not sure even that would work without testing it though.

 

[Guest]

Can you write out what exactly I would need to do.

I have created a before update event listed below. What I would also
like it to do is go to the record that is in the database. I thought
that was what I was doing but it isn't working. Can someone please
help.

Private Sub SSN_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[SSN]", "Security", "[SSN]= '" & Me![SSN] &
"'")) Then MsgBox "This SSN already exists."
Cancel = True
Me.Undo
DoCmd.GoToRecord , , acFirst
DoCmd.FindRecord SSN

End If

End Sub

You cannot navigate in the middle of an update event. Your code needs to cancel
the update, issue an Undo on the form, and then it might work to move to another
record. I'm not sure even that would work without testing it though.

 

[Guest]

As Rick said, it is possible this will not work. Moving to a different
record duing the Before Update event, even with the Cancel, may not work, but
you can try this:

Private Sub SSN_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[SSN]", "Security", "[SSN]= '" & Me![SSN] &
"'")) Then MsgBox "This SSN already exists."
Cancel = True
Me.Undo
With Me.RecordsetClone
.FindFirst "[SSN] = '" & Me.SSN & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

--
Dave Hargis, Microsoft Access MVP

Can you write out what exactly I would need to do.

I have created a before update event listed below. What I would also
like it to do is go to the record that is in the database. I thought
that was what I was doing but it isn't working. Can someone please
help.

Private Sub SSN_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[SSN]", "Security", "[SSN]= '" & Me![SSN] &
"'")) Then MsgBox "This SSN already exists."
Cancel = True
Me.Undo
DoCmd.GoToRecord , , acFirst
DoCmd.FindRecord SSN

End If

End Sub

You cannot navigate in the middle of an update event. Your code needs to cancel
the update, issue an Undo on the form, and then it might work to move to another
record. I'm not sure even that would work without testing it though.