Passing a parameter to a subform

English
, ,
#1

I have a database with many tables called tbl_1, tbl_2, tbl_3, …, tbl_1000, all with the same structure e.g. (ID, field1, field2, etc), plus a main table with a different structure but with 1000 item entries (ID, data1, data2, tbl_ID), each one corresponding to one of the other tables. In the main table, ID is the primary key (1,2,3 etc), and when this key is selected in a Form, I want it to open a subform with the corresponding tbl_ID.

tbl_main
(ID, data1, data2, tbl_name)

tbl_1
(ID, field1, field2)

tbl_2
(ID, field1, field2)

tbl_1000
(ID, field1, field2)

So the problem is that in the subform I don’t know the name of the tbl_n I am going to display until the appropriate ID is selected in the main form. For example, if in the main form the selection is ID=23, I then want the subform to display tbl_23.

Can you please tell me step by step what I have to do to set up the Form and SubForm to accomplish this?
I am using LibreOffice 6.3.3.2
Thanks!

(Extra information)
Here is an example, grossly simplified. In this tbl_ASME contains information on collections of data called “volumes”. Details of each volume are contained in the files tbl_1, tbl_2, tbl_3, etc. Clearly all the data could be in one large table, (in this case tbl_n) but there would be a lot of redundant information. So I tried normalizing the tables.

As a matter of interest, in the full database, there are approximately 1000 volumes, each containing data on ancient documents, such as births, marriages and deaths from about 500 years ago. Each volume has about 30 columns of data specific to the volume (date, type, who, where, what, subcategories and so on). Most of the volumes have been photographed, with between 100 and 500 records (photos) for each volume. Thus tbl_1 will contain hyperlinks and other information about (say) 500 photographs for volume 1, while tbl_2 contains (say) 300 hyperlinks for volume 2, and so on. This means that I have literally hundreds and hundreds of tables. Access to the photographs in a volume are determined through a series of cascaded queries on tbl_ASME which end up with a volume number, say “n”. Then the system needs to open up tbl_n and display all the links to the photographs for that volume.

(A further matter of interest is that this system works perfectly well in a Microsoft Access application, but I now need to put the data online and so must have something that runs on a Unix server, such as LibreOffice)example.odb.

#2

https://documentation.libreoffice.org/en/english-documentation/

#3

Hello,

Using a Form/SubForm means there is an established relationship between the data in one table and the data in another. Although your main table has ‘tbl_name’ in the records, this is not a database relationship.

A correct relationship would be using a field in the secondary tables linked back to the main record.

Since sub forms are linked back to another form via fields, use of a sub form is not relevant here. It could simply be another main form.

To do what is wanted would require writing macro code specific to your needs. When the main record is read, code is needed to read the appropriate field containing the table name wanted. This is used to fill in the appropriate information on the other form for display and refresh to obtain the data.

Edit:

In order to possibly help, have attached a simple two table Base file. Still have your main table but eliminated table name in records - see no reason for this.

The second table contains the records of your 1000 (?) other tables where instead the data is linked back to the primary table via the ID in the primary table. Have only demo’ed two records (your tables) but should give the basis for all. Have included a form which consists of a main form and a sub form.

Sample ------Relationship.odb

Hope this helps.

Edit #2:

Have taken your sample and created a form. When the button is selected, a small macro will execute to create the desired table name by appending “VOL” to “tbl_”. This is inserted to the secondary form and the data is reloaded. Macro used:

Option Explicit

Sub GetTable
    Dim oForm1 As Object
    Dim oForm2 As Object
    oForm1 = ThisComponent.Drawpage.Forms.getByName("MainForm")
    oForm2 = ThisComponent.Drawpage.Forms.getByName("SelectedTable")
    oForm2.commandType = 0
    oForm2.command = "tbl_" & oForm1.getColumns().getByName("VOL").Value
    oForm2.reload()
End Sub

Sample applying this ------ TableVolumes.odb

#4

Thanks Ratslinger. It looks to me as if I have normalized the tables incorrectly, as I do have references back to the main table in each of the secondary tables, but I don’t know how to use them! I wanted to upload an example file, so as to show you better the situation, but I can’t see how to do that in a comment. Anyway, I will try to normalize the data differently. Concerning the use of macros, I am currently doing exactly that, but I was hoping for an easier solution!

#5

Unfortunately it is not clear what this reference is from the secondary to main tables.

In a typical relational set-up you would not have 100 different tables but rather one table with links back to the main table.

As for adding a sample, you can do this by editing you original question and adding there.

#6

Thanks for your sample “relationship.odb”, I will try and adapt it to my tables. Please see the further infornation edited in the original question above, and my example .odb

#7

@nexturejohn,

Have included macro sample in answer. Understood you have stated using macros already but thought this may be different. Haven’t much else to offer.

#8

Thanks again for your help. I had in fact written a similar macro to progress this, but yours is more elegant than mine, and if I adapt it to my circumstances, I can clean things up considerably. Thanks again for your time and effort.

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