BeforeUpdate event

S

Sher

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
 
R

Rick Brandt

Sher said:
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.
 
S

Sher

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

Rick Brandt said:
Sher said:
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.
 
K

Klatuu

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


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

Rick Brandt said:
Sher said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top