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 |
#11
|
|||
|
|||
Problems with Delete Command
Thanks Graham.
Sorry for the delayed response. I wanted to see if I could cobble something together myself without asking for help everytime. The fruits of my labour appear below. Again, any suggestions for improvement would be appreciated. Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) 'Prevent user warnings DoCmd.SetWarnings False If IsNull(Me.txtWebComType) Then ' Dimension variables Dim strMsg_Del As String ' First delete confirmation Dim strTitle_Del As String ' Title for first delete confirmation Dim strMsg_Contacts As String ' Second delete confirmation (record relates to other contacts) Dim strTitle_Contacts As String ' Title for second delete confirmation Dim strSQL_Contacts As String ' SQL string to check if record relates to other Contacts Dim strSQL_ScrollBars As String ' SQL string to determine if vertical scrollbars are required Dim strSQL_DeleteMain As String ' SQL string to delete entry in main (not junction) table Dim strSQL_ContactNames As String ' SQL string to get names of related contacts Dim rstTemp As DAO.Recordset ' Recordset to store details of other Contacts related to existing record ' Initialise variables strMsg_Del = "Would you like to delete this entry?" strTitle_Del = "Delete WebCommunication Record" strMsg_Contacts = "This record relates to the other Contacts listed below. " & vbCrLf strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete the record for these Contacts too?" strTitle_Contacts = "Multi-Contact Record" strSQL_Contacts = "SELECT * FROM ContactWebComs " & _ "WHERE ([ContactWebComs].[WebComID]=" & Me.ContactWebComs_WebComID & ") " & _ "AND NOT ([ContactWebComs].[ContactID] = " & Me.ContactID & ")" strSQL_DeleteMain = "Delete * from WebComs where WebComID=" & Me.ContactWebComs_WebComID & "" strSQL_ContactNames = "SELECT Individuals.FirstName, Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN " ' Undo changes to existing row Me.Undo Cancel = True ' Make sure that the user is not simply trying to delete an empty row If IsNull(Me.ContactWebComs_WebComID) Then ' Row did not relate to an existing record - no need to access database ' Just remove the empty row on the form Me.AllowAdditions = False ' Row relates to an existing record Else ' Does user want to proceed with delete? If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle_Del) _ = vbYes Then ' Delete command confirmed - now assess the user's options ' First: Check whether entry relates to other Contacts in the Junction Table Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts) ' Make sure the recordset is not empty If Not rstTemp.EOF Then ' record relates to other Contacts Dim fld As Field Dim strFields As String Dim strContactIDS As String Dim strContactNames As String strContactIDS = "(" strContactNames = "" rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related ContactIDs For i = 1 To rstTemp.RecordCount strContactIDS = strContactIDS & rstTemp.Fields("ContactID").Value strContactIDS = strContactIDS & "," rstTemp.MoveNext Next i ' Remove last comma strContactIDS = Mid(strContactIDS, 1, (Len(strContactIDS) - 1)) strContactIDS = strContactIDS & ")" ' Clear existing recordset variable rstTemp.Close Set rstTemp = Nothing ' reset recordset variable Set rstTemp = CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS) rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related Contact Names For i = 1 To rstTemp.RecordCount strContactNames = strContactNames & rstTemp.Fields("FirstName").Value & " " strContactNames = strContactNames & rstTemp.Fields("LastName").Value & vbCrLf rstTemp.MoveNext Next i ' Query user: delete the underlying record or just the Junction Table entry? Select Case MsgBox(strMsg_Contacts & vbCrLf & vbCrLf & _ "Related Contacts: " & vbCrLf & strContactNames, _ vbQuestion + vbYesNoCancel + vbDefaultButton2, strTitle_Contacts) Case vbYes MsgBox ("Confirmed delete underlying table.") GoTo Delete_Main Case vbNo MsgBox ("Confirmed delete junction table entries only." & _ vbCrLf & vbCrLf & "strSQL_DeleteMain: " & strSQL_DeleteMain) GoTo Delete_Junction Case vbCancel Exit Sub Case Else Stop End Select Else ' If recordset is empty, record relates to this Contact only GoTo Delete_Main End If End If ' User has cancelled delete command - No need to for extra Undo and Cancel End If ' Close second If ... Then statement (IsNull(Me.ContactWebComs_WebComID)) End If ' Close first If ... Then statement (IsNull(Me.txtWebComType)) 'Reset original settings DoCmd.SetWarnings True Exit Sub Delete_Main: ' Delete entries in the primary table (cascade delete will delete junction table entries) CurrentDb.Execute strSQL_DeleteMain, dbFailOnError Me.Requery ' Make sure there is no empty "add new record" row Me.AllowAdditions = False Exit Sub Delete_Junction: ' WebCom relates to other Contacts, delete Junction Table entries only Set rstTemp2 = Me.RecordsetClone ' Make sure recordset is not empty If Not Me.RecordsetClone.EOF Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With ' Make sure there is no empty "add new record" row Me.AllowAdditions = False ' Clean Up rstTemp2.Close Set rstTemp2 = Nothing Exit Sub End If End Sub Regards Bob Graham Mandeno wrote: 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... |
#12
|
|||
|
|||
Problems with Delete Command
Hi Bob
Looks pretty good. You forgot to say if it's working or not :-) A few comments: 1. There is nothing to be gained here by disabling warnings. Why are you using SetWarnings? 2. You are finding the names related to the WebComID using two queries - one to find the list of ContactIDs from the junction table, and another to find the list if individuals matching those ContactIDs using an IN clause. It would be much easier and faster to run a single query: "Select FirstName & ' ' & LastName as ContactName " _ & "from Individuals inner join ContactWebComs " _ & "on Individuals.ContactID = ContactWebComs.ContactID " _ & "where WebComID=" & Me.ContactWebComs_WebComID _ & " and Individuals.ContactID" & Me.ContactID Note that the concatenation of first and last name is happening in the query also. 3. The usual way to traverse a recordset is not to use RecordCount as a count loop limit, but instead to loop until the EOF condition is met: Do Until rstTemp.EOF ' do something with record rstTemp.MoveNext Loop Then you don't need to do a MoveLast/MoveFirst to ascertain the RecordCount. 4. This is a style/readability thing: if you are going to use string variables to construct messages and SQL strings and suchlike, set their values just before you use them, otherwise someone trying to read the code has to scroll back and forth to remember what's in the string. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Thanks Graham. Sorry for the delayed response. I wanted to see if I could cobble something together myself without asking for help everytime. The fruits of my labour appear below. Again, any suggestions for improvement would be appreciated. Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) 'Prevent user warnings DoCmd.SetWarnings False If IsNull(Me.txtWebComType) Then ' Dimension variables Dim strMsg_Del As String ' First delete confirmation Dim strTitle_Del As String ' Title for first delete confirmation Dim strMsg_Contacts As String ' Second delete confirmation (record relates to other contacts) Dim strTitle_Contacts As String ' Title for second delete confirmation Dim strSQL_Contacts As String ' SQL string to check if record relates to other Contacts Dim strSQL_ScrollBars As String ' SQL string to determine if vertical scrollbars are required Dim strSQL_DeleteMain As String ' SQL string to delete entry in main (not junction) table Dim strSQL_ContactNames As String ' SQL string to get names of related contacts Dim rstTemp As DAO.Recordset ' Recordset to store details of other Contacts related to existing record ' Initialise variables strMsg_Del = "Would you like to delete this entry?" strTitle_Del = "Delete WebCommunication Record" strMsg_Contacts = "This record relates to the other Contacts listed below. " & vbCrLf strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete the record for these Contacts too?" strTitle_Contacts = "Multi-Contact Record" strSQL_Contacts = "SELECT * FROM ContactWebComs " & _ "WHERE ([ContactWebComs].[WebComID]=" & Me.ContactWebComs_WebComID & ") " & _ "AND NOT ([ContactWebComs].[ContactID] = " & Me.ContactID & ")" strSQL_DeleteMain = "Delete * from WebComs where WebComID=" & Me.ContactWebComs_WebComID & "" strSQL_ContactNames = "SELECT Individuals.FirstName, Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN " ' Undo changes to existing row Me.Undo Cancel = True ' Make sure that the user is not simply trying to delete an empty row If IsNull(Me.ContactWebComs_WebComID) Then ' Row did not relate to an existing record - no need to access database ' Just remove the empty row on the form Me.AllowAdditions = False ' Row relates to an existing record Else ' Does user want to proceed with delete? If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle_Del) _ = vbYes Then ' Delete command confirmed - now assess the user's options ' First: Check whether entry relates to other Contacts in the Junction Table Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts) ' Make sure the recordset is not empty If Not rstTemp.EOF Then ' record relates to other Contacts Dim fld As Field Dim strFields As String Dim strContactIDS As String Dim strContactNames As String strContactIDS = "(" strContactNames = "" rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related ContactIDs For i = 1 To rstTemp.RecordCount strContactIDS = strContactIDS & rstTemp.Fields("ContactID").Value strContactIDS = strContactIDS & "," rstTemp.MoveNext Next i ' Remove last comma strContactIDS = Mid(strContactIDS, 1, (Len(strContactIDS) - 1)) strContactIDS = strContactIDS & ")" ' Clear existing recordset variable rstTemp.Close Set rstTemp = Nothing ' reset recordset variable Set rstTemp = CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS) rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related Contact Names For i = 1 To rstTemp.RecordCount strContactNames = strContactNames & rstTemp.Fields("FirstName").Value & " " strContactNames = strContactNames & rstTemp.Fields("LastName").Value & vbCrLf rstTemp.MoveNext Next i ' Query user: delete the underlying record or just the Junction Table entry? Select Case MsgBox(strMsg_Contacts & vbCrLf & vbCrLf & _ "Related Contacts: " & vbCrLf & strContactNames, _ vbQuestion + vbYesNoCancel + vbDefaultButton2, strTitle_Contacts) Case vbYes MsgBox ("Confirmed delete underlying table.") GoTo Delete_Main Case vbNo MsgBox ("Confirmed delete junction table entries only." & _ vbCrLf & vbCrLf & "strSQL_DeleteMain: " & strSQL_DeleteMain) GoTo Delete_Junction Case vbCancel Exit Sub Case Else Stop End Select Else ' If recordset is empty, record relates to this Contact only GoTo Delete_Main End If End If ' User has cancelled delete command - No need to for extra Undo and Cancel End If ' Close second If ... Then statement (IsNull(Me.ContactWebComs_WebComID)) End If ' Close first If ... Then statement (IsNull(Me.txtWebComType)) 'Reset original settings DoCmd.SetWarnings True Exit Sub Delete_Main: ' Delete entries in the primary table (cascade delete will delete junction table entries) CurrentDb.Execute strSQL_DeleteMain, dbFailOnError Me.Requery ' Make sure there is no empty "add new record" row Me.AllowAdditions = False Exit Sub Delete_Junction: ' WebCom relates to other Contacts, delete Junction Table entries only Set rstTemp2 = Me.RecordsetClone ' Make sure recordset is not empty If Not Me.RecordsetClone.EOF Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With ' Make sure there is no empty "add new record" row Me.AllowAdditions = False ' Clean Up rstTemp2.Close Set rstTemp2 = Nothing Exit Sub End If End Sub Regards Bob Graham Mandeno wrote: 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... |
#13
|
|||
|
|||
Problems with Delete Command
Thanks alot Graham.
As a matter of fact it is working :-D I just wasn't sure if there was a better way to do some of things I was doing. From the looks of the your comments, there's obviously room for improvement. Thanks for the tips. Regards Bob Graham Mandeno wrote: Hi Bob Looks pretty good. You forgot to say if it's working or not :-) A few comments: 1. There is nothing to be gained here by disabling warnings. Why are you using SetWarnings? 2. You are finding the names related to the WebComID using two queries - one to find the list of ContactIDs from the junction table, and another to find the list if individuals matching those ContactIDs using an IN clause. It would be much easier and faster to run a single query: "Select FirstName & ' ' & LastName as ContactName " _ & "from Individuals inner join ContactWebComs " _ & "on Individuals.ContactID = ContactWebComs.ContactID " _ & "where WebComID=" & Me.ContactWebComs_WebComID _ & " and Individuals.ContactID" & Me.ContactID Note that the concatenation of first and last name is happening in the query also. 3. The usual way to traverse a recordset is not to use RecordCount as a count loop limit, but instead to loop until the EOF condition is met: Do Until rstTemp.EOF ' do something with record rstTemp.MoveNext Loop Then you don't need to do a MoveLast/MoveFirst to ascertain the RecordCount. 4. This is a style/readability thing: if you are going to use string variables to construct messages and SQL strings and suchlike, set their values just before you use them, otherwise someone trying to read the code has to scroll back and forth to remember what's in the string. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Thanks Graham. Sorry for the delayed response. I wanted to see if I could cobble something together myself without asking for help everytime. The fruits of my labour appear below. Again, any suggestions for improvement would be appreciated. Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) 'Prevent user warnings DoCmd.SetWarnings False If IsNull(Me.txtWebComType) Then ' Dimension variables Dim strMsg_Del As String ' First delete confirmation Dim strTitle_Del As String ' Title for first delete confirmation Dim strMsg_Contacts As String ' Second delete confirmation (record relates to other contacts) Dim strTitle_Contacts As String ' Title for second delete confirmation Dim strSQL_Contacts As String ' SQL string to check if record relates to other Contacts Dim strSQL_ScrollBars As String ' SQL string to determine if vertical scrollbars are required Dim strSQL_DeleteMain As String ' SQL string to delete entry in main (not junction) table Dim strSQL_ContactNames As String ' SQL string to get names of related contacts Dim rstTemp As DAO.Recordset ' Recordset to store details of other Contacts related to existing record ' Initialise variables strMsg_Del = "Would you like to delete this entry?" strTitle_Del = "Delete WebCommunication Record" strMsg_Contacts = "This record relates to the other Contacts listed below. " & vbCrLf strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete the record for these Contacts too?" strTitle_Contacts = "Multi-Contact Record" strSQL_Contacts = "SELECT * FROM ContactWebComs " & _ "WHERE ([ContactWebComs].[WebComID]=" & Me.ContactWebComs_WebComID & ") " & _ "AND NOT ([ContactWebComs].[ContactID] = " & Me.ContactID & ")" strSQL_DeleteMain = "Delete * from WebComs where WebComID=" & Me.ContactWebComs_WebComID & "" strSQL_ContactNames = "SELECT Individuals.FirstName, Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN " ' Undo changes to existing row Me.Undo Cancel = True ' Make sure that the user is not simply trying to delete an empty row If IsNull(Me.ContactWebComs_WebComID) Then ' Row did not relate to an existing record - no need to access database ' Just remove the empty row on the form Me.AllowAdditions = False ' Row relates to an existing record Else ' Does user want to proceed with delete? If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle_Del) _ = vbYes Then ' Delete command confirmed - now assess the user's options ' First: Check whether entry relates to other Contacts in the Junction Table Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts) ' Make sure the recordset is not empty If Not rstTemp.EOF Then ' record relates to other Contacts Dim fld As Field Dim strFields As String Dim strContactIDS As String Dim strContactNames As String strContactIDS = "(" strContactNames = "" rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related ContactIDs For i = 1 To rstTemp.RecordCount strContactIDS = strContactIDS & rstTemp.Fields("ContactID").Value strContactIDS = strContactIDS & "," rstTemp.MoveNext Next i ' Remove last comma strContactIDS = Mid(strContactIDS, 1, (Len(strContactIDS) - 1)) strContactIDS = strContactIDS & ")" ' Clear existing recordset variable rstTemp.Close Set rstTemp = Nothing ' reset recordset variable Set rstTemp = CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS) rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related Contact Names For i = 1 To rstTemp.RecordCount strContactNames = strContactNames & rstTemp.Fields("FirstName").Value & " " strContactNames = strContactNames & rstTemp.Fields("LastName").Value & vbCrLf rstTemp.MoveNext Next i ' Query user: delete the underlying record or just the Junction Table entry? Select Case MsgBox(strMsg_Contacts & vbCrLf & vbCrLf & _ "Related Contacts: " & vbCrLf & strContactNames, _ vbQuestion + vbYesNoCancel + vbDefaultButton2, strTitle_Contacts) Case vbYes MsgBox ("Confirmed delete underlying table.") GoTo Delete_Main Case vbNo MsgBox ("Confirmed delete junction table entries only." & _ vbCrLf & vbCrLf & "strSQL_DeleteMain: " & strSQL_DeleteMain) GoTo Delete_Junction Case vbCancel Exit Sub Case Else Stop End Select Else ' If recordset is empty, record relates to this Contact only GoTo Delete_Main End If End If ' User has cancelled delete command - No need to for extra Undo and Cancel End If ' Close second If ... Then statement (IsNull(Me.ContactWebComs_WebComID)) End If ' Close first If ... Then statement (IsNull(Me.txtWebComType)) 'Reset original settings DoCmd.SetWarnings True Exit Sub Delete_Main: ' Delete entries in the primary table (cascade delete will delete junction table entries) CurrentDb.Execute strSQL_DeleteMain, dbFailOnError Me.Requery ' Make sure there is no empty "add new record" row Me.AllowAdditions = False Exit Sub Delete_Junction: ' WebCom relates to other Contacts, delete Junction Table entries only Set rstTemp2 = Me.RecordsetClone ' Make sure recordset is not empty If Not Me.RecordsetClone.EOF Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With ' Make sure there is no empty "add new record" row Me.AllowAdditions = False ' Clean Up rstTemp2.Close Set rstTemp2 = Nothing Exit Sub End If End Sub Regards Bob Graham Mandeno wrote: 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... |
#14
|
|||
|
|||
Problems with Delete Command
Hi Graham,
Is it possible to modify your single query statement so that grabs the organisation name OR the firstname & lastname linked to the WebCom depending on the entity type? eg something along the lines of: "Select ((FirstName & ' ' & LastName as ContactName " _ & "from Individuals inner join ContactWebComs " _ & "on Individuals.ContactID = ContactWebComs.ContactID) " _ & "OR (Organisations.Name" _ & "from Organisations inner join ContactWebComs " _ & "on Organisations.ContactID = ContactWebComs.ContactID))" & "where WebComID=" & Me.ContactWebComs_WebComID _ & " and Individuals.ContactID" & Me.ContactID I've tried using the query designer in Access but I can't seem to construct a workable query. I haven't got a clue how to join all the tables together to enable one query. Should I perform two separate queries in this instance?: One to check for a matching contactid in the individuals table and then a separate query against the organisations table? Regards Bob Bob wrote: Thanks alot Graham. As a matter of fact it is working :-D I just wasn't sure if there was a better way to do some of things I was doing. From the looks of the your comments, there's obviously room for improvement. Thanks for the tips. Regards Bob Graham Mandeno wrote: Hi Bob Looks pretty good. You forgot to say if it's working or not :-) A few comments: 1. There is nothing to be gained here by disabling warnings. Why are you using SetWarnings? 2. You are finding the names related to the WebComID using two queries - one to find the list of ContactIDs from the junction table, and another to find the list if individuals matching those ContactIDs using an IN clause. It would be much easier and faster to run a single query: "Select FirstName & ' ' & LastName as ContactName " _ & "from Individuals inner join ContactWebComs " _ & "on Individuals.ContactID = ContactWebComs.ContactID " _ & "where WebComID=" & Me.ContactWebComs_WebComID _ & " and Individuals.ContactID" & Me.ContactID Note that the concatenation of first and last name is happening in the query also. 3. The usual way to traverse a recordset is not to use RecordCount as a count loop limit, but instead to loop until the EOF condition is met: Do Until rstTemp.EOF ' do something with record rstTemp.MoveNext Loop Then you don't need to do a MoveLast/MoveFirst to ascertain the RecordCount. 4. This is a style/readability thing: if you are going to use string variables to construct messages and SQL strings and suchlike, set their values just before you use them, otherwise someone trying to read the code has to scroll back and forth to remember what's in the string. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Thanks Graham. Sorry for the delayed response. I wanted to see if I could cobble something together myself without asking for help everytime. The fruits of my labour appear below. Again, any suggestions for improvement would be appreciated. Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) 'Prevent user warnings DoCmd.SetWarnings False If IsNull(Me.txtWebComType) Then ' Dimension variables Dim strMsg_Del As String ' First delete confirmation Dim strTitle_Del As String ' Title for first delete confirmation Dim strMsg_Contacts As String ' Second delete confirmation (record relates to other contacts) Dim strTitle_Contacts As String ' Title for second delete confirmation Dim strSQL_Contacts As String ' SQL string to check if record relates to other Contacts Dim strSQL_ScrollBars As String ' SQL string to determine if vertical scrollbars are required Dim strSQL_DeleteMain As String ' SQL string to delete entry in main (not junction) table Dim strSQL_ContactNames As String ' SQL string to get names of related contacts Dim rstTemp As DAO.Recordset ' Recordset to store details of other Contacts related to existing record ' Initialise variables strMsg_Del = "Would you like to delete this entry?" strTitle_Del = "Delete WebCommunication Record" strMsg_Contacts = "This record relates to the other Contacts listed below. " & vbCrLf strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete the record for these Contacts too?" strTitle_Contacts = "Multi-Contact Record" strSQL_Contacts = "SELECT * FROM ContactWebComs " & _ "WHERE ([ContactWebComs].[WebComID]=" & Me.ContactWebComs_WebComID & ") " & _ "AND NOT ([ContactWebComs].[ContactID] = " & Me.ContactID & ")" strSQL_DeleteMain = "Delete * from WebComs where WebComID=" & Me.ContactWebComs_WebComID & "" strSQL_ContactNames = "SELECT Individuals.FirstName, Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN " ' Undo changes to existing row Me.Undo Cancel = True ' Make sure that the user is not simply trying to delete an empty row If IsNull(Me.ContactWebComs_WebComID) Then ' Row did not relate to an existing record - no need to access database ' Just remove the empty row on the form Me.AllowAdditions = False ' Row relates to an existing record Else ' Does user want to proceed with delete? If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle_Del) _ = vbYes Then ' Delete command confirmed - now assess the user's options ' First: Check whether entry relates to other Contacts in the Junction Table Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts) ' Make sure the recordset is not empty If Not rstTemp.EOF Then ' record relates to other Contacts Dim fld As Field Dim strFields As String Dim strContactIDS As String Dim strContactNames As String strContactIDS = "(" strContactNames = "" rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related ContactIDs For i = 1 To rstTemp.RecordCount strContactIDS = strContactIDS & rstTemp.Fields("ContactID").Value strContactIDS = strContactIDS & "," rstTemp.MoveNext Next i ' Remove last comma strContactIDS = Mid(strContactIDS, 1, (Len(strContactIDS) - 1)) strContactIDS = strContactIDS & ")" ' Clear existing recordset variable rstTemp.Close Set rstTemp = Nothing ' reset recordset variable Set rstTemp = CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS) rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related Contact Names For i = 1 To rstTemp.RecordCount strContactNames = strContactNames & rstTemp.Fields("FirstName").Value & " " strContactNames = strContactNames & rstTemp.Fields("LastName").Value & vbCrLf rstTemp.MoveNext Next i ' Query user: delete the underlying record or just the Junction Table entry? Select Case MsgBox(strMsg_Contacts & vbCrLf & vbCrLf & _ "Related Contacts: " & vbCrLf & strContactNames, _ vbQuestion + vbYesNoCancel + vbDefaultButton2, strTitle_Contacts) Case vbYes MsgBox ("Confirmed delete underlying table.") GoTo Delete_Main Case vbNo MsgBox ("Confirmed delete junction table entries only." & _ vbCrLf & vbCrLf & "strSQL_DeleteMain: " & strSQL_DeleteMain) GoTo Delete_Junction Case vbCancel Exit Sub Case Else Stop End Select Else ' If recordset is empty, record relates to this Contact only GoTo Delete_Main End If End If ' User has cancelled delete command - No need to for extra Undo and Cancel End If ' Close second If ... Then statement (IsNull(Me.ContactWebComs_WebComID)) End If ' Close first If ... Then statement (IsNull(Me.txtWebComType)) 'Reset original settings DoCmd.SetWarnings True Exit Sub Delete_Main: ' Delete entries in the primary table (cascade delete will delete junction table entries) CurrentDb.Execute strSQL_DeleteMain, dbFailOnError Me.Requery ' Make sure there is no empty "add new record" row Me.AllowAdditions = False Exit Sub Delete_Junction: ' WebCom relates to other Contacts, delete Junction Table entries only Set rstTemp2 = Me.RecordsetClone ' Make sure recordset is not empty If Not Me.RecordsetClone.EOF Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With ' Make sure there is no empty "add new record" row Me.AllowAdditions = False ' Clean Up rstTemp2.Close Set rstTemp2 = Nothing Exit Sub End If End Sub Regards Bob Graham Mandeno wrote: 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... |
#15
|
|||
|
|||
Problems with Delete Command
Hi Bob
I'll give you a clue... it's called a "UNION query" :-) The SQL could get fairly messy if you need to construct it in code, so I would create and save a union query with three columns: ContactName, ContactID, and WebComID, and create your on-the-fly SQL on that saved query, not on the base tables. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ups.com... Hi Graham, Is it possible to modify your single query statement so that grabs the organisation name OR the firstname & lastname linked to the WebCom depending on the entity type? eg something along the lines of: "Select ((FirstName & ' ' & LastName as ContactName " _ & "from Individuals inner join ContactWebComs " _ & "on Individuals.ContactID = ContactWebComs.ContactID) " _ & "OR (Organisations.Name" _ & "from Organisations inner join ContactWebComs " _ & "on Organisations.ContactID = ContactWebComs.ContactID))" & "where WebComID=" & Me.ContactWebComs_WebComID _ & " and Individuals.ContactID" & Me.ContactID I've tried using the query designer in Access but I can't seem to construct a workable query. I haven't got a clue how to join all the tables together to enable one query. Should I perform two separate queries in this instance?: One to check for a matching contactid in the individuals table and then a separate query against the organisations table? Regards Bob Bob wrote: Thanks alot Graham. As a matter of fact it is working :-D I just wasn't sure if there was a better way to do some of things I was doing. From the looks of the your comments, there's obviously room for improvement. Thanks for the tips. Regards Bob Graham Mandeno wrote: Hi Bob Looks pretty good. You forgot to say if it's working or not :-) A few comments: 1. There is nothing to be gained here by disabling warnings. Why are you using SetWarnings? 2. You are finding the names related to the WebComID using two queries - one to find the list of ContactIDs from the junction table, and another to find the list if individuals matching those ContactIDs using an IN clause. It would be much easier and faster to run a single query: "Select FirstName & ' ' & LastName as ContactName " _ & "from Individuals inner join ContactWebComs " _ & "on Individuals.ContactID = ContactWebComs.ContactID " _ & "where WebComID=" & Me.ContactWebComs_WebComID _ & " and Individuals.ContactID" & Me.ContactID Note that the concatenation of first and last name is happening in the query also. 3. The usual way to traverse a recordset is not to use RecordCount as a count loop limit, but instead to loop until the EOF condition is met: Do Until rstTemp.EOF ' do something with record rstTemp.MoveNext Loop Then you don't need to do a MoveLast/MoveFirst to ascertain the RecordCount. 4. This is a style/readability thing: if you are going to use string variables to construct messages and SQL strings and suchlike, set their values just before you use them, otherwise someone trying to read the code has to scroll back and forth to remember what's in the string. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Thanks Graham. Sorry for the delayed response. I wanted to see if I could cobble something together myself without asking for help everytime. The fruits of my labour appear below. Again, any suggestions for improvement would be appreciated. Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) 'Prevent user warnings DoCmd.SetWarnings False If IsNull(Me.txtWebComType) Then ' Dimension variables Dim strMsg_Del As String ' First delete confirmation Dim strTitle_Del As String ' Title for first delete confirmation Dim strMsg_Contacts As String ' Second delete confirmation (record relates to other contacts) Dim strTitle_Contacts As String ' Title for second delete confirmation Dim strSQL_Contacts As String ' SQL string to check if record relates to other Contacts Dim strSQL_ScrollBars As String ' SQL string to determine if vertical scrollbars are required Dim strSQL_DeleteMain As String ' SQL string to delete entry in main (not junction) table Dim strSQL_ContactNames As String ' SQL string to get names of related contacts Dim rstTemp As DAO.Recordset ' Recordset to store details of other Contacts related to existing record ' Initialise variables strMsg_Del = "Would you like to delete this entry?" strTitle_Del = "Delete WebCommunication Record" strMsg_Contacts = "This record relates to the other Contacts listed below. " & vbCrLf strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete the record for these Contacts too?" strTitle_Contacts = "Multi-Contact Record" strSQL_Contacts = "SELECT * FROM ContactWebComs " & _ "WHERE ([ContactWebComs].[WebComID]=" & Me.ContactWebComs_WebComID & ") " & _ "AND NOT ([ContactWebComs].[ContactID] = " & Me.ContactID & ")" strSQL_DeleteMain = "Delete * from WebComs where WebComID=" & Me.ContactWebComs_WebComID & "" strSQL_ContactNames = "SELECT Individuals.FirstName, Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN " ' Undo changes to existing row Me.Undo Cancel = True ' Make sure that the user is not simply trying to delete an empty row If IsNull(Me.ContactWebComs_WebComID) Then ' Row did not relate to an existing record - no need to access database ' Just remove the empty row on the form Me.AllowAdditions = False ' Row relates to an existing record Else ' Does user want to proceed with delete? If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle_Del) _ = vbYes Then ' Delete command confirmed - now assess the user's options ' First: Check whether entry relates to other Contacts in the Junction Table Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts) ' Make sure the recordset is not empty If Not rstTemp.EOF Then ' record relates to other Contacts Dim fld As Field Dim strFields As String Dim strContactIDS As String Dim strContactNames As String strContactIDS = "(" strContactNames = "" rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related ContactIDs For i = 1 To rstTemp.RecordCount strContactIDS = strContactIDS & rstTemp.Fields("ContactID").Value strContactIDS = strContactIDS & "," rstTemp.MoveNext Next i ' Remove last comma strContactIDS = Mid(strContactIDS, 1, (Len(strContactIDS) - 1)) strContactIDS = strContactIDS & ")" ' Clear existing recordset variable rstTemp.Close Set rstTemp = Nothing ' reset recordset variable Set rstTemp = CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS) rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related Contact Names For i = 1 To rstTemp.RecordCount strContactNames = strContactNames & rstTemp.Fields("FirstName").Value & " " strContactNames = strContactNames & rstTemp.Fields("LastName").Value & vbCrLf rstTemp.MoveNext Next i ' Query user: delete the underlying record or just the Junction Table entry? Select Case MsgBox(strMsg_Contacts & vbCrLf & vbCrLf & _ "Related Contacts: " & vbCrLf & strContactNames, _ vbQuestion + vbYesNoCancel + vbDefaultButton2, strTitle_Contacts) Case vbYes MsgBox ("Confirmed delete underlying table.") GoTo Delete_Main Case vbNo MsgBox ("Confirmed delete junction table entries only." & _ vbCrLf & vbCrLf & "strSQL_DeleteMain: " & strSQL_DeleteMain) GoTo Delete_Junction Case vbCancel Exit Sub Case Else Stop End Select Else ' If recordset is empty, record relates to this Contact only GoTo Delete_Main End If End If ' User has cancelled delete command - No need to for extra Undo and Cancel End If ' Close second If ... Then statement (IsNull(Me.ContactWebComs_WebComID)) End If ' Close first If ... Then statement (IsNull(Me.txtWebComType)) 'Reset original settings DoCmd.SetWarnings True Exit Sub Delete_Main: ' Delete entries in the primary table (cascade delete will delete junction table entries) CurrentDb.Execute strSQL_DeleteMain, dbFailOnError Me.Requery ' Make sure there is no empty "add new record" row Me.AllowAdditions = False Exit Sub Delete_Junction: ' WebCom relates to other Contacts, delete Junction Table entries only Set rstTemp2 = Me.RecordsetClone ' Make sure recordset is not empty If Not Me.RecordsetClone.EOF Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With ' Make sure there is no empty "add new record" row Me.AllowAdditions = False ' Clean Up rstTemp2.Close Set rstTemp2 = Nothing Exit Sub End If End Sub Regards Bob Graham Mandeno wrote: 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... |
#16
|
|||
|
|||
Problems with Delete Command
Thank you o-wise-one :-D
I now have the following query saved as qryAllContactWebComs: Select FirstName & ' ' & LastName as ContactName,Individuals.ContactID as ContactID,WebComID,1 As ContactType from Individuals inner join ContactWebComs on Individuals.ContactID = ContactWebComs.ContactID UNION Select Name as ContactName,Organisations.ContactID as ContactID,WebComID,2 As ContactType from Organisations inner join ContactWebComs on Organisations.ContactID = ContactWebComs.ContactID ORDER BY ContactType, WebComID; The above is then searched programmatically using the following sql string: "Select ContactName" _ & " from qryAllContactWebComs " _ & " where qryAllContactWebComs.WebComID = " & Me.ContactWebComs_WebComID _ & " And qryAllContactWebComs.ContactID " & Me.ContactID _ & ";" Thanks again Graham. You've been a tremendous help. Cheers Bob Graham Mandeno wrote: Hi Bob I'll give you a clue... it's called a "UNION query" :-) The SQL could get fairly messy if you need to construct it in code, so I would create and save a union query with three columns: ContactName, ContactID, and WebComID, and create your on-the-fly SQL on that saved query, not on the base tables. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ups.com... Hi Graham, Is it possible to modify your single query statement so that grabs the organisation name OR the firstname & lastname linked to the WebCom depending on the entity type? eg something along the lines of: "Select ((FirstName & ' ' & LastName as ContactName " _ & "from Individuals inner join ContactWebComs " _ & "on Individuals.ContactID = ContactWebComs.ContactID) " _ & "OR (Organisations.Name" _ & "from Organisations inner join ContactWebComs " _ & "on Organisations.ContactID = ContactWebComs.ContactID))" & "where WebComID=" & Me.ContactWebComs_WebComID _ & " and Individuals.ContactID" & Me.ContactID I've tried using the query designer in Access but I can't seem to construct a workable query. I haven't got a clue how to join all the tables together to enable one query. Should I perform two separate queries in this instance?: One to check for a matching contactid in the individuals table and then a separate query against the organisations table? Regards Bob Bob wrote: Thanks alot Graham. As a matter of fact it is working :-D I just wasn't sure if there was a better way to do some of things I was doing. From the looks of the your comments, there's obviously room for improvement. Thanks for the tips. Regards Bob Graham Mandeno wrote: Hi Bob Looks pretty good. You forgot to say if it's working or not :-) A few comments: 1. There is nothing to be gained here by disabling warnings. Why are you using SetWarnings? 2. You are finding the names related to the WebComID using two queries - one to find the list of ContactIDs from the junction table, and another to find the list if individuals matching those ContactIDs using an IN clause. It would be much easier and faster to run a single query: "Select FirstName & ' ' & LastName as ContactName " _ & "from Individuals inner join ContactWebComs " _ & "on Individuals.ContactID = ContactWebComs.ContactID " _ & "where WebComID=" & Me.ContactWebComs_WebComID _ & " and Individuals.ContactID" & Me.ContactID Note that the concatenation of first and last name is happening in the query also. 3. The usual way to traverse a recordset is not to use RecordCount as a count loop limit, but instead to loop until the EOF condition is met: Do Until rstTemp.EOF ' do something with record rstTemp.MoveNext Loop Then you don't need to do a MoveLast/MoveFirst to ascertain the RecordCount. 4. This is a style/readability thing: if you are going to use string variables to construct messages and SQL strings and suchlike, set their values just before you use them, otherwise someone trying to read the code has to scroll back and forth to remember what's in the string. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Thanks Graham. Sorry for the delayed response. I wanted to see if I could cobble something together myself without asking for help everytime. The fruits of my labour appear below. Again, any suggestions for improvement would be appreciated. Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) 'Prevent user warnings DoCmd.SetWarnings False If IsNull(Me.txtWebComType) Then ' Dimension variables Dim strMsg_Del As String ' First delete confirmation Dim strTitle_Del As String ' Title for first delete confirmation Dim strMsg_Contacts As String ' Second delete confirmation (record relates to other contacts) Dim strTitle_Contacts As String ' Title for second delete confirmation Dim strSQL_Contacts As String ' SQL string to check if record relates to other Contacts Dim strSQL_ScrollBars As String ' SQL string to determine if vertical scrollbars are required Dim strSQL_DeleteMain As String ' SQL string to delete entry in main (not junction) table Dim strSQL_ContactNames As String ' SQL string to get names of related contacts Dim rstTemp As DAO.Recordset ' Recordset to store details of other Contacts related to existing record ' Initialise variables strMsg_Del = "Would you like to delete this entry?" strTitle_Del = "Delete WebCommunication Record" strMsg_Contacts = "This record relates to the other Contacts listed below. " & vbCrLf strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete the record for these Contacts too?" strTitle_Contacts = "Multi-Contact Record" strSQL_Contacts = "SELECT * FROM ContactWebComs " & _ "WHERE ([ContactWebComs].[WebComID]=" & Me.ContactWebComs_WebComID & ") " & _ "AND NOT ([ContactWebComs].[ContactID] = " & Me.ContactID & ")" strSQL_DeleteMain = "Delete * from WebComs where WebComID=" & Me.ContactWebComs_WebComID & "" strSQL_ContactNames = "SELECT Individuals.FirstName, Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN " ' Undo changes to existing row Me.Undo Cancel = True ' Make sure that the user is not simply trying to delete an empty row If IsNull(Me.ContactWebComs_WebComID) Then ' Row did not relate to an existing record - no need to access database ' Just remove the empty row on the form Me.AllowAdditions = False ' Row relates to an existing record Else ' Does user want to proceed with delete? If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle_Del) _ = vbYes Then ' Delete command confirmed - now assess the user's options ' First: Check whether entry relates to other Contacts in the Junction Table Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts) ' Make sure the recordset is not empty If Not rstTemp.EOF Then ' record relates to other Contacts Dim fld As Field Dim strFields As String Dim strContactIDS As String Dim strContactNames As String strContactIDS = "(" strContactNames = "" rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related ContactIDs For i = 1 To rstTemp.RecordCount strContactIDS = strContactIDS & rstTemp.Fields("ContactID").Value strContactIDS = strContactIDS & "," rstTemp.MoveNext Next i ' Remove last comma strContactIDS = Mid(strContactIDS, 1, (Len(strContactIDS) - 1)) strContactIDS = strContactIDS & ")" ' Clear existing recordset variable rstTemp.Close Set rstTemp = Nothing ' reset recordset variable Set rstTemp = CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS) rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related Contact Names For i = 1 To rstTemp.RecordCount strContactNames = strContactNames & rstTemp.Fields("FirstName").Value & " " strContactNames = strContactNames & rstTemp.Fields("LastName").Value & vbCrLf rstTemp.MoveNext Next i ' Query user: delete the underlying record or just the Junction Table entry? Select Case MsgBox(strMsg_Contacts & vbCrLf & vbCrLf & _ "Related Contacts: " & vbCrLf & strContactNames, _ vbQuestion + vbYesNoCancel + vbDefaultButton2, strTitle_Contacts) Case vbYes MsgBox ("Confirmed delete underlying table.") GoTo Delete_Main Case vbNo MsgBox ("Confirmed delete junction table entries only." & _ vbCrLf & vbCrLf & "strSQL_DeleteMain: " & strSQL_DeleteMain) GoTo Delete_Junction Case vbCancel Exit Sub Case Else Stop End Select Else ' If recordset is empty, record relates to this Contact only GoTo Delete_Main End If End If ' User has cancelled delete command - No need to for extra Undo and Cancel End If ' Close second If ... Then statement (IsNull(Me.ContactWebComs_WebComID)) End If ' Close first If ... Then statement (IsNull(Me.txtWebComType)) 'Reset original settings DoCmd.SetWarnings True Exit Sub Delete_Main: ' Delete entries in the primary table (cascade delete will delete junction table entries) CurrentDb.Execute strSQL_DeleteMain, dbFailOnError Me.Requery ' Make sure there is no empty "add new record" row Me.AllowAdditions = False Exit Sub Delete_Junction: ' WebCom relates to other Contacts, delete Junction Table entries only Set rstTemp2 = Me.RecordsetClone ' Make sure recordset is not empty If Not Me.RecordsetClone.EOF Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With ' Make sure there is no empty "add new record" row Me.AllowAdditions = False ' Clean Up rstTemp2.Close Set rstTemp2 = Nothing Exit Sub End If End Sub Regards Bob Graham Mandeno wrote: 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... |
#17
|
|||
|
|||
Problems with Delete Command
Good Man! Knew you could do it :-D
-- Cheers, Graham "Bob" wrote in message ups.com... Thank you o-wise-one :-D I now have the following query saved as qryAllContactWebComs: Select FirstName & ' ' & LastName as ContactName,Individuals.ContactID as ContactID,WebComID,1 As ContactType from Individuals inner join ContactWebComs on Individuals.ContactID = ContactWebComs.ContactID UNION Select Name as ContactName,Organisations.ContactID as ContactID,WebComID,2 As ContactType from Organisations inner join ContactWebComs on Organisations.ContactID = ContactWebComs.ContactID ORDER BY ContactType, WebComID; The above is then searched programmatically using the following sql string: "Select ContactName" _ & " from qryAllContactWebComs " _ & " where qryAllContactWebComs.WebComID = " & Me.ContactWebComs_WebComID _ & " And qryAllContactWebComs.ContactID " & Me.ContactID _ & ";" Thanks again Graham. You've been a tremendous help. Cheers Bob Graham Mandeno wrote: Hi Bob I'll give you a clue... it's called a "UNION query" :-) The SQL could get fairly messy if you need to construct it in code, so I would create and save a union query with three columns: ContactName, ContactID, and WebComID, and create your on-the-fly SQL on that saved query, not on the base tables. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message ups.com... Hi Graham, Is it possible to modify your single query statement so that grabs the organisation name OR the firstname & lastname linked to the WebCom depending on the entity type? eg something along the lines of: "Select ((FirstName & ' ' & LastName as ContactName " _ & "from Individuals inner join ContactWebComs " _ & "on Individuals.ContactID = ContactWebComs.ContactID) " _ & "OR (Organisations.Name" _ & "from Organisations inner join ContactWebComs " _ & "on Organisations.ContactID = ContactWebComs.ContactID))" & "where WebComID=" & Me.ContactWebComs_WebComID _ & " and Individuals.ContactID" & Me.ContactID I've tried using the query designer in Access but I can't seem to construct a workable query. I haven't got a clue how to join all the tables together to enable one query. Should I perform two separate queries in this instance?: One to check for a matching contactid in the individuals table and then a separate query against the organisations table? Regards Bob Bob wrote: Thanks alot Graham. As a matter of fact it is working :-D I just wasn't sure if there was a better way to do some of things I was doing. From the looks of the your comments, there's obviously room for improvement. Thanks for the tips. Regards Bob Graham Mandeno wrote: Hi Bob Looks pretty good. You forgot to say if it's working or not :-) A few comments: 1. There is nothing to be gained here by disabling warnings. Why are you using SetWarnings? 2. You are finding the names related to the WebComID using two queries - one to find the list of ContactIDs from the junction table, and another to find the list if individuals matching those ContactIDs using an IN clause. It would be much easier and faster to run a single query: "Select FirstName & ' ' & LastName as ContactName " _ & "from Individuals inner join ContactWebComs " _ & "on Individuals.ContactID = ContactWebComs.ContactID " _ & "where WebComID=" & Me.ContactWebComs_WebComID _ & " and Individuals.ContactID" & Me.ContactID Note that the concatenation of first and last name is happening in the query also. 3. The usual way to traverse a recordset is not to use RecordCount as a count loop limit, but instead to loop until the EOF condition is met: Do Until rstTemp.EOF ' do something with record rstTemp.MoveNext Loop Then you don't need to do a MoveLast/MoveFirst to ascertain the RecordCount. 4. This is a style/readability thing: if you are going to use string variables to construct messages and SQL strings and suchlike, set their values just before you use them, otherwise someone trying to read the code has to scroll back and forth to remember what's in the string. -- Good Luck! Graham Mandeno [Access MVP] Auckland, New Zealand "Bob" wrote in message oups.com... Thanks Graham. Sorry for the delayed response. I wanted to see if I could cobble something together myself without asking for help everytime. The fruits of my labour appear below. Again, any suggestions for improvement would be appreciated. Private Sub txtWebComType_BeforeUpdate(Cancel As Integer) 'Prevent user warnings DoCmd.SetWarnings False If IsNull(Me.txtWebComType) Then ' Dimension variables Dim strMsg_Del As String ' First delete confirmation Dim strTitle_Del As String ' Title for first delete confirmation Dim strMsg_Contacts As String ' Second delete confirmation (record relates to other contacts) Dim strTitle_Contacts As String ' Title for second delete confirmation Dim strSQL_Contacts As String ' SQL string to check if record relates to other Contacts Dim strSQL_ScrollBars As String ' SQL string to determine if vertical scrollbars are required Dim strSQL_DeleteMain As String ' SQL string to delete entry in main (not junction) table Dim strSQL_ContactNames As String ' SQL string to get names of related contacts Dim rstTemp As DAO.Recordset ' Recordset to store details of other Contacts related to existing record ' Initialise variables strMsg_Del = "Would you like to delete this entry?" strTitle_Del = "Delete WebCommunication Record" strMsg_Contacts = "This record relates to the other Contacts listed below. " & vbCrLf strMsg_Contacts = strMsg_Contacts & "Do you want to want to delete the record for these Contacts too?" strTitle_Contacts = "Multi-Contact Record" strSQL_Contacts = "SELECT * FROM ContactWebComs " & _ "WHERE ([ContactWebComs].[WebComID]=" & Me.ContactWebComs_WebComID & ") " & _ "AND NOT ([ContactWebComs].[ContactID] = " & Me.ContactID & ")" strSQL_DeleteMain = "Delete * from WebComs where WebComID=" & Me.ContactWebComs_WebComID & "" strSQL_ContactNames = "SELECT Individuals.FirstName, Individuals.LastName FROM Individuals WHERE Individuals.ContactID IN " ' Undo changes to existing row Me.Undo Cancel = True ' Make sure that the user is not simply trying to delete an empty row If IsNull(Me.ContactWebComs_WebComID) Then ' Row did not relate to an existing record - no need to access database ' Just remove the empty row on the form Me.AllowAdditions = False ' Row relates to an existing record Else ' Does user want to proceed with delete? If MsgBox(strMsg_Del, vbQuestion Or vbYesNo Or vbDefaultButton2, strTitle_Del) _ = vbYes Then ' Delete command confirmed - now assess the user's options ' First: Check whether entry relates to other Contacts in the Junction Table Set rstTemp = CurrentDb.OpenRecordset(strSQL_Contacts) ' Make sure the recordset is not empty If Not rstTemp.EOF Then ' record relates to other Contacts Dim fld As Field Dim strFields As String Dim strContactIDS As String Dim strContactNames As String strContactIDS = "(" strContactNames = "" rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related ContactIDs For i = 1 To rstTemp.RecordCount strContactIDS = strContactIDS & rstTemp.Fields("ContactID").Value strContactIDS = strContactIDS & "," rstTemp.MoveNext Next i ' Remove last comma strContactIDS = Mid(strContactIDS, 1, (Len(strContactIDS) - 1)) strContactIDS = strContactIDS & ")" ' Clear existing recordset variable rstTemp.Close Set rstTemp = Nothing ' reset recordset variable Set rstTemp = CurrentDb.OpenRecordset(strSQL_ContactNames & strContactIDS) rstTemp.MoveLast rstTemp.MoveFirst ' Enumerate related Contact Names For i = 1 To rstTemp.RecordCount strContactNames = strContactNames & rstTemp.Fields("FirstName").Value & " " strContactNames = strContactNames & rstTemp.Fields("LastName").Value & vbCrLf rstTemp.MoveNext Next i ' Query user: delete the underlying record or just the Junction Table entry? Select Case MsgBox(strMsg_Contacts & vbCrLf & vbCrLf & _ "Related Contacts: " & vbCrLf & strContactNames, _ vbQuestion + vbYesNoCancel + vbDefaultButton2, strTitle_Contacts) Case vbYes MsgBox ("Confirmed delete underlying table.") GoTo Delete_Main Case vbNo MsgBox ("Confirmed delete junction table entries only." & _ vbCrLf & vbCrLf & "strSQL_DeleteMain: " & strSQL_DeleteMain) GoTo Delete_Junction Case vbCancel Exit Sub Case Else Stop End Select Else ' If recordset is empty, record relates to this Contact only GoTo Delete_Main End If End If ' User has cancelled delete command - No need to for extra Undo and Cancel End If ' Close second If ... Then statement (IsNull(Me.ContactWebComs_WebComID)) End If ' Close first If ... Then statement (IsNull(Me.txtWebComType)) 'Reset original settings DoCmd.SetWarnings True Exit Sub Delete_Main: ' Delete entries in the primary table (cascade delete will delete junction table entries) CurrentDb.Execute strSQL_DeleteMain, dbFailOnError Me.Requery ' Make sure there is no empty "add new record" row Me.AllowAdditions = False Exit Sub Delete_Junction: ' WebCom relates to other Contacts, delete Junction Table entries only Set rstTemp2 = Me.RecordsetClone ' Make sure recordset is not empty If Not Me.RecordsetClone.EOF Then With Me.RecordsetClone .Bookmark = Me.Bookmark .Delete End With ' Make sure there is no empty "add new record" row Me.AllowAdditions = False ' Clean Up rstTemp2.Close Set rstTemp2 = Nothing Exit Sub End If End Sub Regards Bob Graham Mandeno wrote: 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 | |
|
|