Page 1 of 1

[Solved] Form SQL Error

PostPosted: Sun Jun 03, 2018 5:30 am
by chris-nz
A couple of Errors occured.When adding a SUM nested query to the buydca.odb Invoice & Details form, it initially worked,then broke the Form.
I'm trying to do a nested query of the subform InvoiceDetails & Total Price query, to calculate a Sum Total from the item's Total Price's The nested query only has one field, which is the Sum function of the Total Price field in the query (which links into the subform.) The Sum query was then tied to a Form numerical field, and initially worked. Then later on, reopening the db, it broke the Form, because the InvoiceID and associated Main Form fields no longer loaded/displayed. So i tried to delete the new field, and it's subquery -- this resulted in the problem, that when opening the Invoice & Details Form again, after saving, the db kept trying to access the subquery SQL, though it and the associated field has been deleted. (for clarity i recreated the nested subquery Sub Total for anyone interested in analyzing the bug).
I'm unable to find a menu option for viewing the Form subquery SQL , which should have been deleted when it and the associated field was deleted. Why when opening the Form, is it still trying to access the now deleted sub nested query ?
Trying too explain this is difficult when keeping the description as quick and simple as possible.

Re: Form SQL Error

PostPosted: Sun Jun 03, 2018 5:50 am
by UnklDonald418
Why when opening the Form, is it still trying to access the now deleted sub nested query ?

Looks like one of those silly errors we sometimes make.
When I open the “Invoice & Details” form in the Edit/Design mode and open the Form Properties dialog for MainForm I see Content type = Query and Content = Sum total.

Re: Form SQL Error

PostPosted: Sun Jun 03, 2018 6:42 am
by chris-nz
Ok makes sense. Any thoughts on why the nested query doesn't work ? ta

Re: Form SQL Error

PostPosted: Sun Jun 03, 2018 6:48 am
by UnklDonald418
As to your Sum Total query.
You can create a query to calculate the Sum, but also include the "Invoice_ID" field in the query. Something like:
Code: Select all   Expand viewCollapse view
SELECT "Invoice_ID", SUM( "Total Price" ) AS "InvTot" FROM "InvoiceDetails & Total Price" GROUP BY "Invoice_ID"

Add a Form to your SubForm (I guess it would be a SubSubForm) and use the query as the data source and the "Invoice_ID" to link to the "Invoice_ID" on the SubForm.
To the SubSubForm add a currency control and use the calculated total as the Data field.