RunQuery DeleteQuery

[Waldin]

hi this is my code that runs a Query on Update of a field the 2nd half is suppose to delete the query once information is removed which triggard the query to run but it doesnt reverse the query, i know my bottom half of the the code is wrong but i cant see where, please help me fix it.


Option Compare Database
Private Sub Product_AfterUpdate()
DoCmd.SetWarnings False
Me.Descriptions = Me.Product.Column(1)
Me.PartsPrice = Me.Product.Column(2)
If IsNull(Me.PartsPrice) Or Me.PartsPrice = "" Then
Me.PartsPrice = 0
End If
DoCmd.OpenQuery "QryQuotationAppendRR"
DoCmd.OpenQuery "QryQuotationAppendRR2"
DoCmd.OpenQuery "QryQuotationAppendPaint"
DoCmd.OpenQuery "QryQuotationAppendPaint2"
DoCmd.OpenQuery "QryQuotationAppendLabour"
DoCmd.OpenQuery "QryQuotationAppendLabour2"
DoCmd.OpenQuery "QryQuotationAppendOutwork"
DoCmd.Requery
Forms![Quotation]![QuotationDetails].SetFocus
DoCmd.SetWarnings True
If IsNull(Me.PartsPrice) Or Me.PartsPrice = "" Then
Me.PartsPrice = 0
End If
DoCmd.DeleteObject "QryQuotationAppendRR"
DoCmd.DeleteObject "QryQuotationAppendRR2"
DoCmd.DeleteObject "QryQuotationAppendPaint"
DoCmd.DeleteObject "QryQuotationAppendPaint2"
DoCmd.DeleteObject "QryQuotationAppendLabour"
DoCmd.DeleteObject "QryQuotationAppendLabour2"
DoCmd.DeleteObject "QryQuotationAppendOutwork"
Forms![Quotation]![QuotationDetails].SetFocus
DoCmd.SetWarnings True

End Sub

 

[John Big Booty]

My first question is; why are you deleting a set of Queries once they have been run You do realise that once they have been deleted they are gone for ever. So this code will only ever run once

 

[Waldin]

The OpenQuery Command Runs only once a part is selected, then the Remove and Refit, Paintwork,Labour and Outsourcing that goes with that specific part are automatically filled in-in the SubForms labelled Labour,Paintwork and Outwork (this is because each part is different and some parts has too many details to enter.)


so the query works like a dream, however if i remove a part i would like it if access could like re-query backwards and remove whatever was added upon the selection of that specific part.


i hope that makes sense

 

[John Big Booty]

What confuses me is you are using;

[URL="http://msdn.microsoft.com/en-us/library/office/bb237899%28v=office.12%29.aspx"]DoCmd.DeleteObject[/URL] "QryQuotationAppendRR"

Which will permanently delete your Query QryQuotationAppendRR which means that your code will only run once, from there on it will crash as you have deleted all the queries that the code refers to.

If you wish to delete an item from your DB, you need to create a Delete Query and use DoCmd.OpenQuery method to run that query.

 

[Waldin]

Thank you MR BIG BOOTY, most helpfull, i was definatly on the wrong path...