Options for refreshing a form containing multiple subforms without macros?

English
#1

Following the progress from all the help in How to change a WHERE clause based on user input (form)?, I’m now trying to setup a form with several Subforms, each showing data from a different query. All Subforms get the same parameter value from the Mainform.

As I understand it, the solution using a button with the refresh behavior is linked to the Subform and so I would need a refresh button for each Subform, to use the same solution. This is not practical. I tried a button with the same behavior but linked to the Mainform. It works for the entire form, but it shows a popup asking the user to save the form first.

I’m still trying to avoid using macros, are there any other options to refresh a form in this case? Thanks.

#2

Good day,
Generally speaking, a MainForm may have several SubForms; it’s true. Nevertheless, if you fancy, you may create in turn a SubSubForm, i.e. a SubForm related to a precedent one…

#3

And updating a Subform would also update any nested Subforms without asking the user for a save?


And the nested Subforms would still read the parameter from the same filter table / row?

#4

As long as I remember, there are 2 different approaches, i.e. if the MainForm is for data entries, the descending Subforms are automatically updated, but a user might use the Form only for a survey , in this case, datas are read only…

#5

I just need read-only, but how do I propagate the parameter value down the nested sequence of Subforms?

#6

Edit your Form and The Form Navigator will allow you to add subforms to your display. (choose Edit Mode).
hint

#7

You can use parameter values in the column list of a SELECT statement.
SELECT "somewhere".*, :paramX AS "X" FROM "somewhere"
In this case, this subform shows includes a column with one value “X” inherited from its parent, and the next subform can refer to column “X”.

#8

I can’t get this to work with a SQL like

SELECT COUNT( "record_id" ) "total", :param "prm" 
FROM "Table" 
WHERE "item_id" = :param AND "user_id" NOT LIKE 'xpto'

Base automatically removes AS keywords.
This would be the first Subform, providing a necessary data point - ‘total’ as well as the parameter for all the other nested Subforms.
But I get this error when I run the query:


image
This query also doesn’t work standalone - if I save it as a Query and run it directly, it shows the same error, whereas a working query with a paramenter filter shows a popup and then displays a result table. The error goes away when I remove , :param "prm".


It does work though if the parameter does not go into the WHERE clause:

SELECT "Table".*, :item_id AS "id_param" 
FROM "Table" 
WHERE "record_id" = 1

But the parameter needs to be an integer, always?. If I type in a string I get zero. I tried single and double quotes around the parameter. Can parameters be strings?
However this is just a gimmick, right? I’m supposed to create a statement with the only purpose of getting the parameter as selectable data field?


What are my options in any case, are there other ways to do this? I mean going back to the original post, I’m trying to create a form consisting of several Subforms (each with a different query) using the same value as a parameter filter, and no macros.

#9

No, strings are possible, but you can only ask for contents of columns, not for the column itself.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.