One subform for common fields across nested data?

Creating and using forms

One subform for common fields across nested data?

Postby chrispdx » Tue Jan 21, 2020 10:53 pm

First: thanks to the volunteers and admins for all the outstanding assistance and resources you provide to this community.


This is a database for the management of a private family cabin that is owned and used by a dispersed second generation.
  • Everything is organized into a three-layer hierarchy of locations: (1) Sections > (2) Areas > (3) Sub-Areas (defined as three tables with PK/FK relations established).
  • Any location, at any of the three levels, can have two main types of data:
    (A) Orientation/Summary Information (a text field in each of the three Location tables), and
    (B) checklist items for procedures occurring at four main stages each year (defined in the "TYPEINFO" table):
    (1) Season Opening,
    (2) Mid-Season Departure,
    (3) Mid-Season Arrival, and
    (4) Season Closing.
  • These check list items are all stored in a single (poorly named) "INFO" table with foreign key/s to the associated location and a field for TYPEINFO.
After much effort and learning (largely thanks to this forum), this all seems to be working well.

QUESTION Database: ... 9RIciEdKMA

I've created a form (with several linked subforms) for reviewing and editing the Location, Orientation, and Checklist data ("Main Form 3"). This form works fine, but it is unwieldy to have three identical report sections for the exact same data points (Orientation field and four stages of checklist items) -- one for each of the three Location levels (Section>Area>Subarea). Not every Location has these data and so the user sees a lot of blank fields and has to scroll up and down the form to find the relevant info. I would prefer to keep this all on the same main form rather than having to open a new form window for Location details.

Can you help me show the Location "details" (i.e., the Orientation field and four Staged Checklists subforms) in the same place, regardless of which location level is the focus? So far I have been able to avoid using any macros, thankfully, but I presume this would involve three "Detail" buttons (one for each of the Location table-controls) to show the selected Location's Orientation field and the four Staged Checklist subforms) in the same spot, whether it's a Section, Area, or Subarea.

Would "power filtering" be a better approach rather than using the three Location subform table controls?

Thank you for your assistance.
LibreOffice | OSX 10.15.2
Posts: 4
Joined: Tue Oct 02, 2018 1:07 am

Re: One subform for common fields across nested data?

Postby UnklDonald418 » Sat Jan 25, 2020 7:12 pm

As time allowed, I’ve been looking at your database and I believe what I uploaded does something similar to what you are asking. It looks like you already have most of the pieces to use power filtering, so I went that route.

I uploaded a file that contains 3 elements that you can copy into your JDBC (Split) database.
A table Filter3 is a modified version of your filter table.
A query, INFO_all_qry01 which is your query with a few indexes added that are needed to connect to the filter table.
A form document Main Form 4. For it to work properly requires two push buttons that need to be pushed after each change in the filter table selections. It would be possible to eliminate the buttons but that would require coded macros. The form document is for lookup only, you would still need your other form for data entry. It might be possible to combine them into a single form document but it might be a little crowded.
(14.33 KiB) Downloaded 6 times
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.7 & LibreOffice - Windows 10 Professional
Posts: 1286
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: One subform for common fields across nested data?

Postby chrisb » Tue Jan 28, 2020 3:33 am

chrispdx said:
Can you help me show the Location "details" (i.e., the Orientation field and four Staged Checklists subforms) in the same place, regardless of which location level is the focus?

the answer may be to replace the four table controls with one table control which contains a flag that enables identification of the various stages of the season (opening, departure, arrival & closing).
even more page space can be reclaimed by replacing the three table controls (section, area, sub-area) with cascading list boxes.

i have run up a small demo db which utilises your table data but uses a different structure in order to provide you with a working example.
i added the table "tSeason" which contains the values (opening, departure, arrival & closing).
the form occupies one page & is fairly easy to read, input is simple.
two small macros are used which update the list boxes & reload the inner forms.

i wish you luck but can envisage a multitude of issues as your project progresses.
(71.43 KiB) Downloaded 5 times
open office 4.1.7 & LibreOffice 6.3.2 using HSQL (Embedded) and HSQL 2.5.0 (Split) on Windows 10
Posts: 207
Joined: Mon Jun 07, 2010 4:16 pm

Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests