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
|
|||
|
|||
Wayne,
I used your code example, but when it gets to the line CurrentDb.Execute strSQL, dbFailOnError I get an error that states "Run-tim error '3061': Too few parameters. Expected1. Do you know what this is? Thanks! "Wayne Morgan" wrote: 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 |
#12
|
|||
|
|||
What's the value of strSQL when it fails?
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... Wayne, I used your code example, but when it gets to the line CurrentDb.Execute strSQL, dbFailOnError I get an error that states "Run-tim error '3061': Too few parameters. Expected1. Do you know what this is? Thanks! "Wayne Morgan" wrote: 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 |
#13
|
|||
|
|||
strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062"
20362 is the current purchase order that is being created that I want to cancel. Any ideas? Thanks! "Douglas J. Steele" wrote: What's the value of strSQL when it fails? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... Wayne, I used your code example, but when it gets to the line CurrentDb.Execute strSQL, dbFailOnError I get an error that states "Run-tim error '3061': Too few parameters. Expected1. Do you know what this is? Thanks! "Wayne Morgan" wrote: 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 |
#14
|
|||
|
|||
It's unusual to be deleting from a query: why not delete from the table?
What happens when you try to run qryOrderDetailsSource: are you prompted for a parameter? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062" 20362 is the current purchase order that is being created that I want to cancel. Any ideas? Thanks! "Douglas J. Steele" wrote: What's the value of strSQL when it fails? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... Wayne, I used your code example, but when it gets to the line CurrentDb.Execute strSQL, dbFailOnError I get an error that states "Run-tim error '3061': Too few parameters. Expected1. Do you know what this is? Thanks! "Wayne Morgan" wrote: 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 |
#15
|
|||
|
|||
I have also tried deleting straight from the table. I tried the query
because the other person that was using this had a query. When I tryo to delete from the tabe, I have the same error message. When I run the query, it brings up all Purchase Orders that have ever been created and I don't get prompted for anything. Ideas? Thanks! "Douglas J. Steele" wrote: It's unusual to be deleting from a query: why not delete from the table? What happens when you try to run qryOrderDetailsSource: are you prompted for a parameter? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062" 20362 is the current purchase order that is being created that I want to cancel. Any ideas? Thanks! "Douglas J. Steele" wrote: What's the value of strSQL when it fails? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... Wayne, I used your code example, but when it gets to the line CurrentDb.Execute strSQL, dbFailOnError I get an error that states "Run-tim error '3061': Too few parameters. Expected1. Do you know what this is? Thanks! "Wayne Morgan" wrote: 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 |
#16
|
|||
|
|||
Is PurchaseOrderId a text field?
If so, you'll need strSQL to contain Delete * From qryOrderDetailsSource Where PurchaseOrderID='23062' as Wayne indicated some time ago. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... I have also tried deleting straight from the table. I tried the query because the other person that was using this had a query. When I tryo to delete from the tabe, I have the same error message. When I run the query, it brings up all Purchase Orders that have ever been created and I don't get prompted for anything. Ideas? Thanks! "Douglas J. Steele" wrote: It's unusual to be deleting from a query: why not delete from the table? What happens when you try to run qryOrderDetailsSource: are you prompted for a parameter? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062" 20362 is the current purchase order that is being created that I want to cancel. Any ideas? Thanks! "Douglas J. Steele" wrote: What's the value of strSQL when it fails? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... Wayne, I used your code example, but when it gets to the line CurrentDb.Execute strSQL, dbFailOnError I get an error that states "Run-tim error '3061': Too few parameters. Expected1. Do you know what this is? Thanks! "Wayne Morgan" wrote: 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 |
#17
|
|||
|
|||
PurchaseOrderID is a number field.
"Douglas J Steele" wrote: Is PurchaseOrderId a text field? If so, you'll need strSQL to contain Delete * From qryOrderDetailsSource Where PurchaseOrderID='23062' as Wayne indicated some time ago. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... I have also tried deleting straight from the table. I tried the query because the other person that was using this had a query. When I tryo to delete from the tabe, I have the same error message. When I run the query, it brings up all Purchase Orders that have ever been created and I don't get prompted for anything. Ideas? Thanks! "Douglas J. Steele" wrote: It's unusual to be deleting from a query: why not delete from the table? What happens when you try to run qryOrderDetailsSource: are you prompted for a parameter? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062" 20362 is the current purchase order that is being created that I want to cancel. Any ideas? Thanks! "Douglas J. Steele" wrote: What's the value of strSQL when it fails? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... Wayne, I used your code example, but when it gets to the line CurrentDb.Execute strSQL, dbFailOnError I get an error that states "Run-tim error '3061': Too few parameters. Expected1. Do you know what this is? Thanks! "Wayne Morgan" wrote: 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 |
#18
|
|||
|
|||
The only thing I can thing of is that your query is too complex to allow it
to be used in a delete statement (although you say you can't delete directly from the table either). Sorry. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... PurchaseOrderID is a number field. "Douglas J Steele" wrote: Is PurchaseOrderId a text field? If so, you'll need strSQL to contain Delete * From qryOrderDetailsSource Where PurchaseOrderID='23062' as Wayne indicated some time ago. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... I have also tried deleting straight from the table. I tried the query because the other person that was using this had a query. When I tryo to delete from the tabe, I have the same error message. When I run the query, it brings up all Purchase Orders that have ever been created and I don't get prompted for anything. Ideas? Thanks! "Douglas J. Steele" wrote: It's unusual to be deleting from a query: why not delete from the table? What happens when you try to run qryOrderDetailsSource: are you prompted for a parameter? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062" 20362 is the current purchase order that is being created that I want to cancel. Any ideas? Thanks! "Douglas J. Steele" wrote: What's the value of strSQL when it fails? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... Wayne, I used your code example, but when it gets to the line CurrentDb.Execute strSQL, dbFailOnError I get an error that states "Run-tim error '3061': Too few parameters. Expected1. Do you know what this is? Thanks! "Wayne Morgan" wrote: 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 |
#19
|
|||
|
|||
Thank you so much for looking into this and helping me out. I think I'll
just scrap the cancel button. It's not a huge deal, just a convience for the user. The query I am using is the one Access automatically creates when you base a form on a table. I just actually saved it to the query section in Access for testing this out. I never created the query myself. That could also be the problem. Thanks again! "Douglas J. Steele" wrote: The only thing I can thing of is that your query is too complex to allow it to be used in a delete statement (although you say you can't delete directly from the table either). Sorry. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... PurchaseOrderID is a number field. "Douglas J Steele" wrote: Is PurchaseOrderId a text field? If so, you'll need strSQL to contain Delete * From qryOrderDetailsSource Where PurchaseOrderID='23062' as Wayne indicated some time ago. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... I have also tried deleting straight from the table. I tried the query because the other person that was using this had a query. When I tryo to delete from the tabe, I have the same error message. When I run the query, it brings up all Purchase Orders that have ever been created and I don't get prompted for anything. Ideas? Thanks! "Douglas J. Steele" wrote: It's unusual to be deleting from a query: why not delete from the table? What happens when you try to run qryOrderDetailsSource: are you prompted for a parameter? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062" 20362 is the current purchase order that is being created that I want to cancel. Any ideas? Thanks! "Douglas J. Steele" wrote: What's the value of strSQL when it fails? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "jped" wrote in message ... Wayne, I used your code example, but when it gets to the line CurrentDb.Execute strSQL, dbFailOnError I get an error that states "Run-tim error '3061': Too few parameters. Expected1. Do you know what this is? Thanks! "Wayne Morgan" wrote: 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 |
|
Thread Tools | |
Display Modes | |
|
|