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
|
|||
|
|||
Delete a record from a form and its subform
Hello to everybody.
How may I cancel a record with a button and all the relative informations in the subform. Many thanks Regards John |
#2
|
|||
|
|||
You need to delete the information in the subform first. You should have
Referential Integrity enforced on the link between the tables for the main and sub forms, so it won't let you delete the record from the main form as long as there are associated records in the subform (unless you also set the link for Cascade Deletes). To do the delete using a button, run a delete query on the subform's table then delete the record from the main form. Example: strSQL = "Delete * From SubformTable Where ID=" & Me.txtID 'The Me.txtID above is a textbox that has the Master Link field in the main form 'and ID is the field in the subform's table that matches the Child Link field. CurrentDb.Execute strSQL, dbFailOnError strSQL = "Delete * From MainformTable Where ID=" & Me.txtId CurrentDb.Execute strSQL, dbFailOnError 'This is similar to the previous one, only now we are deleting from 'the table for the main form. In this case, instead of the query, you 'could also have used 'RunCommand acCmdDeleteRecord -- Wayne Morgan MS Access MVP "John B" wrote in message ... Hello to everybody. How may I cancel a record with a button and all the relative informations in the subform. Many thanks Regards John |
#3
|
|||
|
|||
Thanks for your help.
Regards John |
#4
|
|||
|
|||
I presume that you don't want to use referention integrity and then the
cascade deletion option? Well I (almost) never prefer it... You can trigger the "On Delete" event from the mainform. There you put the code to delete the records from the subform. e.g. s = "Delete * from subtable where field=" & me.id currentdb.execute s -- Kind Regards Damiaan info @t dampee d0t be "John B" wrote in message ... Thanks for your help. Regards John |
#5
|
|||
|
|||
Hi Wayne,
I took your example for my application because I happen to have the same need as John. But I guess I have made an error somewhere as an error message says "Data type mismatch in criteria expression". Could you please kindly tell why such error is occuring in my case? Many thanks! Private Sub cbDelete_Click() ' Delete relative record in qryReidAmendedProductDrawings from subform ' where the Me.FormerDWGNo is a textbox that has the Master Link field ' in the main form ' and (another) FormerDWGNo is the field in the subform's query that ' matches the Child Link field. strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo=" & Me.FormerDWGNo CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem line! ' Now that the relative record in subform is deleted, we now delete the record ' in the query from main form RunCommand acCmdDeleteRecord End Sub -- Regards, Sam "Wayne Morgan" wrote: You need to delete the information in the subform first. You should have Referential Integrity enforced on the link between the tables for the main and sub forms, so it won't let you delete the record from the main form as long as there are associated records in the subform (unless you also set the link for Cascade Deletes). To do the delete using a button, run a delete query on the subform's table then delete the record from the main form. Example: strSQL = "Delete * From SubformTable Where ID=" & Me.txtID 'The Me.txtID above is a textbox that has the Master Link field in the main form 'and ID is the field in the subform's table that matches the Child Link field. CurrentDb.Execute strSQL, dbFailOnError strSQL = "Delete * From MainformTable Where ID=" & Me.txtId CurrentDb.Execute strSQL, dbFailOnError 'This is similar to the previous one, only now we are deleting from 'the table for the main form. In this case, instead of the query, you 'could also have used 'RunCommand acCmdDeleteRecord -- Wayne Morgan MS Access MVP "John B" wrote in message ... Hello to everybody. How may I cancel a record with a button and all the relative informations in the subform. Many thanks Regards John |
#6
|
|||
|
|||
strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo="
& Me.FormerDWGNo What data type is the field FormerDWGNo? The way you have this written, the field should be a number data type. If the field is a string, the syntax would be: strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo='" & Me.FormerDWGNo & "'" If there is a possibility of apostrophes in the data (i.e. O'Hare), then this will need to be modified more. Also, if the value is actually a date or some other data type, modification may be needed. -- Wayne Morgan MS Access MVP "Sam Kuo" .(donotspam) wrote in message ... Hi Wayne, I took your example for my application because I happen to have the same need as John. But I guess I have made an error somewhere as an error message says "Data type mismatch in criteria expression". Could you please kindly tell why such error is occuring in my case? Many thanks! Private Sub cbDelete_Click() ' Delete relative record in qryReidAmendedProductDrawings from subform ' where the Me.FormerDWGNo is a textbox that has the Master Link field ' in the main form ' and (another) FormerDWGNo is the field in the subform's query that ' matches the Child Link field. strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo=" & Me.FormerDWGNo CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem line! ' Now that the relative record in subform is deleted, we now delete the record ' in the query from main form RunCommand acCmdDeleteRecord End Sub -- Regards, Sam "Wayne Morgan" wrote: You need to delete the information in the subform first. You should have Referential Integrity enforced on the link between the tables for the main and sub forms, so it won't let you delete the record from the main form as long as there are associated records in the subform (unless you also set the link for Cascade Deletes). To do the delete using a button, run a delete query on the subform's table then delete the record from the main form. Example: strSQL = "Delete * From SubformTable Where ID=" & Me.txtID 'The Me.txtID above is a textbox that has the Master Link field in the main form 'and ID is the field in the subform's table that matches the Child Link field. CurrentDb.Execute strSQL, dbFailOnError strSQL = "Delete * From MainformTable Where ID=" & Me.txtId CurrentDb.Execute strSQL, dbFailOnError 'This is similar to the previous one, only now we are deleting from 'the table for the main form. In this case, instead of the query, you 'could also have used 'RunCommand acCmdDeleteRecord -- Wayne Morgan MS Access MVP "John B" wrote in message ... Hello to everybody. How may I cancel a record with a button and all the relative informations in the subform. Many thanks Regards John |
#7
|
|||
|
|||
Thanks Wayne, your were right.
FormerDWGNo field is text (with no apostrophes), so I've now changed the syntax as you pointed out. But now the next line "RunCommand acCmdDeleteRecord" attracts another error which says "The command or action 'DeleteRecord' isn't available now". What does that mean? Private Sub cbDelete_Click() strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo='" & Me.FormerDWGNo & "'" CurrentDb.Execute strSQL, dbFailOnError ' Now that the relative record in subform is deleted, we now delete the record ' in the query from main form RunCommand acCmdDeleteRecord '-- this line now attracts another error! End Sub -- Regards, Sam |
#8
|
|||
|
|||
Using a command such as the RunCommand that you have will perform the action
on the item that has the focus. It may be that the form you're trying to delete a record from isn't the one with the focus. Depending on what record your delete query just deleted, it is also possible that the RunCommand is trying to delete the same record you just deleted, which can't be done. -- Wayne Morgan MS Access MVP "Sam Kuo" .(donotspam) wrote in message ... Thanks Wayne, your were right. FormerDWGNo field is text (with no apostrophes), so I've now changed the syntax as you pointed out. But now the next line "RunCommand acCmdDeleteRecord" attracts another error which says "The command or action 'DeleteRecord' isn't available now". What does that mean? Private Sub cbDelete_Click() strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo='" & Me.FormerDWGNo & "'" CurrentDb.Execute strSQL, dbFailOnError ' Now that the relative record in subform is deleted, we now delete the record ' in the query from main form RunCommand acCmdDeleteRecord '-- this line now attracts another error! End Sub |
#9
|
|||
|
|||
Thanks Wayne,
Instead of using the acCmdDeleteRecord, I tried using the alternative line you suggested as below and it works. However, the combo boxes that I use to filter records still contains the deleted record in its list. Can I also update the combo box lists after the record is deleted? The RowSource of some combo boxes is the same as the main form's RecordSource, but some ain't. Regards, Sam Private Sub cbDelete_Click() ' Delete record from the query for the subform strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo='" & Me.FormerDWGNo & "'" CurrentDb.Execute strSQL, dbFailOnError 'This is similar to the previous one, only now we are deleting from 'the query for the main form. strSQL = "Delete * From qryReidProductDrawings Where FormerDWGNo='" & Me.FormerDWGNo & "'" CurrentDb.Execute strSQL, dbFailOnError End Sub "Wayne Morgan" wrote: Using a command such as the RunCommand that you have will perform the action on the item that has the focus. It may be that the form you're trying to delete a record from isn't the one with the focus. Depending on what record your delete query just deleted, it is also possible that the RunCommand is trying to delete the same record you just deleted, which can't be done. -- Wayne Morgan MS Access MVP |
#10
|
|||
|
|||
Yes, after doing the delete you need to requery the combo boxes.
Me.cboMyCombo.Requery -- Wayne Morgan MS Access MVP "Sam Kuo" .(donotspam) wrote in message ... Thanks Wayne, Instead of using the acCmdDeleteRecord, I tried using the alternative line you suggested as below and it works. However, the combo boxes that I use to filter records still contains the deleted record in its list. Can I also update the combo box lists after the record is deleted? The RowSource of some combo boxes is the same as the main form's RecordSource, but some ain't. |
|
Thread Tools | |
Display Modes | |
|
|