If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problems with Delete Command
Hi folks,
I have a continuous subform on a main data entry form. Each entry on the subform contains two controls - a combo-box and a textbox beside it. The combobox allows the user to select a WebComType (Web Communication Type - eg work email, home email etc). The box beside it is for entering the actual email address etc. Once a user has inserted a record into the linked table (tblWebComs), I want the user to be able to delete the record by simply clearing the text box. For this purpose, I have inserted the following into the BeforeUpdate event for the combo-box: Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then CurrentDb.Execute "DELETE * FROM WebComs WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "", , dbFailOnError Else Cancel = True Me.Undo End If End If End Sub The subform is linked to my main form (Clients) using this query string auto-generated by Access: SELECT [ContactWebComs].[ContactID], [ContactWebComs].[WebComID] AS ContactWebComs_WebComID, [WebComs].[WebComID] AS WebComs_WebComID, [WebComs].[WebComType], [WebComs].[WebComDetails] FROM WebComs INNER JOIN ContactWebComs ON [WebComs].[WebComID]=[ContactWebComs].[WebComID]; The Child/Master link fields is ContactsID which appears in my contacts table (the record source for the main form) and my junction table (ContactWebComs). When I run the BeforeUpdate code, I experience two problems: (1) When I clear the combo-box and shift the focus (eg to the text box beside it), the message box pops up to confirm the delete as expected. But after that, nothing happens until I change focus again (eg tab to the next row). (2) Once I change focus a second time, a message pops up saying that the record has been deleted - but as soon as I press okay on this message box, Access just shuts down - no error messages or anything, the entire application just shuts down. After I restart the application and check the entries in tblWebComs, I can see that the record I selected was in fact deleted. I've tried a couple of variations for delete command. For instance, I've tried hard coding the WebComID to delete like so: CurrentDb.Execute "DELETE * FROM WebComs WHERE [WebComs].[WebComID]=20", dbFailOnError Again, the record is actually deleted, but I get exactly the same errors. I've also tried running the following code instead of the currentdb.execute line: strSQL = "DELETE * " & _ "FROM WebComs " & _ "WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "" DoCmd.SetWarnings False ' Turn off Access warning messages DoCmd.RunSQL strSQL DoCmd.SetWarnings True ' Warning set on again This results in exactly the same problems - and a correctly deleted record. If I run the above code without the SetWarnings lines, the following happens: (a) my custom message box to popup; (b) then Access's own message box warning that I am about to delete a record from the specified table pops up; (c) then nothing happens until I change focus to another part of the form; (d) then a box pops up saying that "record is deleted" (e) then all the rows in my subform disappear except for the blank "new record" line (f) a check of the table confirms that the record was correctly deleted. The difference here is that Access does not shut down, but I can't stop access from deleting all my rows. If I try to do incorporate a Me.Requery after the outside "If ... Then" statement, Access complains that the line is preventing it from saving anything to the database. If I try a "Me.txtWebComType.Requery" line instead, I get an error saying that "You must save the current field before you run a query action". If I insert a "Me.txtWebComType.Requery" line in the AfterUpdate event instead, I end up with the same problems described above. I all out of ideas. Does anyone else have any pointers as to what might be wrong with my code, or what might be causing these problems? I should mention that the DoCmd code above worked fine when was (incorrectly) deleting entries in my junction table (ContactWebComs) instead of my WebComs table. (Please note, I have cascade update and delete selected for all relationships). TIA Bob |
#2
|
|||
|
|||
Problems with Delete Command
Hi Bob
Hmmm... the "Access shutting down" bit is very nasty. This is clearly a bug, but I suspect it is being triggered by the fact that you are deleting a record which has been locked for editing. Also, you are deleting the record directly using SQL, so the form doesn't know it's going to happen. If Access did not shut down, then I would expect you to see the ugly #Deleted message in the controls for that row. Try this: First, undo the form (Me.Undo) BEFORE attempting to delete. Second, delete the record from the form's recordset, not directly from the table. If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then Me.Recordset.Delete End If End If -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi folks, I have a continuous subform on a main data entry form. Each entry on the subform contains two controls - a combo-box and a textbox beside it. The combobox allows the user to select a WebComType (Web Communication Type - eg work email, home email etc). The box beside it is for entering the actual email address etc. Once a user has inserted a record into the linked table (tblWebComs), I want the user to be able to delete the record by simply clearing the text box. For this purpose, I have inserted the following into the BeforeUpdate event for the combo-box: Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then CurrentDb.Execute "DELETE * FROM WebComs WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "", , dbFailOnError Else Cancel = True Me.Undo End If End If End Sub The subform is linked to my main form (Clients) using this query string auto-generated by Access: SELECT [ContactWebComs].[ContactID], [ContactWebComs].[WebComID] AS ContactWebComs_WebComID, [WebComs].[WebComID] AS WebComs_WebComID, [WebComs].[WebComType], [WebComs].[WebComDetails] FROM WebComs INNER JOIN ContactWebComs ON [WebComs].[WebComID]=[ContactWebComs].[WebComID]; The Child/Master link fields is ContactsID which appears in my contacts table (the record source for the main form) and my junction table (ContactWebComs). When I run the BeforeUpdate code, I experience two problems: (1) When I clear the combo-box and shift the focus (eg to the text box beside it), the message box pops up to confirm the delete as expected. But after that, nothing happens until I change focus again (eg tab to the next row). (2) Once I change focus a second time, a message pops up saying that the record has been deleted - but as soon as I press okay on this message box, Access just shuts down - no error messages or anything, the entire application just shuts down. After I restart the application and check the entries in tblWebComs, I can see that the record I selected was in fact deleted. I've tried a couple of variations for delete command. For instance, I've tried hard coding the WebComID to delete like so: CurrentDb.Execute "DELETE * FROM WebComs WHERE [WebComs].[WebComID]=20", dbFailOnError Again, the record is actually deleted, but I get exactly the same errors. I've also tried running the following code instead of the currentdb.execute line: strSQL = "DELETE * " & _ "FROM WebComs " & _ "WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "" DoCmd.SetWarnings False ' Turn off Access warning messages DoCmd.RunSQL strSQL DoCmd.SetWarnings True ' Warning set on again This results in exactly the same problems - and a correctly deleted record. If I run the above code without the SetWarnings lines, the following happens: (a) my custom message box to popup; (b) then Access's own message box warning that I am about to delete a record from the specified table pops up; (c) then nothing happens until I change focus to another part of the form; (d) then a box pops up saying that "record is deleted" (e) then all the rows in my subform disappear except for the blank "new record" line (f) a check of the table confirms that the record was correctly deleted. The difference here is that Access does not shut down, but I can't stop access from deleting all my rows. If I try to do incorporate a Me.Requery after the outside "If ... Then" statement, Access complains that the line is preventing it from saving anything to the database. If I try a "Me.txtWebComType.Requery" line instead, I get an error saying that "You must save the current field before you run a query action". If I insert a "Me.txtWebComType.Requery" line in the AfterUpdate event instead, I end up with the same problems described above. I all out of ideas. Does anyone else have any pointers as to what might be wrong with my code, or what might be causing these problems? I should mention that the DoCmd code above worked fine when was (incorrectly) deleting entries in my junction table (ContactWebComs) instead of my WebComs table. (Please note, I have cascade update and delete selected for all relationships). TIA Bob |
#3
|
|||
|
|||
Problems with Delete Command
Hi Graham,
I tried just cutting and pasting your code and it didn't work. I assumed that this was because I needed to create a recordset first. Anyways, I had another crack at this and the following code seems to work but any advice you might have on improving it would be appreciated (I assume the second set of Undo/Cancel commands are unnecessary): Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String Dim rstTemp As DAO.Recordset ' Using DAO strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" strSQL = "SELECT * FROM WebComs WHERE [WebComs].[WebComID] = " & Me.ContactWebComs_WebComID & "" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then Set rstTemp = CurrentDb.OpenRecordset(strSQL) ' Make sure the recordset is not empty If Not rstTemp.EOF Then rstTemp.Delete Me.Requery End If rstTemp.Close Set rstTemp = Nothing Else Me.Undo ' do this unconditionally Cancel = True End If End If End Sub I tried the code without the first Undo and Cancel but it didn't work. Amazingly, Access didn't actually shut down but as you predicted the row in subform showed "#Deleted" immediately after the code ran. If I leave the Requery line in without the first set of Undo/Cancel commands, Access complains. Hence, the only way I can get the form to reflect the current state of the database (ie Requery) is if I leave the first set of Undo/Cancel commands in place. Since the code appears in the BeforeUpdate event, logically the the field linked to txtWebComType has not yet been updated by the time the code runs. I can see (now - thanks to you) that if the row is actually deleted before the update event occurs then clearly by the time Access goes to update the field it will error out since the relevant row (and field) no longer exists. This being the case, I would have thought that the error could be avoid (without the first set of Undo/Cancel commands) if I simply move the code to the AfterUpdate event. But this still does not work. Can you explain why this is so? TIA Bob Graham Mandeno wrote: Hi Bob Hmmm... the "Access shutting down" bit is very nasty. This is clearly a bug, but I suspect it is being triggered by the fact that you are deleting a record which has been locked for editing. Also, you are deleting the record directly using SQL, so the form doesn't know it's going to happen. If Access did not shut down, then I would expect you to see the ugly #Deleted message in the controls for that row. Try this: First, undo the form (Me.Undo) BEFORE attempting to delete. Second, delete the record from the form's recordset, not directly from the table. If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then Me.Recordset.Delete End If End If -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi folks, I have a continuous subform on a main data entry form. Each entry on the subform contains two controls - a combo-box and a textbox beside it. The combobox allows the user to select a WebComType (Web Communication Type - eg work email, home email etc). The box beside it is for entering the actual email address etc. Once a user has inserted a record into the linked table (tblWebComs), I want the user to be able to delete the record by simply clearing the text box. For this purpose, I have inserted the following into the BeforeUpdate event for the combo-box: Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then CurrentDb.Execute "DELETE * FROM WebComs WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "", , dbFailOnError Else Cancel = True Me.Undo End If End If End Sub The subform is linked to my main form (Clients) using this query string auto-generated by Access: SELECT [ContactWebComs].[ContactID], [ContactWebComs].[WebComID] AS ContactWebComs_WebComID, [WebComs].[WebComID] AS WebComs_WebComID, [WebComs].[WebComType], [WebComs].[WebComDetails] FROM WebComs INNER JOIN ContactWebComs ON [WebComs].[WebComID]=[ContactWebComs].[WebComID]; The Child/Master link fields is ContactsID which appears in my contacts table (the record source for the main form) and my junction table (ContactWebComs). When I run the BeforeUpdate code, I experience two problems: (1) When I clear the combo-box and shift the focus (eg to the text box beside it), the message box pops up to confirm the delete as expected. But after that, nothing happens until I change focus again (eg tab to the next row). (2) Once I change focus a second time, a message pops up saying that the record has been deleted - but as soon as I press okay on this message box, Access just shuts down - no error messages or anything, the entire application just shuts down. After I restart the application and check the entries in tblWebComs, I can see that the record I selected was in fact deleted. I've tried a couple of variations for delete command. For instance, I've tried hard coding the WebComID to delete like so: CurrentDb.Execute "DELETE * FROM WebComs WHERE [WebComs].[WebComID]=20", dbFailOnError Again, the record is actually deleted, but I get exactly the same errors. I've also tried running the following code instead of the currentdb.execute line: strSQL = "DELETE * " & _ "FROM WebComs " & _ "WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "" DoCmd.SetWarnings False ' Turn off Access warning messages DoCmd.RunSQL strSQL DoCmd.SetWarnings True ' Warning set on again This results in exactly the same problems - and a correctly deleted record. If I run the above code without the SetWarnings lines, the following happens: (a) my custom message box to popup; (b) then Access's own message box warning that I am about to delete a record from the specified table pops up; (c) then nothing happens until I change focus to another part of the form; (d) then a box pops up saying that "record is deleted" (e) then all the rows in my subform disappear except for the blank "new record" line (f) a check of the table confirms that the record was correctly deleted. The difference here is that Access does not shut down, but I can't stop access from deleting all my rows. If I try to do incorporate a Me.Requery after the outside "If ... Then" statement, Access complains that the line is preventing it from saving anything to the database. If I try a "Me.txtWebComType.Requery" line instead, I get an error saying that "You must save the current field before you run a query action". If I insert a "Me.txtWebComType.Requery" line in the AfterUpdate event instead, I end up with the same problems described above. I all out of ideas. Does anyone else have any pointers as to what might be wrong with my code, or what might be causing these problems? I should mention that the DoCmd code above worked fine when was (incorrectly) deleting entries in my junction table (ContactWebComs) instead of my WebComs table. (Please note, I have cascade update and delete selected for all relationships). TIA Bob |
#4
|
|||
|
|||
Problems with Delete Command
Hi Bob
Instead of Me.Recordset.Delete try With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With The method using RecordsetClone should work with all versions of Access and not require a Requery. Opening a separate Recordset will still require a requery (as will a SQL delete) because the form's recordset is not being used. And no, you do not require the second Undo/Cancel. If txtWebComType has been cleared then the Undo/Cancel must happen in any case - either because the user does not want to delete the record, or because the lock on the record must be released so it can be deleted. That's why I moved it up before the If MsgBox... The reason the code does not work in AfterUpdate is that only the FIELD has been updated at that point, not the entire record, so the record is still in the process of being edited. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Hi Graham, I tried just cutting and pasting your code and it didn't work. I assumed that this was because I needed to create a recordset first. Anyways, I had another crack at this and the following code seems to work but any advice you might have on improving it would be appreciated (I assume the second set of Undo/Cancel commands are unnecessary): Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String Dim rstTemp As DAO.Recordset ' Using DAO strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" strSQL = "SELECT * FROM WebComs WHERE [WebComs].[WebComID] = " & Me.ContactWebComs_WebComID & "" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then Set rstTemp = CurrentDb.OpenRecordset(strSQL) ' Make sure the recordset is not empty If Not rstTemp.EOF Then rstTemp.Delete Me.Requery End If rstTemp.Close Set rstTemp = Nothing Else Me.Undo ' do this unconditionally Cancel = True End If End If End Sub I tried the code without the first Undo and Cancel but it didn't work. Amazingly, Access didn't actually shut down but as you predicted the row in subform showed "#Deleted" immediately after the code ran. If I leave the Requery line in without the first set of Undo/Cancel commands, Access complains. Hence, the only way I can get the form to reflect the current state of the database (ie Requery) is if I leave the first set of Undo/Cancel commands in place. Since the code appears in the BeforeUpdate event, logically the the field linked to txtWebComType has not yet been updated by the time the code runs. I can see (now - thanks to you) that if the row is actually deleted before the update event occurs then clearly by the time Access goes to update the field it will error out since the relevant row (and field) no longer exists. This being the case, I would have thought that the error could be avoid (without the first set of Undo/Cancel commands) if I simply move the code to the AfterUpdate event. But this still does not work. Can you explain why this is so? TIA Bob Graham Mandeno wrote: Hi Bob Hmmm... the "Access shutting down" bit is very nasty. This is clearly a bug, but I suspect it is being triggered by the fact that you are deleting a record which has been locked for editing. Also, you are deleting the record directly using SQL, so the form doesn't know it's going to happen. If Access did not shut down, then I would expect you to see the ugly #Deleted message in the controls for that row. Try this: First, undo the form (Me.Undo) BEFORE attempting to delete. Second, delete the record from the form's recordset, not directly from the table. If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then Me.Recordset.Delete End If End If -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi folks, I have a continuous subform on a main data entry form. Each entry on the subform contains two controls - a combo-box and a textbox beside it. The combobox allows the user to select a WebComType (Web Communication Type - eg work email, home email etc). The box beside it is for entering the actual email address etc. Once a user has inserted a record into the linked table (tblWebComs), I want the user to be able to delete the record by simply clearing the text box. For this purpose, I have inserted the following into the BeforeUpdate event for the combo-box: Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then CurrentDb.Execute "DELETE * FROM WebComs WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "", , dbFailOnError Else Cancel = True Me.Undo End If End If End Sub The subform is linked to my main form (Clients) using this query string auto-generated by Access: SELECT [ContactWebComs].[ContactID], [ContactWebComs].[WebComID] AS ContactWebComs_WebComID, [WebComs].[WebComID] AS WebComs_WebComID, [WebComs].[WebComType], [WebComs].[WebComDetails] FROM WebComs INNER JOIN ContactWebComs ON [WebComs].[WebComID]=[ContactWebComs].[WebComID]; The Child/Master link fields is ContactsID which appears in my contacts table (the record source for the main form) and my junction table (ContactWebComs). When I run the BeforeUpdate code, I experience two problems: (1) When I clear the combo-box and shift the focus (eg to the text box beside it), the message box pops up to confirm the delete as expected. But after that, nothing happens until I change focus again (eg tab to the next row). (2) Once I change focus a second time, a message pops up saying that the record has been deleted - but as soon as I press okay on this message box, Access just shuts down - no error messages or anything, the entire application just shuts down. After I restart the application and check the entries in tblWebComs, I can see that the record I selected was in fact deleted. I've tried a couple of variations for delete command. For instance, I've tried hard coding the WebComID to delete like so: CurrentDb.Execute "DELETE * FROM WebComs WHERE [WebComs].[WebComID]=20", dbFailOnError Again, the record is actually deleted, but I get exactly the same errors. I've also tried running the following code instead of the currentdb.execute line: strSQL = "DELETE * " & _ "FROM WebComs " & _ "WHERE [WebComs].[WebComID]=" & Me.ContactWebComs_WebComID & "" DoCmd.SetWarnings False ' Turn off Access warning messages DoCmd.RunSQL strSQL DoCmd.SetWarnings True ' Warning set on again This results in exactly the same problems - and a correctly deleted record. If I run the above code without the SetWarnings lines, the following happens: (a) my custom message box to popup; (b) then Access's own message box warning that I am about to delete a record from the specified table pops up; (c) then nothing happens until I change focus to another part of the form; (d) then a box pops up saying that "record is deleted" (e) then all the rows in my subform disappear except for the blank "new record" line (f) a check of the table confirms that the record was correctly deleted. The difference here is that Access does not shut down, but I can't stop access from deleting all my rows. If I try to do incorporate a Me.Requery after the outside "If ... Then" statement, Access complains that the line is preventing it from saving anything to the database. If I try a "Me.txtWebComType.Requery" line instead, I get an error saying that "You must save the current field before you run a query action". If I insert a "Me.txtWebComType.Requery" line in the AfterUpdate event instead, I end up with the same problems described above. I all out of ideas. Does anyone else have any pointers as to what might be wrong with my code, or what might be causing these problems? I should mention that the DoCmd code above worked fine when was (incorrectly) deleting entries in my junction table (ContactWebComs) instead of my WebComs table. (Please note, I have cascade update and delete selected for all relationships). TIA Bob |
#5
|
|||
|
|||
Problems with Delete Command
Hi Graham,
Just in case I've got part of this wrong, this is what I now have in my BeforeUpdate event: If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With End If End If This certainly works without have to requery the database after the delete. Unfortunately, the code is only deleting the entries in my junction table (ContactWebComs) - not the entries in my linked table (WebComs). Regards Bob Graham Mandeno wrote: Hi Bob Instead of Me.Recordset.Delete try With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With The method using RecordsetClone should work with all versions of Access and not require a Requery. Opening a separate Recordset will still require a requery (as will a SQL delete) because the form's recordset is not being used. And no, you do not require the second Undo/Cancel. If txtWebComType has been cleared then the Undo/Cancel must happen in any case - either because the user does not want to delete the record, or because the lock on the record must be released so it can be deleted. That's why I moved it up before the If MsgBox... The reason the code does not work in AfterUpdate is that only the FIELD has been updated at that point, not the entire record, so the record is still in the process of being edited. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... |
#6
|
|||
|
|||
Problems with Delete Command
But the fact that you have a junction table suggests a many-to-many
relationship. One contact has many WebComs and one WebCom can be shared by many contacts. Is this the case? If not, then you need to change your design. If one WebCom cannot be shared by many contacts then you don't need a junction table - you just need a ContactID field in tblWebComs and a ONE-to-many relationship. If it truly is many-to-many, then perhaps you want to have additional code that says, "If this guy is the last to leave then turn out the lights". In other words, if no further ContactWebComs records refer to this WebComs record, then delete it. -- Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Hi Graham, Just in case I've got part of this wrong, this is what I now have in my BeforeUpdate event: If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With End If End If This certainly works without have to requery the database after the delete. Unfortunately, the code is only deleting the entries in my junction table (ContactWebComs) - not the entries in my linked table (WebComs). Regards Bob Graham Mandeno wrote: Hi Bob Instead of Me.Recordset.Delete try With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With The method using RecordsetClone should work with all versions of Access and not require a Requery. Opening a separate Recordset will still require a requery (as will a SQL delete) because the form's recordset is not being used. And no, you do not require the second Undo/Cancel. If txtWebComType has been cleared then the Undo/Cancel must happen in any case - either because the user does not want to delete the record, or because the lock on the record must be released so it can be deleted. That's why I moved it up before the If MsgBox... The reason the code does not work in AfterUpdate is that only the FIELD has been updated at that point, not the entire record, so the record is still in the process of being edited. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... |
#7
|
|||
|
|||
Problems with Delete Command
Hi Graham,
Yes, there is meant to be a many to many relationship here. I am surprised that the recordsetclone property doesn't delete the entries in both tables at the same time since the form's recordsource contains an inner join. As for "lights out" code you mentioned, to be honest I hadn't even thought of that. Conceptually, then, I take it that the code has to follow the following procedu 1. Allow user to invoke delete (by clearing text box) 2. Get user confirmation to delete 3. Lookup the ContactWebComs table to check if current WebCom is linked to any other entries 4. If other entries exist, advise user - and delete the relevant junction table entries only (ie leave entry in WebComs alone) 5. Else, delete entries in both tables. Is this the way it's normally done? Thanks Bob "Graham Mandeno" wrote in message ... But the fact that you have a junction table suggests a many-to-many relationship. One contact has many WebComs and one WebCom can be shared by many contacts. Is this the case? If not, then you need to change your design. If one WebCom cannot be shared by many contacts then you don't need a junction table - you just need a ContactID field in tblWebComs and a ONE-to-many relationship. If it truly is many-to-many, then perhaps you want to have additional code that says, "If this guy is the last to leave then turn out the lights". In other words, if no further ContactWebComs records refer to this WebComs record, then delete it. -- Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Hi Graham, Just in case I've got part of this wrong, this is what I now have in my BeforeUpdate event: If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With End If End If This certainly works without have to requery the database after the delete. Unfortunately, the code is only deleting the entries in my junction table (ContactWebComs) - not the entries in my linked table (WebComs). Regards Bob Graham Mandeno wrote: Hi Bob Instead of Me.Recordset.Delete try With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With The method using RecordsetClone should work with all versions of Access and not require a Requery. Opening a separate Recordset will still require a requery (as will a SQL delete) because the form's recordset is not being used. And no, you do not require the second Undo/Cancel. If txtWebComType has been cleared then the Undo/Cancel must happen in any case - either because the user does not want to delete the record, or because the lock on the record must be released so it can be deleted. That's why I moved it up before the If MsgBox... The reason the code does not work in AfterUpdate is that only the FIELD has been updated at that point, not the entire record, so the record is still in the process of being edited. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... |
#8
|
|||
|
|||
Problems with Delete Command
Hi Bob
Yes - basically the user has four choices (you may not wish to offer all of them): 1. Do nothing and undo the change to the record. 2. Delete the junction record and leave the WebComs record intact 3. Delete the junction record and the WebComs record if it is no longer referenced. 4. Delete all related junction records AND the WebComs record Currently you are offering only 1 or 2. You can facilitate 4 by setting Cascade Deletes on the relation between WebComs and the junction table. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, Yes, there is meant to be a many to many relationship here. I am surprised that the recordsetclone property doesn't delete the entries in both tables at the same time since the form's recordsource contains an inner join. As for "lights out" code you mentioned, to be honest I hadn't even thought of that. Conceptually, then, I take it that the code has to follow the following procedu 1. Allow user to invoke delete (by clearing text box) 2. Get user confirmation to delete 3. Lookup the ContactWebComs table to check if current WebCom is linked to any other entries 4. If other entries exist, advise user - and delete the relevant junction table entries only (ie leave entry in WebComs alone) 5. Else, delete entries in both tables. Is this the way it's normally done? Thanks Bob "Graham Mandeno" wrote in message ... But the fact that you have a junction table suggests a many-to-many relationship. One contact has many WebComs and one WebCom can be shared by many contacts. Is this the case? If not, then you need to change your design. If one WebCom cannot be shared by many contacts then you don't need a junction table - you just need a ContactID field in tblWebComs and a ONE-to-many relationship. If it truly is many-to-many, then perhaps you want to have additional code that says, "If this guy is the last to leave then turn out the lights". In other words, if no further ContactWebComs records refer to this WebComs record, then delete it. -- Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Hi Graham, Just in case I've got part of this wrong, this is what I now have in my BeforeUpdate event: If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With End If End If This certainly works without have to requery the database after the delete. Unfortunately, the code is only deleting the entries in my junction table (ContactWebComs) - not the entries in my linked table (WebComs). Regards Bob Graham Mandeno wrote: Hi Bob Instead of Me.Recordset.Delete try With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With The method using RecordsetClone should work with all versions of Access and not require a Requery. Opening a separate Recordset will still require a requery (as will a SQL delete) because the form's recordset is not being used. And no, you do not require the second Undo/Cancel. If txtWebComType has been cleared then the Undo/Cancel must happen in any case - either because the user does not want to delete the record, or because the lock on the record must be released so it can be deleted. That's why I moved it up before the If MsgBox... The reason the code does not work in AfterUpdate is that only the FIELD has been updated at that point, not the entire record, so the record is still in the process of being edited. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... |
#9
|
|||
|
|||
Problems with Delete Command
Hi Graham,
I have cascade deletes set at the moment. But how do I get recordsetclone to point to one table rather than the other to facilitate switching between options? (ie to delete from WebComs and the junction table for option 3, but only from the junction table if the record in WebComs is required for other relationships as per option 2). Do I need to fiddle with the select query that I am currently using as the record source for the subform (see my first post) to enable recordsetclone to achieve this? Or is recordsetclone not able to be used where you have a many to many relationship? Regards Bob Hi Bob Yes - basically the user has four choices (you may not wish to offer all of them): 1. Do nothing and undo the change to the record. 2. Delete the junction record and leave the WebComs record intact 3. Delete the junction record and the WebComs record if it is no longer referenced. 4. Delete all related junction records AND the WebComs record Currently you are offering only 1 or 2. You can facilitate 4 by setting Cascade Deletes on the relation between WebComs and the junction table. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, Yes, there is meant to be a many to many relationship here. I am surprised that the recordsetclone property doesn't delete the entries in both tables at the same time since the form's recordsource contains an inner join. As for "lights out" code you mentioned, to be honest I hadn't even thought of that. Conceptually, then, I take it that the code has to follow the following procedu 1. Allow user to invoke delete (by clearing text box) 2. Get user confirmation to delete 3. Lookup the ContactWebComs table to check if current WebCom is linked to any other entries 4. If other entries exist, advise user - and delete the relevant junction table entries only (ie leave entry in WebComs alone) 5. Else, delete entries in both tables. Is this the way it's normally done? Thanks Bob "Graham Mandeno" wrote in message ... But the fact that you have a junction table suggests a many-to-many relationship. One contact has many WebComs and one WebCom can be shared by many contacts. Is this the case? If not, then you need to change your design. If one WebCom cannot be shared by many contacts then you don't need a junction table - you just need a ContactID field in tblWebComs and a ONE-to-many relationship. If it truly is many-to-many, then perhaps you want to have additional code that says, "If this guy is the last to leave then turn out the lights". In other words, if no further ContactWebComs records refer to this WebComs record, then delete it. -- Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Hi Graham, Just in case I've got part of this wrong, this is what I now have in my BeforeUpdate event: If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With End If End If This certainly works without have to requery the database after the delete. Unfortunately, the code is only deleting the entries in my junction table (ContactWebComs) - not the entries in my linked table (WebComs). Regards Bob Graham Mandeno wrote: Hi Bob Instead of Me.Recordset.Delete try With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With The method using RecordsetClone should work with all versions of Access and not require a Requery. Opening a separate Recordset will still require a requery (as will a SQL delete) because the form's recordset is not being used. And no, you do not require the second Undo/Cancel. If txtWebComType has been cleared then the Undo/Cancel must happen in any case - either because the user does not want to delete the record, or because the lock on the record must be released so it can be deleted. That's why I moved it up before the If MsgBox... The reason the code does not work in AfterUpdate is that only the FIELD has been updated at that point, not the entire record, so the record is still in the process of being edited. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... |
#10
|
|||
|
|||
Problems with Delete Command
Hi Bob
I see your misunderstanding. RecordsetClone should be used only to delete the junction record that is currently displayed in your subform, simply to avoid the #Deleted display. You could do a SQL delete followed by Me.Requery if you prefer (remembering the Me.Undo first, of course!) To delete the record in WebComs, do a SQL delete: CurrentDb.Execute "Delete * from tblWebComs where WebComID=" _ & lngID, dbFailOnError If you ALWAYS want to delete ALL the related junction table records, then forget the RecordsetClone.Delete and just execute the SQL delete followed by Me.Requery. (The cascade deletes will take care of the rest.) -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ps.com... Hi Graham, I have cascade deletes set at the moment. But how do I get recordsetclone to point to one table rather than the other to facilitate switching between options? (ie to delete from WebComs and the junction table for option 3, but only from the junction table if the record in WebComs is required for other relationships as per option 2). Do I need to fiddle with the select query that I am currently using as the record source for the subform (see my first post) to enable recordsetclone to achieve this? Or is recordsetclone not able to be used where you have a many to many relationship? Regards Bob Hi Bob Yes - basically the user has four choices (you may not wish to offer all of them): 1. Do nothing and undo the change to the record. 2. Delete the junction record and leave the WebComs record intact 3. Delete the junction record and the WebComs record if it is no longer referenced. 4. Delete all related junction records AND the WebComs record Currently you are offering only 1 or 2. You can facilitate 4 by setting Cascade Deletes on the relation between WebComs and the junction table. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ... Hi Graham, Yes, there is meant to be a many to many relationship here. I am surprised that the recordsetclone property doesn't delete the entries in both tables at the same time since the form's recordsource contains an inner join. As for "lights out" code you mentioned, to be honest I hadn't even thought of that. Conceptually, then, I take it that the code has to follow the following procedu 1. Allow user to invoke delete (by clearing text box) 2. Get user confirmation to delete 3. Lookup the ContactWebComs table to check if current WebCom is linked to any other entries 4. If other entries exist, advise user - and delete the relevant junction table entries only (ie leave entry in WebComs alone) 5. Else, delete entries in both tables. Is this the way it's normally done? Thanks Bob "Graham Mandeno" wrote in message ... But the fact that you have a junction table suggests a many-to-many relationship. One contact has many WebComs and one WebCom can be shared by many contacts. Is this the case? If not, then you need to change your design. If one WebCom cannot be shared by many contacts then you don't need a junction table - you just need a ContactID field in tblWebComs and a ONE-to-many relationship. If it truly is many-to-many, then perhaps you want to have additional code that says, "If this guy is the last to leave then turn out the lights". In other words, if no further ContactWebComs records refer to this WebComs record, then delete it. -- Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Hi Graham, Just in case I've got part of this wrong, this is what I now have in my BeforeUpdate event: If IsNull(Me.txtWebComType) Then Dim strMsg As String Dim strTitle As String Dim strSQL As String strMsg = "Would you like to delete this entry?" strTitle = "Delete WebCommunication Record" Me.Undo ' do this unconditionally Cancel = True If MsgBox(strMsg, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle) _ = vbYes Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With End If End If This certainly works without have to requery the database after the delete. Unfortunately, the code is only deleting the entries in my junction table (ContactWebComs) - not the entries in my linked table (WebComs). Regards Bob Graham Mandeno wrote: Hi Bob Instead of Me.Recordset.Delete try With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With The method using RecordsetClone should work with all versions of Access and not require a Requery. Opening a separate Recordset will still require a requery (as will a SQL delete) because the form's recordset is not being used. And no, you do not require the second Undo/Cancel. If txtWebComType has been cleared then the Undo/Cancel must happen in any case - either because the user does not want to delete the record, or because the lock on the record must be released so it can be deleted. That's why I moved it up before the If MsgBox... The reason the code does not work in AfterUpdate is that only the FIELD has been updated at that point, not the entire record, so the record is still in the process of being edited. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... |
|
Thread Tools | |
Display Modes | |
|
|