[Solved] Subform query using a parameter

Creating and using forms
Post Reply
pastim
Posts: 56
Joined: Fri Jun 15, 2012 2:16 pm

[Solved] Subform query using a parameter

Post by pastim »

I am new to Base, moving from Access.

I am trying to get a form to display some data based on a value I may wish to change, but not by using a dialogue, and not by changing something in the database.

I have produced a from, with a subform that displays the data I want, and has a parameter in the SQL. As a result it asks the user to type in the parameter. That all works OK.

What I want to do is get this sub-form to use a value in the main form as the parameter.

I named the parameter 'xyz' (referred to as ':xyz' in the subform SQL) and created a text field in the main form named xyz. I initialised this on main form load (using a basic macro), but the subform doesn't seem to find the xyz control in the parent and ignores it, asking for user input again.

I tried using the 'fill parameter' event on the subform, filling in this same xyz control, but that doesn't work either (the subform now does not ask for a parameter to be input - as expected - but returns no data).

The Base Help states:
Here :name is a parameter that must be filled out when loading. The parameter is automatically filled out from the parent form if possible. If the parameter cannot be filled out, this event is called and a linked macro can fill out the parameter.

The question is how can a basic macro fill out the parameter? If it isn't a control in the main form, what is it?
I suspect I am suffering from a complete misunderstanding of what the parameters actually are, but have searched in vain for an explanation.
Last edited by pastim on Tue Jul 17, 2012 9:58 pm, edited 1 time in total.
Ubuntu 12.04 64 bit, LibreOffice 4.0.2, MySQL 5.5.29 managed by phpMyAdmin 3.4.10.1-1
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Subform query using on a parameter

Post by rudolfo »

pastim wrote: What I want to do is get this sub-form to use a value in the main form as the parameter.
A Master-Detail relation, which is a mainform with some kind of overview data and a subform with a detail view does this for you without the need for any macro. You will need two tables, that have at least one column in common, typically a foreign key. Or also quite common that you use the same table for the mainform and the subform. Think of a CD-Collection where you show only the artist and the title of the CD in the (mainform) as an overview and when you select a row in that overview the full detailed data for this CD (running time, issue date, etc) will be shown in the subform.
Once you have a table (or two tables) you can execute the Forms Wizard to create a Master-Detail
relation and you should get a result that is good enough for the basic operations. Play a bit with it and see what it has to offer. If it fits into your needs you might finetune it a bit.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
pastim
Posts: 56
Joined: Fri Jun 15, 2012 2:16 pm

Re: Subform query using on a parameter

Post by pastim »

Thanks (for some reason I did not get an email from my subscription to this thread for your reply, hence my late response).

I fully understand the scenario you outline. Unfortunately that's not what I'm trying to do.

What I was trying to do was have a form with a listbox where I select an single value for an attribute (eg an account - there aren't that many), and then have the subform show the detailed information for that value. With an unbound listbox I could not make this work. After much head-scratching I eventually had to create a spoof table to hold the current listbox attribute value (so the listbox is bound to one, and only one row in that new table). Clumsy, but workable.
Ubuntu 12.04 64 bit, LibreOffice 4.0.2, MySQL 5.5.29 managed by phpMyAdmin 3.4.10.1-1
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Subform query using on a parameter

Post by rudolfo »

Why don't you want to think about my suggestion? When you say you need a listbox to choose and select a record that you want to see in detail, you have to sit back and consider the situation again. Most important is to forget the visual bias that you might have from other scenarios: In reality you don't need a listbox, you only need some control that helps you to select a certain record. It is just the human mind that works very often based on bias and prejudice that makes you think only in listboxes. Actually if you have a table grid control instead of a listbox with the above described master-detail relation and let this grid have only two columns (an id and a descriptive text or name for this id) it does what you want to see. Maybe not in the visual form that you want to see (listbox). But there is a rule of thumb when working with databases: First care for the logic, then for the visual representation.

But if you want to use a Listbox for filtering (their typical use is for a n:1 relation to a lookup table, often reflecting a foreign key) you better look in the tutorial Cascading Listboxes with macros section and among the database examples: Relations reflected by list boxes in forms sounds the most promising.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Subform query using on a parameter

Post by Arineckaig »

pastim quote:
What I want to do is get this sub-form to use a value in the main form as the parameter.
If it were to be of any help, a simple demo to illustrate the method suggested by rudolfo can be downloaded from:
http://dl.dropbox.com/u/10552709/Indexed-Subforms.odb
The "Notes" form document in that Base file explains just two potential methods whereby selecting a value in a parent form provides the parameter that filters the record-set available to and displayed in the sub-form(s).
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
pastim
Posts: 56
Joined: Fri Jun 15, 2012 2:16 pm

Re: [Solved] Subform query using a parameter

Post by pastim »

After some time I discovered that if a subform is based on a table, the master/slave parameters work as expected, provided the slave field is an attribute in the table.

However, if the subform is based on a query (as some of mine are - sometimes queries selecting from other queries) the query needs to use the x = :y syntax, where y (the query parameter) is then defined as the subform (link slave) attribute to link to the main form (link master) attribute. The parameter 'y' does not seem to need to appear as a named field on the subform. It just needs to be specified as the link slave attribute.

Thanks for the advice & help.
Ubuntu 12.04 64 bit, LibreOffice 4.0.2, MySQL 5.5.29 managed by phpMyAdmin 3.4.10.1-1
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [Solved] Subform query using a parameter

Post by Arineckaig »

I suspect there is no inherent difference between a sub-form based on a table and one based on a query. In each case, the underlying command is SQL: when based on a table it is the equivalent of 'SELECT * FROM TableName', whereas when based of a query the fields in the SELECT statement are more specific.

Furthermore all sub-forms are in effect based on a 'parameter query'. An explanation can be found by entering “sub-forms,description” in the 'Index' tab of the Base help file and scrolling down to the “Link Master Fields” sub paragraph:
The sub-form is based on an SQL query; more specifically, on a Parameter Query. If a field name is entered in the Link master fields box, the data contained in that field in the main form is read to a variable that you must enter in Link slave fields. In an appropriate SQL statement, this variable is compared to the table data that the sub-form refers to.
This means the links offered by the Base GUI between a data form and its sub-form can provide/add extensive and highly flexible WHERE clauses that are applied to the SQL on which the sub-form is effectively based. Naturally, on most occasions a field name is simply entered in the Link slave field, but I suspect this is still treated as a variable whose value and name are compared to data in a matching field in the source for the sub-form.

I would greatly welcome correction or comment on any inaccuracies and/or over simplifications in my understanding of the sub-form as described above.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
pastim
Posts: 56
Joined: Fri Jun 15, 2012 2:16 pm

Re: [Solved] Subform query using a parameter

Post by pastim »

Arineckaig wrote:I suspect there is no inherent difference between a sub-form based on a table and one based on a query. In each case, the underlying command is SQL: when based on a table it is the equivalent of 'SELECT * FROM TableName', whereas when based of a query the fields in the SELECT statement are more specific......
I would greatly welcome correction or comment on any inaccuracies and/or over simplifications in my understanding of the sub-form as described above.
You may well be right, but I can only report what I found when trying to make subforms based on queries work, that is that I had to specified the parameter (as :y where y is nominated as the slave field) in the query myself.
Ubuntu 12.04 64 bit, LibreOffice 4.0.2, MySQL 5.5.29 managed by phpMyAdmin 3.4.10.1-1
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [Solved] Subform query using a parameter

Post by Arineckaig »

I can only report what I found
That is indeed what should be expected when the SQL or Query contains a parameter and is to be parsed by the Base GUI to provide the data source for the sub-form: the parameter variable should be set in the slave link property so that its value will be supplied by the value of whatever field has been set in the master link property.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
pastim
Posts: 56
Joined: Fri Jun 15, 2012 2:16 pm

Re: [Solved] Subform query using a parameter

Post by pastim »

Arineckaig wrote:That is indeed what should be expected when the SQL or Query contains a parameter and is to be parsed by the Base GUI to provide the data source for the sub-form: the parameter variable should be set in the slave link property so that its value will be supplied by the value of whatever field has been set in the master link property.
I used a parameter in the SQL only because I could not get the subform to link (and synchronise) to the master without explicitly including the parameter in the query.

I had expected that using one of the attributes of the query, which was displayed as a control in the subform, as the slave field would work, without using an explicit parameter. It didn't.
Ubuntu 12.04 64 bit, LibreOffice 4.0.2, MySQL 5.5.29 managed by phpMyAdmin 3.4.10.1-1
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [Solved] Subform query using a parameter

Post by Arineckaig »

I had expected that using one of the attributes of the query, which was displayed as a control in the subform, as the slave field would work, without using an explicit parameter. It didn't
Somewhat surprising, but may depend on what may have been "one of the attributes of the query"! Normally, provided the record-set for the sub-form - whether sourced from table, query or SQL - contains a field whose data type matches the data type of a field in the record set of the parent form, then those two fields merely need to be shown as the respective slave and master linking field properties of the sub-form. I guess there must be some other explanation why such simple links should not have worked for you.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
pastim
Posts: 56
Joined: Fri Jun 15, 2012 2:16 pm

Re: [Solved] Subform query using a parameter

Post by pastim »

Arineckaig wrote:Somewhat surprising, but may depend on what may have been "one of the attributes of the query"! Normally, provided the record-set for the sub-form - whether sourced from table, query or SQL - contains a field whose data type matches the data type of a field in the record set of the parent form, then those two fields merely need to be shown as the respective slave and master linking field properties of the sub-form. I guess there must be some other explanation why such simple links should not have worked for you.
Beats me. Every time I have tried, with various different tables and queries, it always works as expected with a table, and never with a query unless I use a parameter. I wonder if anyone else has found this?
Ubuntu 12.04 64 bit, LibreOffice 4.0.2, MySQL 5.5.29 managed by phpMyAdmin 3.4.10.1-1
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [Solved] Subform query using a parameter

Post by Arineckaig »

and never with a query
If you are using MySQL as the back-end, do you get the same failures whether the query is parsed by Base or issued directly to MySQL?
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
pastim
Posts: 56
Joined: Fri Jun 15, 2012 2:16 pm

Re: [Solved] Subform query using a parameter

Post by pastim »

Arineckaig wrote:If you are using MySQL as the back-end, do you get the same failures whether the query is parsed by Base or issued directly to MySQL?
For subforms there does not appear to be an SQL versus SQL [Native] option, just Tabel, Query or SQL. From the Help I understood that the 'Analyse SQL' option on the subform definition just allows one to use the ... graphic tool to design a query. I always leave it at 'Yes'.

I know the distinction between SQL and SQL [Native] appears elsewhere (eg for listbox lookups). I've always chosen the SQL option, which I assume helps Base to see what I am trying to do, but I don't know if it makes any functional difference.

So in direct answer to your question I can't be 100% sure if my subform query is parsed by Base or not, but I assume it is. How else would it link the parameter name to the slave field name?
Ubuntu 12.04 64 bit, LibreOffice 4.0.2, MySQL 5.5.29 managed by phpMyAdmin 3.4.10.1-1
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [Solved] Subform query using a parameter

Post by Arineckaig »

If SQL or a query is to work with a parameter then it must be parsed by Base. When a query is created using "Create Query in SQL view", it can be set to 'Run SQL command directly' by selecting the last button on the toolbar which has SQL with a green tick mark on it. The result can then either be saved and used in the sub-form as a named Query, or the SQL automatically saved to the sub-form if the 'Query design' dialog originated from use of the ... ellipsis icon against the 'Content' box when SQL was selected as the 'Content type' for the sub-form.

Base makes it easy to test the output of SQL or a Query in the 'Query Design' dialog before either is used as the source for the sub-form.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Post Reply