delete warning

[Grimwadec]

I have created a function called DeleteJobRecords to delete subsidiary
records when deleting the main record thus:

Private Sub Form_Delete(Cancel As Integer)

DoCmd.SetWarnings False

If MsgBox("Subsidiary Record Details will also be deleted", _
vbOKCancel) = vbCancel Then
Cancel = True
Else: DeleteJobRecords
End If

DoCmd.SetWarnings True

End Sub

I have coded the DeleteJobRecords function to not display any warning as it
runs each SQL delete query. However I have been unable to stop the built in
access warning re deleting the main record. My attempts included the
DoCmd.SetWarnings statements in the above Sub. Also tried several other
methods including Before and After Delete etc.etc. ad nauseum.

Help please!

 

[Tom van Stiphout]

On Tue, 6 Oct 2009 20:45:01 -0700, Grimwadec

Check out the SetOption method in the Help file.

-Tom.
Microsoft Access MVP

 

[Grimwadec]

Thanks Tom

As far as I can see (and tested ) SetOption will merely remove the warning
if you run a Delete query. I have already suppressed the delete query
warnings using DoCmd.SetWarnings. Its the system warning produced
after>Highlighting the Record Selector> and clicking Delete (you will note
the original Sub is Form_Delete).

Overall what I am trying to do is when a User deletes a record by
Highlighting the Record Selector on the Form and Clicking Delete on the
keyboard, the only message they get is mine saying, "Subsidiary Record
Details will also be deleted", OK or Cancel.

 

[GB]

My uneducated guess? Assuming that the Form_Delete event function is the
correct event for what you are doing.

Set Cancel = true prior to leaving the function irregardless of actually
cancelling or not, and then program all that is necessary to delete the
record(s) in question. My guess is that the Form_Delete event is being run
just prior to actually doing the deletion event and therefore resetting the
warnings to true prior to leaving the function activates them on screen when
the deletion actually occurs.

Otherwise, try to restore system warnings at a point later like on the press
of a "required" button or closing the form, or some other follow-on event to
the form_delete event.

The next thing of course is to look into your deletejobrecords sub routine
to see if it restores the warnings before actually doing the delete. :\

 

[Grimwadec]

Yep, in fact my deletejobrecords sub routine was restoring the warnings
before the delete notwithstanding that I had Docmd.SetWarnings True at the
end of the delete statements. So what I did was create a separate function
(SetWarningsTrue) to run after the subroutine and it works perfectly..Thanks
muchly!

 

[Grimwadec]

AND there's more, vbOKCancel sets the default button as OK. How do I set the
default button to Cancel to avoid a User accidentally hitting Enter and
triggering the deletion? Had a look at vbDefaultButton2 but couldn't see how
to use it?

 

[GB]

When you call vbYesNo add the value of vbDefaultButton2, so:

msgbox("Hi", vbYesNo+vbDefaultButton2)

This will set the default button to the second option.

FYI, I recommend that if you have multiple successive yes/no questions that
you alternate the question and default option. I.e., first question
answering Yes moves forward, second question answering No moves forward. An
alternative is to ensure that the set of buttons show up far enough apart
that a user can not keep the mouse in the same location and successively
click.

It may add a little aggravation to the "knowledgeable" user, but is for
"protection" and integrity of the actions.

 

[GB]

Yeah, sometimes what looks like the right thing on the surface doesn't work
as expected because of other things burried in the weeds. It's an important
practice to only enact actions when they are needed (i.e., setwarnings false
when don't want a result, but true when done with that and in this case, only
set to true when you really need to restore functionality that was lost and
"needed")

 

[GB]

Ohh, sorry, was just reviewing your question and my answer. Instead of
vbDefaultButton2 being added to vbYesNo like I said, it would be
vbDefaultButton2 to vbOkCancel. Same principle applies.

Basically "they" have set it up such that the value used after the message
text can be parsed similar to a binary number considering that only a
particular set of options are available. So by summing a series of numbers
that are or are made up of powers of 2, the result can be broken down and the
applicable action(s) performed on the message box.

 

[Grimwadec]

Ah, so easy eh, and I think the integrity far outweighs the aggravation.

The Users won't know any different as they are being dragged kicking and
scratching from a manual paper based system anyway, in fact I have had to
retain the capacity to issue printable reports of things that can be viewed
on screen because the Principals don't want their staff to complain about the
changes to their routine! And, in that regard the obsolescence of the User
Levels functions in Access 2007 (notwithstanding it was not very User
friendly, which is probably why Microsoft scrapped it?) is a pain as I will
have to produce 3 different front ends with various locked or enabled
settings!

Had my winge - Thanks heaps!