Intercept the delete event on a form

[robert d via AccessMonster.com]

I have a subform. The RecordSource of the subform is set in code. Here's
the line of code:

Me.Subform1.Form.RecordSource = "SELECT Distinct ProjectID, Status FROM
[TEMP_PROJECT_TABLE]"

So maybe the table has twenty rows with about 10 fields per row, but if there
are only two distinct ProjectID, Status, then only these two are displayed.
The subform has two textboxes and is set to display in continuous mode.

I have included the record selectors on the subform. I would like the user
to be able to select a line and delete it if desired.

However, this doesn't work. I guess because Access doesn't know which row to
delete. The table has 20 rows, but only two are displayed on the form
because of the "Distinct" in the SQL statement.

Okay, that's fine.

But, I would like to intercept the delete action that the user has taken.
He/she has clicked on the record selector and then pressed the "Delete" key
on the keyboard. If I can intercept it, I can run my own code to delete all
of the rows in the table that have the selected ProjectID and status.

Can I do this?

 

[Steve Schapel]

Robert,

You are right, a Distinct query is by definition not updateable.

I would normally prefer to use a little Command Button on each row of
the subform for this purpose. But I think you could use the KeyDown or
KeyPress event of the form to do what you describe. Set the KeyPreview
property of the form to Yes.
If KeyCode = vbKeyDelete Then...
Either way, you would do something like this...
CurrentDb.Execute "DELETE * FROM [TEMP_PROJECT_TABLE] WHERE
ProjectID=" & Me.ProjectID & " And Status='" & Me.Status & "')",
dbFailOnError

 

[robert d via AccessMonster.com]

Your mention of the little command button reminded me that I do have another
form where I did something similar on a subform. I have a hyperlink that
replicated with each row of the continuous subform. I can just use a button
instead and make it look like the record selector.

I'll give this a try. This is also far preferable to me rather than try to
intercept Access.

I'll report back if I have problems.

Thanks!

Robert,

You are right, a Distinct query is by definition not updateable.

I would normally prefer to use a little Command Button on each row of
the subform for this purpose. But I think you could use the KeyDown or
KeyPress event of the form to do what you describe. Set the KeyPreview
property of the form to Yes.
If KeyCode = vbKeyDelete Then...
Either way, you would do something like this...
CurrentDb.Execute "DELETE * FROM [TEMP_PROJECT_TABLE] WHERE
ProjectID=" & Me.ProjectID & " And Status='" & Me.Status & "')",
dbFailOnError

I have a subform. The RecordSource of the subform is set in code. Here's
the line of code:

[quoted text clipped - 21 lines]

Can I do this?