can't delete record

[chasday]

I am trying to use a command button on a form to delete the current record
displayed on the form. The record has several related fields in other tables,
but I believe I have successfully deleted all related records with delete
queries.
When I run the code
DoCmd.RunCommand acCmdDeleteRecord
the confirm delete message comes up, and after clicking through it the form
goes blank, appearing like a successful deletion. No error messages. However
the record is still in the table. In table view there isn’t any problem
deleting the record in question i.e. no related records.
I’ve tried using a wizard generated delete button with the same results.
Any ideas?

 

[Jeanette Cunningham]

Chas,
sounds like you are trying to delete the current record before it has been
saved.
When you close the form, the record is saved and that is why it can be
deleted from the table after you close the form.
You won't be able to delete when you are at a new record, and you won't be
able to delete a dirty record.
Try something like this:

If Not Me.NewRecord = True Then
If Me.Dirty Then
Me.Undo
Else
DoCmd.RunCommand acCmdDeleteRecord
End if
End if

Jeanette Cunningham

 

[chasday]

I still get the same result after I close the record and re-open it, so I
know it's not Dirty.

 

[Jeanette Cunningham]

Chas,
another thing to check:
Check the required property of each field in the recordsource for the form.
To do this, open the appropriate tables and in the lower pane of the table
in design view see if any have their required property set to true. If so,
that is done for a very good reason and we won't change that.
Are you comfortable with writing some VBA code to delete a record?

Jeanette Cunningham

 

[chasday]

I found a work-around
I close the form and then delete the record.
ProjectID = Me.ProjectID
'Close this form
DoCmd.Close

'Delete the record
strSQL = "DELETE * FROM [tblProjects] " & "WHERE [ProjectID] = "
& ProjectID
CurrentProject.Connection.Execute strSQL

It doesn't seem like I should have to do it this way, but it works.