472,791 Members | 1,758 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,791 software developers and data experts.

Delete a record from a dialog and requery the form

beacon
579 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click()
  2.  
  3.     Dim strConfirmDelete As String, strConfirmDeleteTitle As String
  4.     Dim strCancelDelete As String, strCancelDeleteTitle As String
  5.     Dim strNullDelete As String, strNullDeleteTitle As String
  6.     Dim varParameter
  7.     Dim varWhere As String
  8.  
  9.     'Parameters to be passed 0-8
  10.     Dim strEpisodeDetailID, strPatientDetailIDFK, strFormIDFK, strDeficiencyIDFK
  11.     Dim strProgressNoteIDFK, strDeficiencyDate, strQuantity, strEmployeeIDFK
  12.     Dim strCorrectedDate
  13.  
  14.     strConfirmDelete = "Are you sure you want to delete this record?" + Chr(13) + Chr(13) + "You will be required to log the deletion."
  15.     strConfirmDeleteTitle = "Confirm Delete"
  16.     strCancelDelete = "The delete operation has been cancelled."
  17.     strCancelDeleteTitle = "Delete Cancelled"
  18.     strNullDelete = "The record you are trying to delete does not exist."
  19.     strNullDeleteTitle = "Delete Failed: No Record"
  20.  
  21.     varWhere = "[EpisodeDetailID] = " & Me.EpisodeDetailID
  22.  
  23.     strEpisodeDetailID = Me.EpisodeDetailID
  24.     strPatientDetailIDFK = Me.PatientDetailIDFK
  25.     strFormIDFK = Me.FormIDFK
  26.     strDeficiencyIDFK = Me.DeficiencyIDFK
  27.     strProgressNoteIDFK = Me.ProgressNoteIDFK
  28.     strDeficiencyDate = Me.DeficiencyDate
  29.     strQuantity = Me.Quantity
  30.     strEmployeeIDFK = Me.EmployeeIDFK
  31.     strCorrectedDate = Me.CorrectedDate
  32.  
  33.     varParameter = strEpisodeDetailID & ";" & strPatientDetailIDFK & ";" & strFormIDFK
  34.     varParameter = varParameter & ";" & strDeficiencyIDFK & ";" & strProgressNoteIDFK
  35.     varParameter = varParameter & ";" & strDeficiencyDate & ";" & strQuantity
  36.     varParameter = varParameter & ";" & strEmployeeIDFK & ";" & strCorrectedDate
  37.  
  38.     If vbYes = MsgBox(strConfirmDelete, vbExclamation + vbYesNo, strConfirmDeleteTitle) Then
  39.         Forms!frmDeficiency.Visible = False
  40.         If IsNull(DLookup("EpisodeDetailID", "qryEpisodeDetail", strWhere)) Then
  41.             MsgBox strNullDelete, vbInformation + vbOKOnly, strNullDeleteTitle
  42.             Me.FormIDFK.SetFocus
  43.             Exit Sub
  44.         Else
  45.             DoCmd.OpenForm "frmDeleteCurrentRecord", DataMode:=acFormAdd, _
  46.                 WindowMode:=acDialog, openArgs:=varParameter
  47.         End If
  48.     Else
  49.         MsgBox strCancelDelete, vbInformation + vbOKOnly, strCancelDeleteTitle
  50.         Me.FormIDFK.SetFocus
  51.         Exit Sub
  52.     End If
  53.  
  54. End Sub
  55.  
And here's the code for the cmdSubmit on the frmDeleteCurrentRecord:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSubmit_Click()
  2.  
  3.     Dim varEpisodeDetailID As String, varPatientDetailIDFK As String, _
  4.         varFormIDFK As String, varDeficiencyIDFK As String, _
  5.         varProgressNoteIDFK As String, varDeficiencyDate As String, _
  6.         varQuantity As String, varEmployeeIDFK As String, _
  7.         varCorrectedDate As String, varAuditorFName As String, _
  8.         varAuditorLName As String, varReason As String, varDateDeleted As String
  9.     Dim nullErrorTitle As String, nullErrorMessage As String
  10.     Dim strConfirmDelete As String, strConfirmDeleteTitle As String
  11.     Dim strCancelDelete As String, strCancelDeleteTitle As String
  12.     Dim ctl As Control
  13.     Dim strDeleteRecordSQL As String, strLogDeletionSQL As String
  14.     Dim strDeleteReasonSQL As String
  15.  
  16.     'Title for fields with null values
  17.     '------------------------------------
  18.     nullErrorTitle = ": Null Value"
  19.  
  20.     'String for null values
  21.     '------------------------
  22.     nullErrorMessage = " cannot be blank. Please enter a value in this field."
  23.  
  24.     'Test for null values
  25.     '-----------------------
  26.     For Each ctl In Me.Controls
  27.        If IsNull(ctl) And Not IsNothing(ctl.Tag) Then
  28.             ctlName = ctl.Tag
  29.             MsgBox ctlName + nullErrorMessage, vbExclamation, ctlName + nullErrorTitle
  30.             ctl.SetFocus
  31.             Exit Sub
  32.         End If
  33.     Next ctl    ' End of null value test
  34.  
  35.     varAuditorFName = Me.AuditorFirstName
  36.     varAuditorLName = Me.AuditorLastName
  37.     varReason = Me.ReasonDeleted
  38.     varDateDeleted = Me.DateDeleted
  39.     varEpisodeDetailID = Me.EpisodeDetailIDFK
  40.     varPatientDetailIDFK = Me.PatientDetailIDFK
  41.     varFormIDFK = Me.FormIDFK
  42.     varDeficiencyIDFK = Me.DeficiencyIDFK
  43.     varProgressNoteIDFK = Me.ProgressNoteIDFK
  44.     varDeficiencyDate = Me.DeficiencyDate
  45.     varQuantity = Me.Quantity
  46.     varEmployeeIDFK = Me.EmployeeIDFK
  47.     varCorrectedDate = Me.CorrectedDate
  48.  
  49.     strLogDeletionSQL = "INSERT INTO qryDeletedRecordsLog ( PatientDetailIDFK, EpisodeDetailID, FormIDFK, ProgressNoteIDFK, DeficiencyIDFK, DeficiencyDate, Quantity, EmployeeIDFK, CorrectedDate ) " & _
  50.                         "SELECT qryEpisodeDetail.PatientDetailIDFK, qryEpisodeDetail.EpisodeDetailID, qryEpisodeDetail.FormIDFK, qryEpisodeDetail.ProgressNoteIDFK, qryEpisodeDetail.DeficiencyIDFK, qryEpisodeDetail.DeficiencyDate, qryEpisodeDetail.Quantity, qryEpisodeDetail.EmployeeIDFK, qryEpisodeDetail.CorrectedDate " & _
  51.                         "FROM qryEpisodeDetail " & _
  52.                         "WHERE (((qryEpisodeDetail.EpisodeDetailID)= " & varEpisodeDetailID & "));"
  53.  
  54.     strDeleteReasonSQL = "UPDATE qryDeletedRecordsLog " & _
  55.                          "SET AuditorFirstName ='" & varAuditorFName & "', " & _
  56.                          "AuditorLastName ='" & varAuditorLName & "', " & _
  57.                          "ReasonDeleted = '" & varReason & "', " & _
  58.                          "DateDeleted = #" & varDateDeleted & "# " & _
  59.                          "WHERE (((qryDeletedRecordsLog.EpisodeDetailID)= " & varEpisodeDetailID & "));"
  60.  
  61.     strConfirmDelete = "Are you sure you want to delete this record?" + Chr(13) + Chr(13) + "Warning: The action cannot be undone."
  62.     strConfirmDeleteTitle = "Confirm Delete"
  63.     strCancelDelete = "The delete operation has been cancelled."
  64.     strCancelDeleteTitle = "Delete Cancelled"
  65.  
  66.     strDeleteRecordSQL = "DELETE * FROM tblEpisodeDetail " & _
  67.                          "WHERE tblEpisodeDetail.EpisodeDetailID = " & varEpisodeDetailID & ";"
  68.  
  69.     If vbYes = MsgBox(strConfirmDelete, vbExclamation + vbYesNo, strConfirmDeleteTitle) Then
  70.         DoCmd.RunSQL strLogDeletionSQL
  71.         DoCmd.RunSQL strDeleteReasonSQL
  72.         DoCmd.RunSQL strDeleteRecordSQL
  73.         CloseMe
  74.         'THIS IS WHERE IT WON'T UPDATE - Sorry for the all caps, just an attention-getter.
  75.         Forms![frmDeficiency]![fSubEpisodeDetail].Refresh
  76.         Forms!frmDeficiency.Visible = True
  77.     Else
  78.         Exit Sub
  79.     End If
  80.  
  81. End Sub
  82.  
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.
Feb 15 '09 #1
10 7005
RuralGuy
375 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.
Feb 15 '09 #2
beacon
579 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?
Feb 15 '09 #3
RuralGuy
375 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.
Feb 15 '09 #4
beacon
579 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!
Feb 15 '09 #5
RuralGuy
375 Expert 256MB
Excellent! Glad you got it sorted.
Feb 15 '09 #6
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.
Mar 11 '09 #7
RuralGuy
375 Expert 256MB
You will get better response if you start a new thread rather than tagging on to an existing one.
Mar 11 '09 #8
OK - I'm going out now... I'll do that later.
Mar 11 '09 #9
Here's the address of the new discussion on deleting records within recursively nested subforms with self-join relationships on a single table:

http://bytes.com/topic/access/answer...delete-requery
Mar 13 '09 #10
LeeGr
1
Not sure if you guys got this resolved, but here's the solution:
Expand|Select|Wrap|Line Numbers
  1. Me.RecordSource = Me.RecordSource
  2. Me.Requery
Oct 31 '18 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Steve | last post by:
I have several pairs of synchronized subforms in an application. I have a Delete button for each pair that uses the following code or similar to delete a record in the second subform: ...
3
by: Uwe Range | last post by:
Hi to all, I am displaying a list of records in a subform which is embedded in a popup main form (in order to ensure that users close the form when leaving it). It seems to be impossible to...
3
by: Maria | last post by:
Is there another way to delete the current record in a subform from the main form, another subform or a sub-subform other than setting focus on a field in the subform and using run command...
3
by: David Altemir | last post by:
I have a button on "Form A" in the right margin of every record in my recordset. When you click on a button, a dialog box ("Form B") pops up that gives more details about that particular record. ...
16
by: MartinR | last post by:
I would like to know the code that i should use to delete a record without the message box saying "You are about to delete a record, are you sure..." poping up. At the moment i am using the...
4
by: Steven | last post by:
Hi, Would need some thought about using a button of the existing form to search record before deleting it. Any quick help is very appreciated. Steve
2
by: Phil Stanton | last post by:
Deleting a record on a membership form. Does some VB checks to see if the family are still in the list. Warns me there is no going back if I delete the record, so I say OK and it appears to delete...
3
by: Constantine AI | last post by:
Hi i am trying to DELETE a Record if the details do not exist within a table. Here is my code below; Dim strSQL As String Dim strSQL2 As String Dim Reply As String Dim db As...
31
by: matthewslyman | last post by:
I have an unusual design and some very unusual issues with my code... I have forced Access to cooperate on everything except one issue - record deletion. My form design involves a recursively...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.