Deleting a record

[Pete Davis]

First of all, thanks to everyone who has helped me out so far. I'm nearly
done with my app thanks to the great support here. Just one more issue:

I have a form where I need to delete the current record from within code in
a button. If certain conditions are met, then I want the record deleted,
otherwise no.

How do I do this?

Thanks.

Pete

 

[Bas Cost Budde]

First of all, thanks to everyone who has helped me out so far. I'm nearly
done with my app thanks to the great support here. Just one more issue:

I have a form where I need to delete the current record from within code in
a button. If certain conditions are met, then I want the record deleted,
otherwise no.

Something like

if {conditions} runcommand accmddeleterecord

?

Do these conditions live in the database, more specifically in the form,
or in code?

 

[Pete Davis]

Sorry, I need to add some more information because my post isn't accurate
enough to give an answer:

I know I can do this:

DoCmd.RunCommand acCmdDeleteRecord

Which I've done, but here's the problem.

I have a form called Picture which displays data from a table of the same
name. However, almost all of the work done is independent of the Access form
binding.

The table Picture has the following fields:

PictureID - Autonumber
PropertyDetailsID - Number
PictureNumber - Number
PictureData - Ole Object

The Picture form is passed a PropertyDetails ID as an argument when it is
opened.

I have a picture number combo that allows you to select a number from 1-4.

There is an Add/Replace picture button which loads a picture from a file and
places it in the Picture Data field.

Where I run into problems is that I have a Delete Picture button. Using ADO,
it deletes the record from the database.

When one tries to close the Picture form after deleting a picture, there's
an error because the PropertyDetailsID field is null (which isn't allowed by
the DB).

Basically, I want Access to ignore the fact that there's a current record so
that when I close the Picture form, I don't get the error message.

Despite the error message, everything works as it's supposed to and
supressing the error message is sufficient for my needs, if that's possible.

Anyone have any ideas?

Pete

 

[Jeff Conrad]

Hi,

Use the Form's BeforeDelConfirm event since it has a cancel event.
Here's just one example:

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
If Me.txtHobbies <> "Access Junkie" Then
MsgBox "Sorry not deleting that one"
Cancel = True
Else
' Proceed with delete
End If
End Sub

Hope that helps,
Jeff Conrad
Bend, Oregon

 

[Pete Davis]

The conditions exist in the form, not the database.

Really, if I can simply conceal the error message, I'd be satisfied with
that. It's non-fatal, and the code works as it's supposed to, otherwise.

It's simply when closing the form it chokes on the fact that the current
record has a null value so it can't save it.

What about some way to make it not even try to save the record. Anything
like that?

Pete

 

[Bas Cost Budde]

The conditions exist in the form, not the database.

Really, if I can simply conceal the error message, I'd be satisfied with
that. It's non-fatal, and the code works as it's supposed to, otherwise.

Shouldn't you provide some feedback to the user?

It's simply when closing the form it chokes on the fact that the current
record has a null value so it can't save it.

What about some way to make it not even try to save the record. Anything
like that?

Me.Undo will undo changes.
Docmd.close acform,me.name,acsaveno will close without saving (I feel
that winds up to the same)

 

[Pete Davis]

I do provide feedback to the user. I ask them if they're sure they want to
delete the record.

I'll try to figure out a way to incorporate the code you provided.

Pete

 

[Bas Cost Budde]

I do provide feedback to the user. I ask them if they're sure they want to
delete the record.

It occurs to me that I haven't asked what error you mean, actually. You
do say it is non-fatal; if it is a trappable (VBA) error, you can
suppress it by starting your procedure with

on error resume next

(or maybe a little more sophisticated error handler)
If it is an Access error, maybe

docmd.setwarnings false

helps

 

[Pete Davis]

I thought about the On Error Resume Next, but I don't know at what point the
message is being sent in the events.

I delete the record data on a button click event. I get the error when the
form is closed. The specific error message is:

The field "Picture.PropertyDetailsID" cannot contain a Null value because
the Required property for this field is set to True. Enter a value in this
field. (with OK and Help buttons).

I then press OK and get:

You can't save this record at this time.
Microsoft Access may have encountered an error while trying to save a
record. If you close the object now, the data changes you made will be lost.
do you want to close the database object anyway?

So, I'd really like to have both of these messages supressed if possible.

Thanks for any help.

Pete

 

[Bas Cost Budde]

I thought about the On Error Resume Next, but I don't know at what point the
message is being sent in the events.

I delete the record data on a button click event. I get the error when the
form is closed. The specific error message is:

The field "Picture.PropertyDetailsID" cannot contain a Null value because
the Required property for this field is set to True. Enter a value in this
field. (with OK and Help buttons).

I then press OK and get:

You can't save this record at this time.
Microsoft Access may have encountered an error while trying to save a
record. If you close the object now, the data changes you made will be lost.
do you want to close the database object anyway?

Obviously, in the delete process propertyDetailsID gets set to Null. If
you create error handlers in every routine on the line, can you get the
offending statement? (Well, probably it is the delete itself which tells
us noting. Nothing new, that is).

No, wait. You get the error on *close*. That means you can do

docmd.close acform, me.name,acsaveno

anyway; does that help? Could you try to add a line like

Picture.PropertyDetailsID = Picture.PropertyDetailsID

? (strange, but sometimes this helps)

 

[Pete Davis]

Thanks, your code:

DoCmd.Close acForm, Me.Name, acSaveNo

worked perfectly. I simply created a "Close Form" button and did it there.
That will be suitable for our needs.

Thanks so much for your help.

Pete

 

[Bas Cost Budde]

Thanks, your code:

DoCmd.Close acForm, Me.Name, acSaveNo

worked perfectly. I simply created a "Close Form" button and did it there.
That will be suitable for our needs.

Thanks so much for your help.

Pete

Don't forget to set the CloseButton property to false. I wouldn't know
how to do acSaveNo in the Unload event...