When try to delete record, required field can't be null

[matchorno]

I have a form that is bound to a table. My situation is: if a user starts to
fill in the form, but changes their mind, they click a delete button.
However if, they have happened to have not yet filled in the primary key,
they get an error stating: "The field Expungements.ExpungementID cannot
contain a Null value because the required property for this field is set to
true. Enter a value in this field". I want to keep this field set to
required. However I don't want to have them required to fill in this field
if they are just trying to delete the record. Is there a clean anyway around
this, besides putting a dummy entry in that field if they try to delete it?
I want to capture this type of error if they are trying to save the record,
but I don't care if it's filled in if they are just trying to delete the
record. Thanks for your help.

 

[John Spencer]

I use a generic function to handle this situation. I have this in a VBA
module and call it from any form or subform.

So I usually end up with something as simple as
sCmdDelete Me
in the button on the form or subform.

Sometimes I will use
If sCmdDelete (Me) = True Then
'Do other actions here
End If

Public Function sCmdDelete(frmAny As Form, _
Optional strCaption As String) As Boolean
'Deletes the currently selected record on a form

Dim tfAllowDeletions As Boolean
Dim tfReturn As Boolean

tfReturn = True

On Error GoTo ERROR_sCmdDelete

With frmAny
If strCaption = vbNullString Then
strCaption = .Caption
End If

If strCaption = vbNullString Then
strCaption = .name
End If

If .CurrentRecord > 0 Then 'make sure there is a record

If .NewRecord = True And .Dirty = False Then
Exit Function
End If

If MsgBox("Delete selected " & strCaption & " record?", _
vbYesNo + vbCritical, "Delete") = vbYes Then

If .NewRecord = False Then
tfAllowDeletions = .AllowDeletions
If .AllowDeletions = False Then .AllowDeletions = True
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True
.AllowDeletions = tfAllowDeletions
Else
.Undo
End If 'Delete existing records only

End If 'Confirm delete
End If 'Current Record
End With

EXIT_sCmdDelete:
sCmdDelete = tfReturn
DoCmd.SetWarnings True
Exit Function

ERROR_sCmdDelete:
Select Case Err.Number
Case 2501
'action cancelled
Case Else
MsgBox Err.Number & ": " & Err.Description, , "Error:
modButtons.sCmdDelete"
End Select

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

If you are talking about a delete command button that you created, have it
put in a bogus or random number into that field before actually deleting the
record.

 

[Dirk Goldgar]

I have a form that is bound to a table. My situation is: if a user starts
to
fill in the form, but changes their mind, they click a delete button.
However if, they have happened to have not yet filled in the primary key,
they get an error stating: "The field Expungements.ExpungementID cannot
contain a Null value because the required property for this field is set
to
true. Enter a value in this field". I want to keep this field set to
required. However I don't want to have them required to fill in this
field
if they are just trying to delete the record. Is there a clean anyway
around
this, besides putting a dummy entry in that field if they try to delete
it?
I want to capture this type of error if they are trying to save the
record,
but I don't care if it's filled in if they are just trying to delete the
record. Thanks for your help.

Undo the record before deleting it. If it's a new record that hasn't been
saved, undoing it is all you have to do. If it's a record that has already
been saved, undo any changes to it and then delete it. For example,

If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
' or Me.Recordset.Delete
End If