Delete Button

[Guest]

I have a table HYInReg which is in a one to many relationship with two table
- deptcode and designation which appear as combo boxes on the form. I have
created a form which combines the functions of add new record, edit record,
and delete record. Add and edit appear to work fine but the delete command
button is not. I originally created the button using the wizard and applied
some VBA code of my own. If the user selects the record (from a combo box)
enters the data in required fields then selects delete record is works okay -
it ask for confirmation then logs the deletion in the logging table
(audHYInReg). However if they select the delete button without entry the data
it displays the necessary messages if they then enter data then select delete
record it only records it as an edit in the logging table. Any help would be
greatly appreciated this is driving me nuts now. Posted below is the code I
have behind the delete command button.
Private Sub RegDelRec_Click()
On Error GoTo Err_RegDelRec_Click

'Disable/Grey Out the "Add Record" and "Edit Record" Buttons
Me!AddNewRec.Enabled = False
Me!EditRec.Enabled = False

'Disable/Grey Out the Text Boxes Not Applicable To Delete A Record
Me!RegisterNumber.Enabled = False
Me!DeptCode.Enabled = False
Me!DateSent.Enabled = False
Me!Designation.Enabled = False
Me!SentTo.Enabled = False
Me!CompanyNames.Enabled = False
Me!CopiedTo.Enabled = False
Me!Subject.Enabled = False
Me!Hyperlink1.Enabled = False
Me!Hyperlink2.Enabled = False
Me!Hyperlink3.Enabled = False
Me!ReasonsforEdit.Enabled = False

If Me.RegDelRec.Enabled And IsNull(Me.ReasonforDel) Then
MsgBox conMESSAGE, vbExclamation, "REASONS FOR DELETION MUST BE
COMPLETED BEFORE CONTINUING"

If Me.RegDelRec.Enabled And IsNull(Me.DeleteDate) Then
MsgBox conMESSAGE, vbExclamation, "DELETION DATE MUST BE COMPLETED
BEFORE CONTINUING"

If Me.RegDelRec.Enabled And IsNull(Me.DeptReqDel) Then
MsgBox conMESSAGE, vbExclamation, "DEPARTMENT REQUIRING THE DELETION
MUST BE COMPLETED BEFORE CONTINUING"

If Me.RegDelRec.Enabled And IsNull(Me.PersonReqDel) Then
MsgBox conMESSAGE, vbExclamation, "PERSONS REQUIRING THE DELETION MUST
BE COMPLETED BEFORE CONTINUING"

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

End If
End If
End If
End If

Exit_RegDelRec_Click:
Exit Sub

Err_RegDelRec_Click:
MsgBox Err.Description
Resume Exit_RegDelRec_Click

End Sub

 

[Guest]

Your nested If….End If constructs appear to be wrongly structured. If the
first If statement evaluates to False, i.e. if the reason for deletion has
been entered, then none of the following code would be executed. What you
should have is If…ElseIf… ElseIf… ElseIf…Else…End If. That way if the first
If evaluates to false the next ElseIf will execute and so on, so the relevant
message will be displayed when the first Null control is encountered.
Otherwise the code to delete the record will execute. Incidentally you can
now use:

RunCommand acCmdDeleteRecord

instead of the two lines of outdated code the wizard uses.

Also you are testing for the button being Enabled. This is not necessary as
the code will only ever be executed if the button is enabled as it is an
event procedure of the button itself.

Ken Sheridan
Stafford, England

 

[Guest]

Thank you Ken this worked a dream many many thank you's