Ms Access Form Delete Event 1

[JIMRW]

Hi, I have just been given the job of making changes to an existing access application (im not an access developer so its fairly new to me)

I have a form which lists records and i want to control the deletion of records so that if the record has a certain status, it can be deleted, otherwise the status is just updated

I understand from the access help that if i write some code on the delete event, i can implement the logic i describe above.

Can anybody provide some advice on the best way to do this?

Thanks in advance

 

[zrzr]

I think the on delete event is not exactly what you need, because it's called to know what to do when you delete a record, and you don't know if the record must be deleted or not.

To my mind, you should add a Delete button in your form, and use the click event on this button. Then, in the VBA code you can write something like :

if [condition] then
'delete the current record
dim rst as DAO.Recordset
set rst = Me.RecordsetClone
rst.delete
rst.close
else
'update the current record
Me.dirty = False
end if

That's all, I hope it'll help !

 

[JIMRW]

Thanks for the help, but its not what im after. Im using a completely data bound subform, and currently the user just presses the delete key to remove a record, but the requirements have changed which is why im asking this question. the microsoft help says

"By running a macro or an event procedure when the Delete event occurs, you can prevent a record from being deleted or allow a record to be deleted only under certain conditions."

which is what im after but im not sure how to implement it

Thanks

 

[zrzr]

I have the french version of Access, and in the help file, I've the following exemple, which prevent someone from deleting any record. I think you just have to add a condition before.

this is the code of the delete event :

Private Sub Form_Delete(Cancel As Integer)
Cancel = True
MsgBox "This record can't be deleted."
End Sub

I hope It'll be right this time

 

[JIMRW]

Thanks, thats what i was after. I have another question though, how can i access a field from the current record (the one the user is attempting to delete)? It is the contents of this field that will determine whether it is to be deleted or not

Thanks again

 

[perrymans]

ZRZR,

Access wizard produces the following for Delete:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70


What is the benefit of your code over this one?

Thanks. Sean.

 

[zrzr]

JIMRW >
If you want to access to value of a field in a form, the syntax is :
Forms![nameoftheform].[Nameofthefield].Value

If the form is the current form(I think it's the case for you), you only have to put :
Me.[Nameofthefield].Value

perrymans > I don't know if there's any benefit with my code. The code generated by the Access Wizard uses Access menu. So if, in any other version of Access, the menu changes ... you will have to modify the code ...

 

[perrymans]

Good reason. Thanks. Sean.