Deleting Records (Access 2000)

Topic

New Reply

In the beginning was the word, and the word was HELP.
I have an Access MDB that started acting strange. I have two tables, I will call AA and BB, cute names I know. Any way the relationship box has BB linked to AA, with the Enforce Referential Integrity and Cascade Delete box is checked. So far so good. I have code in the Form’s Form_AfterDelConfirm event. And all is right in the world. Then I split the Database, into TT which houses tables and QFR which houses Queries, Forms, Reports and one table, Switchboard. Now all the deletes of all the tables work, as they did before, except the one when I delete a row in the AA table. It does delete the AA row and any linked rows in the BB table. BUT, a big but, the confirm message never shows and the Form_AfterDelConfirm is never becomes active. Therefore the code in that event never runs. The delete is done by a button that I let Access create. Access coded a
“DoCmd.DoMenuItem acForm,acEditMenu,Bar,6,,acMenuVer70” and a
“DoCmd.DoMenuItem acForm,acEditMenu,Bar,8,,acMenuVer70” commands, I never soiled my hands with any of this coding. Looking at the Relationships, the Enforce Referential Integrity box is grayed out and checked, while the Cascade Delete box is just grayed out. I have gone back and deleted the link between DataBases AA and BB, and Redone the relationship to what it looked like before. But once I link the Tables back between the two DataBases, MicroSoft, in its wisdom, remove the Cascade Delete check mark. And still we have no confirm for this one delete button. All other delete buttons give me a confirm message. No other tables do a cascade delete. The question, How do I get the Confirm back so that I have by code being executed??????????????

Reply | Quote

Replies

Did you establish the relationships in the backend database? You can only enforce referential integrity on tables in their native database, which is why things are grayed out in the front end. The wizards, for some reason known only to Microsoft, still create Access 95 code and I stopped trying to decipher it years ago so I haven’t any idea of exactly what those two obsolete lines are really doing.

Have you tried using the Delete event rather than the Before or AfterDelConfirm events? If you want to have the user confirm the deletion before proceeding, the Delete event is the logical place to do it. You don’t really want to wait until the record is deleted to the buffer to ask the user if that’s really what they had in mind.

Reply | Quote

Thank You for your interest Charlotte,
Yes I went back to the Table Database and made the changes I talked about. The user wants to see the confirm message and I want to be insured that the AA record was deleted before any records in the BB table are deleted. I also need to update some other tables. This is why I used the Form_AfterDelConfirmed event. I have added a “DoCmd.SetWarnings True” command and it looks like my code was executed. BUT, another big but, the msgbox I have in the “Form_AfterDelConfirm” never was displayed. I know that the Delete function is not part of the C or C++ code that is being generated. The delete is a file handler, written in assembler. I have also run into problems with delete confirm messages when I wrote Clarion. Clarion converted to C and therefore used file handlers. I wanted to do this without me having to go and do what should be done for me. Oh well such is life?????
Once more thanks! I guess it is off to the code wars I go.

Reply | Quote

I’m not sure I understand what you are trying to accomplish. Are you trying to give the user a chance to say OOPs, I didn’t mean to do that? Or are you really wanting to say “The record has been DELETED!”? If you use the BeforeDelete event, you can trigger code to ask the user to confirm, but in general you shouldn’t have to. If on the other hand you are using code to actually delete a record from a recordset then you do have to provide the confirmation process.

I’m also puzzled about your statement
The delete is a file handler, written in assembler.
If you are deleting records in a .mdb database, all those functions are provided as a part of the system. Can you fill in the picture for us a bit.

Reply | Quote

Sorry Wendell, I did not mean to confuse. My question is simply is, Why does not the Microsoft generated statements work? The user normally does get a chance to say Oops with these statements. But it does not work now. Why? Is this an Access problem? Does it come from the under lining code, C than makes up Access or is it Access itself? These are the questions. I have had to create my own code, to do the things I wanted done. I still work like to know, if any one does know, Why does this code of MicroSoft’s not work in this case?
As for the delete is a file handler written in assembler. Well C or C++ does not handle the passing of data from C to a device. C can not read a file or write to a file. All such actions are handled by “File Handlers”, and to my knowledge all “File Handlers” are written in Assembler. VB though called a language still is C code and gets translated to C or C++ and the to Assembler and then to machine language if it is to be an executable.
I have a little knowledge so I am dangerous, usually to myself.

Reply | Quote

I still don’t understand why you’re working so hard at this. If you put your “Do you want to do this” message in the OnDelete event of the form, it will allow the user to verify that they want to delete the record … before the deletion happens. This works whether the table under the form is local or linked.

Private Sub Form_Delete(Cancel As Integer)
  If MsgBox("Are you sure?  ", vbYesNo) = vbNo Then
    Cancel = True
  End If
End Sub

I don’t see what the language Access is written in has to do with this at all. Nor do I know why the other events are failing for you, since AfterDelConfirm works for me with linked tables. I’ve never really figured out what use the BeforeDelConfirm is, so I don’t use it. But using the above, you should still get another chance if you also use this:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

  If MsgBox("Are you REALLY sure?  " & Response, vbYesNo) = vbNo Then
    Cancel = True
  End If
End Sub

If you try this and it doesn’t work, there’s probably something else in your code that is intercepting it, or perhaps you’ve set the Response value to suppress the message.

Reply | Quote

Charlotte,

I just went for a search here because of a flukey thing that’s happening to me, and found the original thread for this message very similar to my experience (only, it was Access 2000) . Like Sarge, I’ve lost my Access confirm in only one of my forms (“Do you really want to delete .. ” etc.) I have all the confirm options in Options turned on. I tried to put a warning in the “BeforeConfirm” event, and, (although Sarge had code in the “AfterDeleteConfirm” event), like Sarge, that code never ran. So, I moved a confirm into the “On Delete” event, and I now have my own programmed confirm, but it’s really bothering me —

This database is small, very simple, and consists of three forms only. One other form as the identical call (docmd.runcommand acCmdDeleteRecord) and immediately pops up with the Access built-in confirm msg, while this one form does not. There are a couple of calls in the problem form, and one of the calls is to an “Application.SetOption “Behavior Entering Field”, 1″ . I commented out that code just for a test, and the behavior difference did not change, and I next compacted the database just in case (with the “Behavior” call still commented out) and the behavior didn’t change.

I’m fixed now using the On Delete event, but, do you have any ideas of what could be the problem? I did a quick look at the KB’s on Microsoft, and found something that seemed to relate to web-based record deletions w/out warnings (Access 2002), but nothing similar to my experience.

Just FYI if you have no answer — basically, I’ve gotten around the problem.

thx
Pat

Reply | Quote

Sorry, but I don’t think I can help you. When I played with the Before and After DeleteConfirm events, they worked for me as advertised. One thing to look for, though, if you’re using any of the DoCmd statements is whether you have SetWarnings turned off. If you do, you won’t get the confirm messages at all.

Reply | Quote

Well, there is more than one flukey thing, and I just add these for interest.

Don’t spend any time on this, this is mostly just FYI and for me to vent on my perplexed state . As I said, I’ve given myself a safety confirm in the “on-delete” module (that _is_ working!), even though I feel there’s something not so good going on w/ my code, or something else:

First, just the option settings and the setwarnings issue:
I searched the code in the entire database. I don’t have a line of code anywhere that sets warnings either on or off, and the manual options are all checked.
Yesterday I tried to explicitely set warnings on, and I just tried it again, and it made no difference (docmd.setwarnings True).
The two other forms run as expected, bringing up the default Microsoft confirm message, with the exact same code line. All forms are set to “allow” filters, edits, deletions, additions, “Data Entry” = no, Recordset Type = dynaset, and locks = edited record. I don’t open either form w/ any restrictions. I don’t see any differences (except that the two working forms are related, and one is a subform of the other, and the problem form calls an option setting “Behavior Entering Field.”

Secondly,
a.) not only is the expected default Microsoft warning expected w/ all my settings not working, and
b.) the “before delete” getting bypassed (I have a msgbox there), and I have used this event before successfully, but
c.) the msgbox in an on-error code bit that I put in the Delete button “on click” is not firing — _unless_ I put a breakpoint in there and step through it. And there is an error, I find.

Here is the line that triggers the error, that (once again) only displays if I have entered a breakpoint and step through:
docmd.runcommand acCmdDeleteRecord.
The error that I get when I step through the code: 2046: The command or action “DeleteRecord” isn’t available now.”
Additionally, there explanatory statements in that error:
*”You may be in a read only db” — it’s not read-only,
*”(you may be in) “an unconverted db fm earlier version” — it is not,
*”The type of object the action applies to isn’t currently selected or isn’t in active view” — I placed a “docmd.runcommand acCmdSelectRecord” just prior to the delete, but I didn’t think that was necessary, and this line also triggered the same error.

Once again, no answer is necessary, this is all just curious.
thx
Pat

Reply | Quote

Try the code Charlotte suggests – I think you will find it works just fine, but if not please repost. In my experience, the Access functions associated with deleting files works as advertised, so I think something in what you’ve done is causing your issue. I don’t know what your background is, but people who are new to Access often do more work than they have to because they don’t understand all of the capabilities of it. One specific question – is your form a bound to a table or query? If it is, then there is a record selection bar on the left side that a user can click on and then press the delete key and it will prompt if you really want to delete the record displayed. Is that still working?

The underpinnings of Access (if you are using a .mdb file as your back-end) is the Jet engine (v4.0 in your case). So the delete of a record never actually goes through any C or assembler. The Jet engine has a number of callable routines and can be used from Access, VB, C or other languages. A unique aspect of Access/Jet is that all tables and other objects are stored in a single Windows/DOS file. Most earlier database products used a file for each table, and different files for other object types.

Finally, most of us never get to the point where we stop being dangerous – it just varies day to day in degree.

Reply | Quote

Charlotte and Wendell,
First thanks! I had to code around this problem, and it WORKS. But I still would like to know why when I created a Delete button by dragging the button tool to my form. I used the “Wizard” and this did work deleting records from the TWo Tables. But then it stopped. It is driving me crazy (that is a short drive these days). As I said now and again I have seen this happen in other PC programs. I was embarrassed to be at the client site and have this come out and bite me. Especially after I believed it was tested and working. So I am now obsess with what happened to cause this not to work!!
I think the next step is to move the databases to another directory and delete the offending button and use the wizard to create a new one and see what transpires from there.
Once again THANKS!!!!!!!
P.S. Not sure why the spell checker does not like obsess???

Reply | Quote

The spell checker doesn’t object to obsess when I use it. Maybe you’re just not holding your fingers right.

Reply | Quote