delete record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to delete a record on a datasheet 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
 
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?
 
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?
 
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
 
Thanks, Ken. That IS what I needed to know. I need to stop beating this
horse, give up and do a programmatic delete as you suggested.
 
one more question: is there a way to have this code run when the user selects
a record and hits "delete"?
 
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>
 
Back
Top