Re: [libreoffice-users] success: form with detail subform; working list boxes in main form

Update: for those who wish to examine it, the database discussed below 
may be downloaded here 
<http://www.tomcloyd.com/misc/storage_containers2.zip>.

On 06/23/2011 02:36 AM, Tom Cloyd wrote:
> This is very early in my learning period, but I seem to have achieved 
> a small breakthrough.
>
> It now appears that if list boxes are to work reliably, or at all, in 
> a form with a subform, they must be created using raw tables - i.e. 
> view or queries cannot be used. Likewise for the main form and the 
> subform. Use only raw table, not views or queries. This means that one 
> cannot take advantage of aliases set up in view or queries, but that's 
> no great loss. Aliases mainly affect labels, in a form, and they can 
> be altered via the control's property dialog.
>
> A remaining problem is how to get sorted list box source tables. If I 
> have a list box that has 250 possible items, I need them sorted, but I 
> cannot see how to do this. So far, using a view to achieve this has 
> resulted in screwy or totally nonfunctional list boxes. I suspect the 
> SQL needs adjusting, but that's a bad solution for your average user. 
> I shouldn't have to be an SQL wizard to get a sorted list box.
>
> In any case, my initial, test case database is working perfectly. It 
> going into immediate use, as it details storage of various things I 
> own (books, clothes, etc.) in various containers, in various rooms, 
> and even various buildings, etc.
>
> So, the procedure I used tonight was this:
>
> 1. Set up main table, with foreign key field for one or more detail 
> forms.
>
> 2. Set up at least one detail table.
>
> 3. Use the main menu Tool>Relationship functionality to link detail 
> table primary key to relevant main table foreign key. (Do not know if 
> this is absolutely necessary or not, but it doesn't hurt anything.)
>
> 4. Launch form designer wizard. Use the main and detail tables set up 
> above. Do NOT use any view or query. Set up main table to display 
> display one record at a time, as set of text boxes linked to record 
> fields,. Set up the subform as a grid matrix (or whatever it's 
> called), so multiple detail records will show. Main table order can be 
> set as a property, and subform can be ordered once the form is 
> executed, using sort options available in the interface of the 
> activated (executing form); they will persist from session to session!
>
> 5. In the form designer, replace all main record field text boxes with 
> list boxes, using the list box wizard (be sure the "use wizard" icon 
> on side of page is depressed, to get the wizard). As before, use raw 
> tables, not views.
>
> 6. In executing the form, one can click the icon at the bottom of the 
> interface which has the tool tip description that begins "Activates an 
> additional table view..." to get a very useful view of the main 
> database at the top of the form. It is especially interesting to note 
> that in this view, for every field to which one attaches a list box in 
> the main form, the main table foreign key is replaced by the data 
> field to which it refers, once you make a selection from a list box. 
> This makes a database filled with foreign keys sudden very readable. 
> Where the foreign keys have not been instantiated, it is readily 
> apparent.
>
> This seems to work without a problem (so far).
>
> If this is the most I get Base to do for me, it's worth the price of 
> admission and then some, but I suspect it can do more!
>
> I hope this description of what I have found to work helps some other 
> folks struggling with forms and list boxes, and I for damn sure an 
> going to try to get the essential information into the documentation, 
> if that proves to be appropriate.
>
> All constructive comments welcome...
>
> Tom


--
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted