How to Auto-Fill Table Using Sub-form links

[craigrson]

I have created a database (attached) with 3 tables, 01_Artifact, 02_Cult_Period, and 03_Group where the majority of information is entered into the 01_Artifact table. I want to use the 02_Cult_Period table to filter the options available in the 03_Group table to help standardize data entry and have the user select the appropriate entry by clicking on the record marker on the left side of the table. 02_Cult_Period and 03_Groups are linked via the “cult_id” field and both tables are linked to the 01_Artifact table using the “cult_id” and “group_id” fields. When I select the desired Cultural Period and Group in the tables on the data entry form, I can see that it populates the “cult_id” and “group_id” fields on 01_Artifacts (read only fields above the tables on the Data Entry Form), but when I save the entry the “cult_id” and “group_id” for the artifact are not saved. For example, the database includes 2 records, the “cult_id” and “group_id” for the first should be “1” (Prehistoric) and “6” (Arms). The same fields for the second object should be “1” (Prehistoric" and “1” (Kitchen), but the fields only display the entries for the most recent record (“1” and “1”) and zeros appear in the fields on 01_Artifacts.
I am not sure if the problem is being caused by improper main form/subform structure or possibly having the record buttons on the wrong form. Any guidance would be very much appreciated!

Artifact Catalog-Forum.odb

Ohh nooo…attached is the nearly final version of the data entry form…As you can see from the tables, I have been struggling to normalize the entries for subforms and retain the “cascading” tables functionality, but I was getting close…or so I thought.

I guess I thought I was closer than I actually am, given that the subform filtering is working (Period, Group, etc.) and the selections appear to populate the field on Artifacts, but I just couldn’t get Artifacts to retain the values when adding new records. I will have to take a closer look at your example and see if I can learn about macros…any suggested resources for teaching a newbie about macros or suggestions for the type of macros I may need to employ?

Artifact Catalog-Forum2.odb

[Ratslinger]

Hello,

Do not see this as being a form and sub form situation. This is really for a list box but it does create additional problems.

First is “cult_period”. So you can create a list box which contains the items in this table and it can store the “cult_id” value in the “Artifact” table.

The “group” is actually another list box. This is where the problem arises. What should be listed here is actually dependant upon the selection of the “cult_period” list box and in to do that you must write specific macros.

For an example of this, see How to use user selected value from combobox1 in combobox2 ‘SELECT’ statement.. Look in my answer there in the edited section for the sample → ProgressiveListboxes.odb

You will need to expand the answer by clicking on the (more) notation there.

Edit 2020-04-30:

Not certain this will be of any help. Cannot conceive of a method other than macros to set progressive list boxes. A sample of this is attached and has had limited testing done with it. The form is DataEntryFormModified. The code used has some commenting to note some of what is happening. The code is not overly complex but repetitive to keep it simpler to possibly comprehend.

For a list of links to Base and macro documentation see answer in this post → To learn LibreOffice Base are there introductions or tutorials?

Sample ------ SecondPost.odb

[craigrson]

Thanks Ratslinger, I considered cascading listboxes, but I am very much a novice when it comes to macros and like the look of the tables (which allow the user to easily see the options available at each stage). This form is just the beginning of a much more detailed data entry form that I expect will include 8-10 subforms and thousands of options when completed, so I am really hoping to stick with the form-subform format. So I guess my questions are…Is it possible to create a data entry form using this format and if so, how can I get the selection on the subform to populate the linked fields in the main form?

[Ratslinger]

@craigrson,

With forms and sub forms there are still problems. A sub form gets its value from the form selected. What you want is for the sub form (Artifact) to get a value from the form (Cult) AND for another sub form (Group) to get a value from form (Cult) AND then for “Group” to act as another form to sub form (Artifact) for another auto value placement.

Not certain of another method to complete your request without some macro coding.

[Ratslinger]

@craigrson,

Have now looked at your updated sample. You have added a number of tables and this would require more analysis and code. Based upon your comments, not certain how you would be able to handle coding this. Learning LO Base macros can take quite some time.

Coding one or two may be OK but the more added the more the complex the code becomes.

[craigrson]

@Ratslinger,
I was afraid this might be the case after your previous comments. Do you have any suggestions for how I may be able to achieve similar functionality, even if it requires starting from scratch?