Problem with referential Integrity

[Moff]

Hello. I use Access 2007 with a project (ADP) and SQL Server 2005 Express
Edition. I have a form to add and modify data. I have an unique key that is
video_name. In that form, there is a subform. To be able to complete the
subform, the unique key “video_name†must exist. That is why I use the
following code for the field video_name.

Private Sub ctl_video_name_AfterUpdate()
On Error GoTo Err_ctl_video_name_AfterUpdate
Application.Echo False
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext
Application.Echo True
Exit_ctl_video_name_AfterUpdate:
Exit Sub
Err_ctl_video_name_AfterUpdate:
MsgBox ("The name of the video is already in the database ...")
End Sub

My problem is if I enter a video name that already exists, the message is
written at the screen and everything frooze and I have to restart Access.
Otherwise, everything is fine. Any ideas ?
Thanks!

 

[OldPro]

Hello. I use Access 2007 with a project (ADP) and SQL Server 2005 Express
Edition. I have a form to add and modify data. I have an unique key that is
video_name. In that form, there is a subform. To be able to complete the
subform, the unique key "video_name" must exist. That is why I use the
following code for the field video_name.

Private Sub ctl_video_name_AfterUpdate()
On Error GoTo Err_ctl_video_name_AfterUpdate
Application.Echo False
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext
Application.Echo True
Exit_ctl_video_name_AfterUpdate:
Exit Sub
Err_ctl_video_name_AfterUpdate:
MsgBox ("The name of the video is already in the database ...")
End Sub

My problem is if I enter a video name that already exists, the message is
written at the screen and everything frooze and I have to restart Access.
Otherwise, everything is fine. Any ideas ?
Thanks!

Turning off the Echo and using GoToRecord is desparate. What is this
code trying to do? There must be a better way.
However, if you add an

Application.Echo True

after the

Err_ctl_video_name_AfterUpdate:

Then that should solve your problem.

 

[Roger Carlson]

Follow the Message Box with

Resume Exit_ctl_video_name_AfterUpdate

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L