512MB |
Hi everybody,
This is probably going to sound unorthodox, but I have to log records that are deleted...I know, go figure.
Anyway, I have a form with a (continuous) subform, and on the subform I have a command button, called cmdDelete, that launches a dialog form, called frmDeleteCurrentRecord, where the user has to enter their name and a reason.
I have managed, through a ton of trial and tribulation, found a way to delete the current record on the subform from the query, qryEpisodeDetail, using SQL. I had to pass parameters to the dialog form in order to create the delete query.
The problem I'm having, is that after the delete query runs and I'm returned to the subform, it says "#Deleted" in each of the controls on the subform. I have tried to refresh/requery in what feels like 100 different ways, but I keep getting error messages about how I need to save the current record.
Here's the code for the cmdDelete: -
Private Sub cmdDelete_Click()
-
-
Dim strConfirmDelete As String, strConfirmDeleteTitle As String
-
Dim strCancelDelete As String, strCancelDeleteTitle As String
-
Dim strNullDelete As String, strNullDeleteTitle As String
-
Dim varParameter
-
Dim varWhere As String
-
-
'Parameters to be passed 0-8
-
Dim strEpisodeDetailID, strPatientDetailIDFK, strFormIDFK, strDeficiencyIDFK
-
Dim strProgressNoteIDFK, strDeficiencyDate, strQuantity, strEmployeeIDFK
-
Dim strCorrectedDate
-
-
strConfirmDelete = "Are you sure you want to delete this record?" + Chr(13) + Chr(13) + "You will be required to log the deletion."
-
strConfirmDeleteTitle = "Confirm Delete"
-
strCancelDelete = "The delete operation has been cancelled."
-
strCancelDeleteTitle = "Delete Cancelled"
-
strNullDelete = "The record you are trying to delete does not exist."
-
strNullDeleteTitle = "Delete Failed: No Record"
-
-
varWhere = "[EpisodeDetailID] = " & Me.EpisodeDetailID
-
-
strEpisodeDetailID = Me.EpisodeDetailID
-
strPatientDetailIDFK = Me.PatientDetailIDFK
-
strFormIDFK = Me.FormIDFK
-
strDeficiencyIDFK = Me.DeficiencyIDFK
-
strProgressNoteIDFK = Me.ProgressNoteIDFK
-
strDeficiencyDate = Me.DeficiencyDate
-
strQuantity = Me.Quantity
-
strEmployeeIDFK = Me.EmployeeIDFK
-
strCorrectedDate = Me.CorrectedDate
-
-
varParameter = strEpisodeDetailID & ";" & strPatientDetailIDFK & ";" & strFormIDFK
-
varParameter = varParameter & ";" & strDeficiencyIDFK & ";" & strProgressNoteIDFK
-
varParameter = varParameter & ";" & strDeficiencyDate & ";" & strQuantity
-
varParameter = varParameter & ";" & strEmployeeIDFK & ";" & strCorrectedDate
-
-
If vbYes = MsgBox(strConfirmDelete, vbExclamation + vbYesNo, strConfirmDeleteTitle) Then
-
Forms!frmDeficiency.Visible = False
-
If IsNull(DLookup("EpisodeDetailID", "qryEpisodeDetail", strWhere)) Then
-
MsgBox strNullDelete, vbInformation + vbOKOnly, strNullDeleteTitle
-
Me.FormIDFK.SetFocus
-
Exit Sub
-
Else
-
DoCmd.OpenForm "frmDeleteCurrentRecord", DataMode:=acFormAdd, _
-
WindowMode:=acDialog, openArgs:=varParameter
-
End If
-
Else
-
MsgBox strCancelDelete, vbInformation + vbOKOnly, strCancelDeleteTitle
-
Me.FormIDFK.SetFocus
-
Exit Sub
-
End If
-
-
End Sub
-
And here's the code for the cmdSubmit on the frmDeleteCurrentRecord: -
Private Sub cmdSubmit_Click()
-
-
Dim varEpisodeDetailID As String, varPatientDetailIDFK As String, _
-
varFormIDFK As String, varDeficiencyIDFK As String, _
-
varProgressNoteIDFK As String, varDeficiencyDate As String, _
-
varQuantity As String, varEmployeeIDFK As String, _
-
varCorrectedDate As String, varAuditorFName As String, _
-
varAuditorLName As String, varReason As String, varDateDeleted As String
-
Dim nullErrorTitle As String, nullErrorMessage As String
-
Dim strConfirmDelete As String, strConfirmDeleteTitle As String
-
Dim strCancelDelete As String, strCancelDeleteTitle As String
-
Dim ctl As Control
-
Dim strDeleteRecordSQL As String, strLogDeletionSQL As String
-
Dim strDeleteReasonSQL As String
-
-
'Title for fields with null values
-
'------------------------------------
-
nullErrorTitle = ": Null Value"
-
-
'String for null values
-
'------------------------
-
nullErrorMessage = " cannot be blank. Please enter a value in this field."
-
-
'Test for null values
-
'-----------------------
-
For Each ctl In Me.Controls
-
If IsNull(ctl) And Not IsNothing(ctl.Tag) Then
-
ctlName = ctl.Tag
-
MsgBox ctlName + nullErrorMessage, vbExclamation, ctlName + nullErrorTitle
-
ctl.SetFocus
-
Exit Sub
-
End If
-
Next ctl ' End of null value test
-
-
varAuditorFName = Me.AuditorFirstName
-
varAuditorLName = Me.AuditorLastName
-
varReason = Me.ReasonDeleted
-
varDateDeleted = Me.DateDeleted
-
varEpisodeDetailID = Me.EpisodeDetailIDFK
-
varPatientDetailIDFK = Me.PatientDetailIDFK
-
varFormIDFK = Me.FormIDFK
-
varDeficiencyIDFK = Me.DeficiencyIDFK
-
varProgressNoteIDFK = Me.ProgressNoteIDFK
-
varDeficiencyDate = Me.DeficiencyDate
-
varQuantity = Me.Quantity
-
varEmployeeIDFK = Me.EmployeeIDFK
-
varCorrectedDate = Me.CorrectedDate
-
-
strLogDeletionSQL = "INSERT INTO qryDeletedRecordsLog ( PatientDetailIDFK, EpisodeDetailID, FormIDFK, ProgressNoteIDFK, DeficiencyIDFK, DeficiencyDate, Quantity, EmployeeIDFK, CorrectedDate ) " & _
-
"SELECT qryEpisodeDetail.PatientDetailIDFK, qryEpisodeDetail.EpisodeDetailID, qryEpisodeDetail.FormIDFK, qryEpisodeDetail.ProgressNoteIDFK, qryEpisodeDetail.DeficiencyIDFK, qryEpisodeDetail.DeficiencyDate, qryEpisodeDetail.Quantity, qryEpisodeDetail.EmployeeIDFK, qryEpisodeDetail.CorrectedDate " & _
-
"FROM qryEpisodeDetail " & _
-
"WHERE (((qryEpisodeDetail.EpisodeDetailID)= " & varEpisodeDetailID & "));"
-
-
strDeleteReasonSQL = "UPDATE qryDeletedRecordsLog " & _
-
"SET AuditorFirstName ='" & varAuditorFName & "', " & _
-
"AuditorLastName ='" & varAuditorLName & "', " & _
-
"ReasonDeleted = '" & varReason & "', " & _
-
"DateDeleted = #" & varDateDeleted & "# " & _
-
"WHERE (((qryDeletedRecordsLog.EpisodeDetailID)= " & varEpisodeDetailID & "));"
-
-
strConfirmDelete = "Are you sure you want to delete this record?" + Chr(13) + Chr(13) + "Warning: The action cannot be undone."
-
strConfirmDeleteTitle = "Confirm Delete"
-
strCancelDelete = "The delete operation has been cancelled."
-
strCancelDeleteTitle = "Delete Cancelled"
-
-
strDeleteRecordSQL = "DELETE * FROM tblEpisodeDetail " & _
-
"WHERE tblEpisodeDetail.EpisodeDetailID = " & varEpisodeDetailID & ";"
-
-
If vbYes = MsgBox(strConfirmDelete, vbExclamation + vbYesNo, strConfirmDeleteTitle) Then
-
DoCmd.RunSQL strLogDeletionSQL
-
DoCmd.RunSQL strDeleteReasonSQL
-
DoCmd.RunSQL strDeleteRecordSQL
-
CloseMe
-
'THIS IS WHERE IT WON'T UPDATE - Sorry for the all caps, just an attention-getter.
-
Forms![frmDeficiency]![fSubEpisodeDetail].Refresh
-
Forms!frmDeficiency.Visible = True
-
Else
-
Exit Sub
-
End If
-
-
End Sub
-
My intention is to have the delete command function just like the delete command on the toolbar, except for the fact that I need to go to another form real quick, have the user jot down some info, and then return to the updated subform.
It's late, so if this is unclear, please forgive me. I'll be happy to clarify if anything doesn't make sense.
| |
Share:
Expert 256MB |
You are opening the frmDeleteCurrentRecord form in Dialog mode so the code in the 1st form has stopped. Simply do Me.Requery in the line after the OpenForm. Refresh will not get rid of *deleted* records. It takes a Requery.
| | 512MB |
Hi Rural,
I want to make sure I understand, so forgive me if I'm just re-stating what you said. (I'm one of those weirdos that has to repeat things to make sure I can envision what I'm being told)
So I need to put Me.Requery on the subform, right after I open the frmDeleteCurrentRecord?
And if I do this, when I choose my submit command on the dialog that effectively deletes the record, I'll be returned to the requeried subform?
| | Expert 256MB |
You are correct. There is no reason to attempt to Requery the 1st form from the other form. The code will return *only* after you close the 2nd form or make it invisible.
| | 512MB |
Thanks Rural.
After I made the main form visible again, I had to set the focus to a different location in order to get the requery to work.
I ended up just setting the focus to the tab control that the subform is on and it accepted the event.
Again, thanks for your help. I worked on this issue for at least 2 hours last night...I didn't realize exactly where control went when I opened the dialog.
Cheers!
| | Expert 256MB |
Excellent! Glad you got it sorted.
| | |
I have an unusual design that Microsoft Access 2003/2007 won't cooperate with... I have forced cooperation on everything except one issue - record deletion.
My form design involves a recursively nested form. In other words, the form, m_settings_menueditor has a subform, m_settings_menueditor. It contains code invoked on the Form_Open event that modifies the form's recordset (so that the root form only displays root nodes in the tree, and nested forms only display the child nodes of their parent.) The forms are displayed as data-sheets. This implements a tree structure, fully editable as a Microsoft Access datasheet, with open/close tree branch buttons in the form of open/close subdatasheet. In the table, "ui_modes_lkp", the menu elements (and submenu elements, and subsubmenu elements etc.) are specified. Root nodes are indicated with Null entries in the parent_mode
With or without database relationships (enforcing referential integrity), the standard MS Access DELETE events just don't seem to work... When I delete a record from the root form, it works fine... But when I delete records from child subforms, they appear to have been deleted (they disappear from the form) but I see a funny symbol in the record selector of the parent forms, all the way back to the root node... It's like a "no entry" road-sign but in black instead of red. When I close the forms completely and reopen them, I see that the deleted record is back there again. I have tried to trace the problem and it seems like some of my other events may be messing with the normal operation of Access's record deletion routines, AFTER the FORM_DELETE event and BEFORE the BEFORE_DELETE_CONFIRM event...
So to work around this problem, I thought I'd manually implement the DELETE function when the FORM_DELETE event is invoked, and cancel the event using
DoCmd.CancelEvent
This leaves a row in the subdatasheet with "Deleted" in place of all the fields. I have attempted to shift the focus away and run DoCmd.Requery, and various VBA based Requery methods (all from within the relevant subform), but it just won't get rid of the "Deleted" record... The VBA-based methods just give error messages saying "The record has been deleted."
This technique currently looks messy but at least it actually deletes the record...
I know my design is a little weird in some respects but I'd be VERY grateful for anyone who can help me with this.
| | Expert 256MB |
You will get better response if you start a new thread rather than tagging on to an existing one.
| | |
OK - I'm going out now... I'll do that later.
| | |
Not sure if you guys got this resolved, but here's the solution: - Me.RecordSource = Me.RecordSource
-
Me.Requery
| | Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
8 posts
views
Thread by Steve |
last post: by
|
3 posts
views
Thread by Uwe Range |
last post: by
|
3 posts
views
Thread by Maria |
last post: by
|
3 posts
views
Thread by David Altemir |
last post: by
|
16 posts
views
Thread by MartinR |
last post: by
|
4 posts
views
Thread by Steven |
last post: by
|
2 posts
views
Thread by Phil Stanton |
last post: by
| | | | | | | | | | | | |