Re: delete record

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hmm... never tried that approach with a nonupdatable recordsource query. Not
sure if the form's BeforeDelConfirm event will occur in such a situation, so
you can try it. Put code in the form's BeforeDelConfirm event procedure --
your code should cancel that event and set the Response variable to
acDataErrContinue constant value, run the programmatic delete, and then exit
the sub.

The more typical way I'd do this is to provide a button in the Detail
section that is labeled "Delete", and let that button's Click event run the
desired code.
--

Ken Snell
<MS ACCESS MVP>




"smk23" <smk23@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F375031D-31F2-4F07-A91A-E1FF16BF63FC@xxxxxxxxxxxxxxxx
one more question: is there a way to have this code run when the user
selects
a record and hits "delete"?
--
sam


"Ken Snell (MVP)" wrote:

Nope, a query that joins four tables together is rarely going to be
updatable.

Assuming that you already have explored other ways to display the data
and
have decided on the current setup, what you'll need to do is to run a
delete
query via programming in the form, and then requery the form's
recordsource
after it's done.

Sample code assuming that the code is running in the subform itself:


Dim dbs As DAO.Database
Dim strSQL As String
On Error GoTo Err_Line
Set dbs = CurrentDb
strSQL = "DELETE * FROM TableName WHERE PrimaryKeyField =" & _
Me.PrimaryKeyFieldName & ";"
DoCmd.Echo False
dbs.Execute strSQL, dbFailOnError
Me.Requery

Exit_Line:
DoCmd.Echo True
dbs.Close
Set dbs = Nothing
Exit Sub

Err_Line:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_Line

--

Ken Snell
<MS ACCESS MVP>


"smk23" <smk23@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9A597307-C9F1-41BD-A750-93807F121106@xxxxxxxxxxxxxxxx
Correct, the recordsource is nonupdatable. I keep fighting with Access
over
this in many scenarios. The PK is in the recordsource.

Could I make a SQL view and specify which single table is updatable?
How
to?
--
sam


"Ken Snell (MVP)" wrote:

Is the primary key value of that one record in the subform's record
source?

I assume that the subform's recordsource query is nonupdatable?

--

Ken Snell
<MS ACCESS MVP>

"smk23" <smk23@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D9A41031-E88A-488D-BDDA-25F067A43A9B@xxxxxxxxxxxxxxxx
I need to delete a record on a data*** subform. In order to display
correctly, the underlying query connects 4 tables. How can I delete
a
record
from one table? The other three tables can be read-only.

Thanks
--
sam








.

Quantcast