Solved

No warning displayed when records can't be deleted.

Posted on 2011-02-27
41
746 Views
Last Modified: 2013-11-28
When I use the command:
   DoCmd.RunCommand acCmdDeleteRecord
on a record that can't be deleted because there is a referential integrity relationship to records in another table, I get no error or warning message.

How can I delete records via code and get the warning when applicable?
0
Comment
Question by:Milewskp
  • 17
  • 14
  • 3
  • +1
41 Comments
 
LVL 1

Author Comment

by:Milewskp
ID: 34993059
Here an mdb that demonstrates the problem:
Open Form2.
Select the record with Name=Sue. then hit cmdDEL. This should delete the record.
Now select the record with Name=Bill and hiw cmdDEL. This won't delete the record.

Now use the DEL key on your keyboard to delete Bill. This should display an explanation of why Bill can't be deleted.

db1.mdb
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 34993874
To enhance this, perhaps you can do a DLookup check in your script just before you delete that record, like:


IF "" & DLookup ("ID","yourtable","yourfield='yourvalue' ") = "" then

DoCmd.RunCommand acCmdDeleteRecord


else

     msgbox "cannot delete record"

end if
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34998729
I don't understand why you have a Self Join here.

The "referential integrity" message is triggered in One to Many relationships specifically, not really in Self Joins...

For ex. DoCmd.RunCommand acCmdDeleteRecord triggers an error and the error code just fine when a standard One To many Delete record is requested (Attempt to Delete Customer when Customer has Orders Outstanding)


Jeff
untitled.JPG
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34998949
Hi Jeff,
<I don't understand why you have a Self Join here.>
What self-join?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34999149
You are relating two tables with the exact same field to each other.
(also Table1 does not have a primary Key)

In other words, I can figure out what a design like this represents...

Again in a standard one to many relationship the error is displayed normally.

Again, ...
Can you please take a step back and clearly explain what the goal is here...

JeffCoachman
0
 
LVL 1

Author Comment

by:Milewskp
ID: 34999335
Hi Jeff,
My understanding is that a Self-Join joins a tabel to itself (eg Table1 joined to Table1). I have a one-many join between Table0 and Table1. This is a demo mdb I created just to illustrate the problem. It is not a real application.

In my real applciation, my users have a command button labelled 'DELETE' that they use to delete records. I need code behind this button to delete teh current record. I was using DoCmd.RunCommand acCmdDeleteRecord, but discover that it doesn't work as expected.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34999845
Again, the error is generated just fine for me in a "Standard" One to many relationship.

<This is a demo mdb I created just to illustrate the problem. It is not a real application.
In my real applciation, my users have a command button labelled 'DELETE' that they use to delete records. >
Then what is the difference between the two databases?

Again this works fine for me.
Did you see my screenshot?

(Please test and verify this in the Northwind sample Database.)

Also in your "real" database, please see if all of your "Confirm" options are enabled.  This may effect this as well.
Finally make sure you don't have any code disabling Error messages (remember once you turn off the alerts in code, you must make sure that somewhere in the same code, the alerts get turned back on.)
For ex.: if you turn off alerts and the code error out, the alerts remain off.
This is why you should always set the alerts back on in your error handling, ...just in case

JeffCoachman
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35006503
Hi Jeff,
<the error is generated just fine for me in a "Standard" One to many relationship>
Can you post your database?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35009409
Well, to be sure, let's test this in an independent source Database.
;-)

You can download the MS Access Northwind sample DB.
On the "Customers" form, put a button with your Delete code.
Then try to delete a customer with your button.
(any customer other than FISSA or PARIS, they do not have any orders)

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c6661372-8dbe-422b-8676-c632d66c529c

I just tried deleting Customer ANATR and I got the message about referential integrity no allowing the delete because ANATR still has orders associated with it.

So verify this on your system independently, then we can go from there...


;-)

Jeff


untitled.JPG
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35016531
Hi Jeff,
I've already done this - the database I've attached was created from scratch - nothing was imported, etc. If that database is working for you then it just be one of the access options that is workstation-specific.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35018171
Well again the DB you posted was just:
  "a demo mdb I created just to illustrate the problem. It is not a real application.
So again, the pseudo self-join you have there is still a bit confusing to me.

But let's be clear.
What was the result when you tried to delete a customer in the Northwind sample db?
You never stated the result...
What happens if you insert a "Real" one to many table setup in your sample DB, and try to delete a parent record while Child records still exist?

<If that database is working for you then it just be one of the access options that is workstation-specific.>
...Or again, it might be that you don't have a valid One to many represented in your design...
 
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35035793
Hi Jeff,
When you try this with the mdb I posted above, can you tell me if you get the same result as me:

- Open Form2.

- Select the record with Name=Sue. then hit the DEL button. This deletes the record for me.

- Now select the record with Name=Bill and hit the DEL button. This won't delete the record for me, and DOESN'T display a msgbox with the reason why.

- Now use the DEL key on your keyboard to delete Bill. This won't delete the record for me, but DOES display a msgbox with the reason why.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35039027
Yes, all of this is confirmed
I get the same results as you.

But again, this is not a standard relationship...
In fact I am still struggling to figure out what this represents.
And for some odd reason you seem to be artfully evading explaing this
Can you take a moment to explain? Yes or no...

So I really can't speculate why (or what effect) this design will have on the way records are deleted using the delete key and/or a coded "Del" button.
Also note that you are trying to delete a record on a subform from the main form, which complicates things even further...

Is this your ultimate question:
    "In my current configuration, how can I show the referential integrity message regardless of whether I use the Delete Key on the keyboard, or my own coded "Del" button, ...to delete a record"?

JeffCoachman
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35043455
Hi Jeff,
<Is this your ultimate question: "In my current configuration, how can I show the referential integrity message regardless of whether I use the Delete Key on the keyboard, or my own coded "Del" button, ...to delete a record"?>
No. The question was, and still is:
<When I use the command:
   DoCmd.RunCommand acCmdDeleteRecord
on a record that can't be deleted because there is a referential integrity relationship to records in another table, I get no error or warning message.
How can I delete records via code and get the warning when applicable? >


<But again, this is not a standard relationship...
In fact I am still struggling to figure out what this represents.
And for some odd reason you seem to be artfully evading explaing this
Can you take a moment to explain? Yes or no...>
All this represents is an example to show the problem. Create you own example - you'll get the same result.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35104336
There is a way to do this, but not with using Docmd.RunCommand.
So the answer to your *specific* question:
<How can I delete records via code and get the warning when applicable? >
...seems to be:

    You can't do this with your non-standard relationship.
0
 
LVL 1

Assisted Solution

by:Milewskp
Milewskp earned 0 total points
ID: 35113563
Folks,
After some googling, I found that others have experienced the exact same problem: acCmdDeleteRecord will not delete the record AND will not provide a message why if a record in another table depends on it for referential integrity.

I didn't find a way to make acCmdDeleteRecord work, but I did find two alternatives:
 - acCmdDelete will generate an Error 2501 ('the RunCommand action was cancelled'). This is better than no response at all, but unfortunately it raises the same error 2501 when the user manually cancels the delete (by answer the delete confirm msgbox with a NO), and I'm not aware of how to distinguish the two.

- rs.Delete (the Delete method of the recordset object) does what I want, and is the solution I settled on.

See the attached mdb for a demo:
 - Open Form2.
 - Select the record with Name=Bill, and try each of the three DEL buttons and the DEL key on your keyboard.

db2.mdb
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35114278
Hi Jeff,
I had a look at the NorthWest db. It turns out the reason it works for OK for the Customers form and not my Form2, is that the former is a Form View form, and the latter is datasheet view:
I have both versions of the Customer form in the attached, stripped down version of the NW db. If you open the Customer form and then enter the Company name field, the OnEnter event procedure will try to delete the record (unsuccessfully, due to the related records in the Orders table), and will generate the warning message.

If, OTOH you try this with the CustomerDS form - no warning message.

So, this is just another Access bug. Surprise, surprise.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:Milewskp
ID: 35115996
Thanks everyone for spending your time trying to help me; it is appreciated. I will be selecting my solution as the answer.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 35135178
This is the issue with posting a "Specific" requirement.

You distinctly stated that your question was:

<The question was, and still is:
<When I use the command:
   DoCmd.RunCommand acCmdDeleteRecord
on a record that can't be deleted because there is a referential integrity relationship to records in another table, I get no error or warning message.
How can I delete records via code and get the warning when applicable? >

To which I correctly answered, ...You can't.

If you had stated at that time that you would like an "alternative", then I would have suggested using SQL which would work regardless.
CurrentDb.Execute "Delete * FROM table0 Where Name=" & "'" & [Forms]![Form2]![sf1].[Form]![Name] & "'", dbFailOnError
Me.Requery


In the future, please make it clear if we must answer your question as posted or if you are seeking alternatives.

JeffCoachman

0
 
LVL 1

Author Comment

by:Milewskp
ID: 35136922
Hi Jeff,
<How can I delete records via code and get the warning when applicable?...You can't.>
Actually, I can - as demonstrated by my db2.mdb attached.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35138107
Not really, you are building your own custom message. not relying on anything built in to Access.

Kinda like you asking a *Specific* question of:
"Can you recreate the exact Excel Pivot Table interface in Access?"
The answer would be NO.
Then you simply create your PT in Excel then use interop code to instantiate the Excel file.
While technically a solution, it does not address your specific question.

This is our situation here, ...you posted a very specif question. (as is typical of many of your questions.)
In all the exchanges here, You did not make mention of accepting alternatives.
This is why I explicitly ask for your "Ultimate" question.


If the question was to simply "make it work", I could have posted something similar as well.

Besides my SQL solution works just as well with less code.

So my solution should be accepted as well.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35141946
To all involved,

For the record, you know I rarely make waves about questions...

And my intent here was not to make this an issue either.

My point was that it was made very clear to me that this was a very specific question.

From this specific question, I saw no specific answer.

Specific Question example...
Q. The question was, and still is:
Can the Import wizard in Access be made to Import a textfile that has the column names in Row 3.
A. No

General Question example:
Q. How do I import a text file into Access that has column headings in Row 3.
A. This cannot be done in Access with the built in functionality, you will have to have an interim conversion step to set Row 1 as the column headings.

In all fairness, the distinction in the above example is subtle.

I am basing my thoughts on the askers previous questions here where, again, the requirements are fairly specific and rigid.

In a great many cases here it is not clear if the asker wants a solution to the Q as posted, or they are looking for a possible alternative or workaround.

In this case I was fully prepared to offer the SQL solution, when I decided to clarify the "Ultimate question":
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26851379.html?cid=1573#a35039027
Is this your ultimate question:
    "In my current configuration, how can I show the referential integrity message regardless of whether I use the Delete Key on the keyboard, or my own coded "Del" button, ...to delete a record"?

To which I recieved the reply:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26851379.html?cid=1573#a35043455:
No. The question was, and still is:
<When I use the command:
   DoCmd.RunCommand acCmdDeleteRecord
on a record that can't be deleted because there is a referential integrity relationship to records in another table, I get no error or warning message.
How can I delete records via code and get the warning when applicable? >

Hence my response that this was not possible with the nonstandard joins.

Going back even further, several times I requested info on this Non standard/Pseudo/Quasi-self Join relationship to see in what real world situation this type of relationship would be applicable.
To date I have never received a direct reply.  This was also a factor in my response.
So in a standard relationship, this would not even be an issue.

In the interest of completeness I too will even go so far as submitting a sample of my SQL alternative, demonstrating that this will work as well.
(and Without the overhead of creating a recordset.)

On Error GoTo Err_SomeSub
    CurrentDb.Execute "Delete * FROM table0 Where Name=" & "'" & [Forms]![Form2]![sf1].[Form]![Name] & "'", dbFailOnError
    Me.Requery
Exit_SomeSub:
    Exit Sub

Err_SomeSub:
    If Err.Number = 3200 Then
        MsgBox Error, vbExclamation
        Resume Exit_SomeSub
    Else
        Resume Exit_SomeSub
    End If

You can add in the askers validation code to simulate the same end result.

In conclusion, all I am looking for here is a point split, nothing more.

;-)

JeffCoachman







db1-2-.mdb
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 35142001
I do this in a database and get a message that says it is going to delete related records.

You have to set the relationship between the tables to allow a cascade delete

Click the DATABASE TOOLS menu
Click RELATIONSHIPS
Click EDIT RELATIONSHIPS
Use this to relate the tables
You then have the option to allow cascade updates and deletes

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35142009
Also the delete button needs to be on the form with the Parent record.

mlmcc
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35142297
Yes, but as you know most Access developers prefer to leave that (Cascade Deletes) option disabled.

This is because, ...yes, it will alert you, but it will also delete the *Parent* record(s), possibly created Orphaned records...

Again with the non-standard relationship the asker is using, it is unclear what the consequences of such a delete would be...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35142331
mlmcc,

...But upon further reflection, (and given the uncertainty surrounding the parameters of this question...)
Yes, then your post too should qualify as a valid solution.

;-)

Jeff
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35143378
When I tried his database the delete button gave me a message that indicated the context was wrong since the button is on the main form not the child form which has the data.

The delete keyboard button worked but I got warned of referential issues and couldn't delete the records.

mlmcc
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35151024
Hi folks,
<In a great many cases here it is not clear if the asker wants a solution to the Q as posted, or they are looking for a possible alternative or workaround.>
If it's not clear, just ask. But, to me this is a redundant question, an alternative or work-around is better than no solution at all, and experts don't need permission to offer any advice, alternative solutions or work-arounds.

< In the future, please make it clear if we must answer your question as posted or if you are seeking alternatives.>
For all my questions, I'm seeking the answer, but alternatives are always welcome. (I would have thought that goes without saying, but there, I've said it.)

<you are building your own custom message. not relying on anything built in to Access>
The question never specified that the solution had to rely on a built-in feature of Access.

< Non standard/Pseudo/Quasi-self Join relationship>
I have no idea what this is about. My db1.mdb example uses a one-many relationship with referential integrity. That's it.
As jimpen says: "Table0 and Table1 have the same data columns but are different distinct objects. This is very poor database design."  Absolutely correct, and absolutely irrelevant to the problem. The NW database will demonstrate the same problem if you change the Customers form to Datasheet-view (as demonstrated by my db2.msb example).

0
 
LVL 1

Author Comment

by:Milewskp
ID: 35151079
Hi mlmcc,
<You have to set the relationship between the tables to allow a cascade delete>
Cascade deletion should be used when the design requires it. But, if it is required by the design, then you don’t have this problem; if it is excluded by the design, it's not a viable solution.
You could also argue that eliminating referential integrity is also a 'solution', since this also solves the problem.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35151111
Hi jeff,
<In the interest of completeness I too will even go so far as submitting a sample of my SQL alternative, demonstrating that this will work as well. ..
    CurrentDb.Execute "Delete * FROM table0 Where Name=" & "'" & [Forms]![Form2]![sf1].[Form]![Name] & "'", dbFailOnError
    Me.Requery
>
This is a viable solution, but it doesn't work as well, as least not for my case, because of two disadvantages:
1. It requires you to include the name of the table and joining fields in the SQL statement. My solution is generic – the same code can be used in multiple forms, which I have.
2. The Requery causes the first record of the form to become current. This may be confusing to users, since it differs from standard delete behaviour, and is bothersome if they have to scroll back where they were.
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35151228
Hi Vee Mod,
For what it's worth, I disagree with the chosen solution and assisted solution. Please consider this:

The question was:
<How can I delete records via code and get the warning when applicable?>
My db2.mdb demonstrates that you can delete the records via code and get the warning when applicable.

Therefore, choosing the solution to be <You can't>, is simply incorrect, and does a disservice to others looking for a solution to this problem (which is common).

Choosing this:
<You have to set the relationship between the tables to allow a cascade delete>
to be an assisted solution is also incorrect:
<Cascade deletion should be used when the design requires it. But, if it is required by the design, then you don’t have this problem; if it is excluded by the design, it's not a viable solution.
You could also argue that eliminating referential integrity is also a 'solution', since this also solves the problem.
>




0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35153868
Milewskp,

Please remember that issues about a questions clarity are rare here.

I just find it interesting to note that throughout this whole exchange, (with Top Experts and Mods all looking at this Q and coming to the same conclusion), that you never once entertained the thought that, ...just perhaps, ...your question was unclear...

JeffCoachman
0
 
LVL 1

Author Comment

by:Milewskp
ID: 35156549
Hi Jeff,
It's clear to me. If it's not clear to you, just ask.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35157353
I did, ...and as you can see, (with my 6 years experience here), ...it was still not clear.

Perhaps if you had posted a sample of the actual db without the non-standard, pseudo/quasi-self join relationship...



0
 
LVL 1

Author Comment

by:Milewskp
ID: 35158150
Hi Jeff,
<When I use the command:
   DoCmd.RunCommand acCmdDeleteRecord
on a record that can't be deleted because there is a referential integrity relationship to records in another table, I get no error or warning message.
How can I delete records via code and get the warning when applicable?>
If you can tell what part of this question is unclear, I will do my best to clarify.

<Perhaps if you had posted a sample of the actual db without the non-standard, pseudo/quasi-self join relationship...>
My db2.mdb attachment is an example db without the 'non-standard, pseudo/quasi-self join relationship'.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now