Solved

Customizing Ms Access Error Message about Referential Integrity violation

Posted on 2004-04-08
7
418 Views
Last Modified: 2008-03-06
Hi,

My goal is the following:

Have a form with a listbox in it. I select and enrtry from it, then press a command button, the record is erased. Deletion is done programatically via a Sub, using an SQL DELETE FROM clause.

Issue:

When the underlying record to erase exists in other tables, a referential integrity violation message comes up. It appears below. How do I intercept the error generated and substitute it for  something custom made that the user can understand.

"Ms Access can't delete 1 record(s) in the delete query du to key violations and 0 record(s) du to lock violations"
0
Comment
Question by:mabelanger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
7 Comments
 
LVL 2

Expert Comment

by:michaelbartolotta
ID: 10789029
mabelanger,
Just perform a SQL Select on the OTHER table(s), if there are any records with matching keys, post a message and skip the Delete.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10790171
Use behind the button code like this:

On Error GoTo err_btnDelete
CurrentDb.Execute ("delete * from YourTable where tableID=" & Me.tableID), dbFailOnError
err_btnDelete:
If Err = 3200 Then
   MsgBox "Key: " & Me.tableID & " is still related to other records"
Else
   MsgBox "Severe error: " & Err.Number & " " & Err.Description
End If

Nic;o)
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 90 total points
ID: 10790180
Hmm, forgot the jump after the succesfull deletion :-(
Use:

On Error GoTo err_btnDelete
CurrentDb.Execute ("delete * from YourTable where tableID=" & Me.tableID), dbFailOnError
goto exit_btnDelete

err_btnDelete:
If Err = 3200 Then
   MsgBox "Key: " & Me.tableID & " is still related to other records"
Else
   MsgBox "Severe error: " & Err.Number & " " & Err.Description
End If

exit_btnDelete:
end sub

Nic;o)
0
 

Author Comment

by:mabelanger
ID: 10793913
Hmm... I see where you're getting.

The following doesn't return anything useful, which is strange:

MsgBox "Severe error: " & Err.Number & " " & Err.Description

With some troubleshooting:

Err.Number returns a zero for a successful deletion as well as for a referential integrity violation.  
In both cases Err.Description returns a blank.  What's the catch? Do I need to put something in the Global section?

0
 
LVL 54

Expert Comment

by:nico5038
ID: 10793964
Strange, I tested this on a defined referential integrety relation violation and got error 3200.

Sure you used the last version I posted ?

Nic;o)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question