Deleting a record

  • Thread starter Thread starter Pete Davis
  • Start date Start date
P

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
 
Pete said:
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.
Click to expand...
Something like

if {conditions} runcommand accmddeleterecord

?

Do these conditions live in the database, more specifically in the form,
or in code?
 
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
 
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
 
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
 
Pete said:
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.
Click to expand...

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?
Click to expand...
Me.Undo will undo changes.
Docmd.close acform,me.name,acsaveno will close without saving (I feel
that winds up to the same)
 
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
 
Pete said:
I do provide feedback to the user. I ask them if they're sure they want to
delete the record.
Click to expand...

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
 
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
 
Pete said:
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?
Click to expand...

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)
 
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
 
Pete said:
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
Click to expand...
Don't forget to set the CloseButton property to false. I wouldn't know
how to do acSaveNo in the Unload event...
 
Back
Top