[Solved] Form SQL Error

Creating and using forms
Post Reply
chris-nz
Posts: 6
Joined: Fri Jun 01, 2018 12:31 pm

[Solved] Form SQL Error

Post 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.
Attachments
buydcaold.odb
(76.46 KiB) Downloaded 173 times
Last edited by chris-nz on Sun Jun 03, 2018 7:13 am, edited 1 time in total.
Apache OpenOffice 4.1.5 on Windows 10 Tablet
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Form SQL Error

Post 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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
chris-nz
Posts: 6
Joined: Fri Jun 01, 2018 12:31 pm

Re: Form SQL Error

Post by chris-nz »

Ok makes sense. Any thoughts on why the nested query doesn't work ? ta
Apache OpenOffice 4.1.5 on Windows 10 Tablet
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Form SQL Error

Post 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

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.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply