[Solved] Error linking subform query to main form

Creating and using forms
Post Reply
Dadrick
Posts: 2
Joined: Fri Mar 05, 2021 7:20 pm

[Solved] Error linking subform query to main form

Post by Dadrick »

Hello,

I begin by saying I am a rank newbie with any kind of database program, I'm trying to learn by doing.

I had stability issues with my database, and it finally crashed hard and would not recover. So I switched to using Villeroy's suggested Split macro to begin from scratch. Now I am having an issue where my query for a subform won't load. The query runs as expected on its own, when I link it to the main form, all the other fields load except the formatted text box for the query result.

The error message reads:
“The data content could not be loaded.”
“user lacks privilge or object not found: FactoredTimeID” ← the qquery's field that links to Mainform subform.

When I select MORE to track down the error there are two Errors and an Information on the list:

Error #1 : “The data content could not be loaded.”
Error #2:
SQL Status: 42501
Error code:-5501

user lacks privilege or object not found: FactoredTimeID

Information:
The SQL command leading to this error is:

SELECT "Recipes"."RecipeID" AS "FactoredTimeID", ( "Recipes"."RecipeHours" + ( "Recipes"."RecipeMins" / 60.00 ) ) * ( 1.00 - "Ports"."LaborFactor" / 100.00 ) AS "FactoredTime" FROM "Recipes", "Ports" WHERE ( "Recipes"."RecipePortID" = "Ports"."PortID" ) AND ( ( "FactoredTimeID" = :link_from_RecipeID ) )

This appears to be the auto-generated SQL that is the result of popup that I used from link fields in the Data tab link fields wizard popup. It is the same code that I wrote to create the query but with the added “AND ( ( "FactoredTimeID" = :link_from_RecipeID ) ).

Any thoughts on why this isn't working? If it is a privilege thing where do I fix that? Or why can't it find the object?

Thank for any insights that can be provided.
Last edited by Dadrick on Fri Mar 05, 2021 9:33 pm, edited 1 time in total.
OpenOffice 4.1.9 on Windows (64bit)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Error linking subform query to main form

Post by Villeroy »

You can omit the alias in "Recipes"."RecipeID" AS "FactoredTimeID". The actual name does not matter. You can attach any form label you want.
You may use the alias but use the true name in the WHERE clause ... AND ( ( "Recipes"."RecipeID" = :link_from_RecipeID ) )
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Dadrick
Posts: 2
Joined: Fri Mar 05, 2021 7:20 pm

Re: [Solved] Error linking subform query to main form

Post by Dadrick »

:bravo: I'm not 100% sure what just happened but it WORKED ! :super:

I will study up some more, to grasp the whys.

Thanks You!
OpenOffice 4.1.9 on Windows (64bit)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Error linking subform query to main form

Post by Villeroy »

Dadrick wrote:I will study up some more, to grasp the whys.
I think, the parser reads the FROM and the WHERE clause to collect the right rows from the table(s) before it reads the column list of the SELECT clause. So the alias name "FactoredTimeID" is not known when it tries to compile the row set.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply