Custom OnDelete in Adp Project

[David via AccessMonster.com]

Hi,

I have a following problem in my adp/SQL Server project. Having two joined
tables Invoice-Customer on a form frmInvoiceOverview in a Datasheet mode I
need to delete just records from the table Invoice. In order to do this
deletion I use the OnDelete hanler, so my routine looks like:

Private Sub Form_Delete(Cancel As Integer)

DoCmd.RunSQL "DELETE FROM Invoice WHERE InvoiceID = " & Me!InvoiceID
Cancel = True

End Sub

So far so good - the selected records get realy deleted BUT the form does not
refresh, so it still shows the deleted records. Me.Requery does not help much
as the form goes always to the first record in the table which is then
confusing for the user. In addition to that deleting multiple selected rows
slows down the process.

Is there any easy solution how to call a custom delete and keep the usual
behaviour of the Access datasheet Form?

 

[Sylvain Lafontaine]

You can store the primary key of the current record and return to it after
making the requery.

 

[Tom Ellison]

If the table is being viewed in datasheet mode, or as a continuous form,
this is not sufficient. In order to maintain integrity of the display, you
would need to be able to specify both which row of the table or query is the
first one at the top of the list, and also specify which of the several rows
on display is the one with the focus. Such control is not, to the best of
my knowledge, available using typical Access datasheets or forms.

I've even tired moving to the former top row, then to the former current
row, setting each as the current row in turn. One difficulty with trying to
do this is that you can't get the information as to which row IS the top one
displayed. The user can scroll the vertical scroll control, and you
wouldn't know it, for example. I have had good success replacing the
vertical scroll bar with a custom control, and taking complete control of
the display. But this is a considerable project.

I would really like to see some work done in this area for some release of
Access. All that is needed is to know which row is at the top and which has
the focus, and be able to restore that. Conventions would be needed,
however, in case either of those is deleted, or if any of the sort keys are
disturbed. It doesn't seem as simple as is felt at first impression.

David's situation contemplates the ability to simultaneously affect multiple
rows in the display simultaneously. That may be even worse. I haven't
thoroughly analyzed the possible implications of that yet. I don't even
know what would be the "natural" way a person might typically expect this to
act. Personally, I don't know HOW I would expect it to act.

For example, the screen shows 4 rows. The user deletes all 4 in one pass.
What should it look like after confirming the deletions? Show the next 4
rows? The previous 4? The previous 2 and the next 2? Re-execute the
previous search (if any)? Proportion the position among all the rows in the
current search order?

I'm not convinced any of these alternatives would seem natural. I'm not
convinced any of them would be superior to just moving to the top of the
table or query and starting over. At least that's consistent, and
understandable.

If, however, the currently selected row is not deleted, and not changed in
the sort key order, then having it remain at the same position on the screen
does seem natural, assuming that, if it's in, say, the 3rd position, there
are still at least 2 rows in the table/query that precede it. In that case,
it should move up on the screen, but certainly still be selected (focus).

Not simple, but I agree something should be done. But not without some
research into what "natural" means in a situation where much of what is
imposed is not intuitively obvious.

I'd like to see a set of specifications for different ways of doing this,
with a system-wide default behavior and the option to define this on a
datasheet or form basis.

Tom Ellison

 

[Sylvain Lafontaine]

When there is a single of multiple deletions executed from an external
command (ie, other than by the user pressing the delete key), I think that
the user will understand if the location of the display is not perfect after
that.

Making sure that the current record is displayed on the screen after the
deletion or the next one if the old current record was part of the deletion
should be sufficient for most cases; even if it's not *perfect* or
*natural*.

However, I agree with you about the lack of information on the top row
displayed as well as to other limitations. For example, the resync of the
display after a requery should be done automatically by Access when
possible; not manually.

 

[David via AccessMonster.com]

You can store the primary key of the current record and return to it after

making the requery.

And when to call requery? The order of delete events is

BeforeDelConfirm-->AfterDelConfirm-->Delete

where Delete is called for every selected record. So if the user selects 4
records the OnDelete habdler is called 4 times. Making requery does not make
sense in this case because it can slow down the deletion and I am afraid that
the selection can get lost after the first requery

The first event triggered after OnDelete is OnCurrent but even here you
cannot call requery as the system would call OnCurrent periodically with no
response to OS

 

[Sylvain Lafontaine]

Maybe you could set a flag so that only a single requery is called on the
OnCurrent event after one or more Delete?