I don't really understand what your IsFormLoaded is doing in Form_Current,
or why that makes a difference.
When you delete a record, another record becomes the current one, so it
would not be surprising to see the form's Current event fire after a delete.
If you are logging deletions, be aware that the record for which the Delete
event fired may not actually get deleted. The user may cancel the deletion.
Code in Form_BeforeDelConfirm may cancel it. Or there may be some
engine-level reason why the record is not deleted (cascading deletes failed,
etc.)
The only way to be sure is to check the Status in Form_AfterDelConfirm. So,
what I do is to use Form_Delete to log the information to a temporary table,
and then use Form_AfterDelConfirm to write it from there to the real logging
table. Details:
http://allenbrowne.com/AppAudit.html
If you are using Access 2010, an even better solution would be to use data
macros to log the deletion.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
- Show quoted text -
Mr. Browne,
Thank you for your response and thank you for everything that you
do for the Access community.
As far as my form, here are some details that were not included in
the first post. The form is opened and filtered from another form
based on criteria entered (which determines if a data has already been
entered for that criteria and if it isn't, it inserts that data into
the form and sets the DataEntry = Yes.) There is also a 'Approval'
Button that when clicked, enters data into fields through a INSERT SQL
in VBA and disables subforms and subfields (requested from the
customer).
Here is the code for the Delete event:
*******************CODE START********************
Private Sub Form_Delete(Cancel As Integer)
On Error GoTo Err_Form_Delete
Dim strSQL As String
Dim strUser As String
strUser = Environ("username") 'NT LoginID
If Len(Nz(Me.strApproved, "")) = 0 Then 'Is the data not
Approved?
Cancel = False
strSQL = "INSERT INTO tblapprovallog (fkReport, strLogin,
ysnApproval, "
strSQL = strSQL & "strComment, dtmTransaction) VALUES ("
strSQL = strSQL & Me.pkReport & ", '" & strUser & "', " &
False
strSQL = strSQL & ", 'DELETED " & Me.dtmDate & "-"
strSQL = strSQL & Me.strShift & " Shift', #" & Now() & "#);"
'Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
Else 'The data is Approved, cancel request, tell user
MsgBox "Report must be unapproved before it can be deleted.",
vbInformation, "Approved Report"
Cancel = True
End If
Exit_Form_Delete:
Exit Sub
Err_Form_Delete:
MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf &
Err.Description
Resume Exit_Form_Delete
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Current()
If CurrentProject.AllForms("frmReport").IsLoaded Then 'if frm1Report
is closed don't check
EnableDisable 'check to disable fields
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_AfterDelConfirm(Status As Integer)
If Status = False Then 'if status is false close frmReport
DoCmd.Close acForm, "frmReport", acSaveNo
End If
End Sub
*******************CODE END********************
Basically what I am trying to do when the record is deleted, Check if
the data for the approval is present, if so then cancel the delete,
and if no data in the Approval field, delete the data and close the
frmReport, forcing the User to go back to Dialog to open frmReport. I
am sure there are other ways to do what I am doing. I still consider
myself green and do what I find logical (which I am sure sounds
illogical to some.) I do know that the frmReport sounds silly, but
you got to go with what they call it.
Again thank you so much for your insight and wisdom.
