Link two list boxes

English
#1

Hello everyone.
I created a database with three tables:

  • “company” → contains the data of the companies with ski resorts
  • “ski resort” → contains the data of each single ski resort
  • “interventions” → are used to record every intervention (repair …) that has taken place on each single ski resort.

I have therefore created the respective forms, but what interests me at the moment is the “interventions” form.
I would like to make sure that in the “interventions” form, there are two list boxes, one to select the ski companies and one to select only the ski resorts that belong to the selected company.

In practice I would like to make sure that when a company is selected in the “listbox_company” list box, automatically in the “listbox_ski_resorts” list box there are only plants belonging to the selected company.

I have tried and tried again but have not been able to do so for now, so I am attaching my own LibreOffice Base file.

Thank you all.
Sorry for the bad English.
ProvaDB_Forum_Listbox.odb (39.5 KB)

#3

https://forum.openoffice.org/en/forum/viewtopic.php?p=251611#p251611
https://forum.openoffice.org/en/forum/viewtopic.php?p=449618#p449618

#2

Don’t see the file…

#4

i’m apologize, i update it right now.

#5

You didn’t define relations.
There is “ID_company” in table “ski_resorts”, which contains values, which aren’t part of table “company”, field “ID”.
There is also a field “ID_company” in table “interventions”. Do you want to link table “company” to both tables (with different values?)?
If you link table “ski_resorts” also to “interventions”, you will get a link from “interventions” through “ski_resorts” to “company”.

See this without any list box: Form, subform and subsubform …
ProvaDB_Forum_Listbox.odb (50.2 KB)

#6

Hi RobertG, sorry maybe I didn’t make myself clear.
What interests me is only the “interventions” form.

I would just like to be able to enter the “interventions” form, select a “company” from the listbox, and make sure that automatically based on the selected company, in the “ski_resorts” lisbox there are only the “ski_resorts” corresponding to the selected “company”.
Thanks again.
Sorry for my English

#7

Hi Gerardo,

again: without defining relations nobody knows what should be linked. I couldn’t guess this when looking at the fields you created in the tables.
If you choose a company in mainform it will automatically show all available ski_resorts of this company in subform. Now you could choose the right ski_resort and could add interventions in subsubform. All this works without any macro.

And one hint: The database file is buggy. Creating a form with a subform produces error. Have created a new database file with the tables and it will work.
FormSubForm.odb (25.1 KB)

1 Like
#8

Thank you very much, RobertG
Now it works fine and without macros, sorry if I have not explained myself well.

#9

Be careful with the “solution”. The “Listboxes” are tablecontrols. The forms aren’t write-protected at this moment. I would set the tablecontrols to write protected, because you don’t want to change the content in ski-resorts and company.

1 Like
#10

I would set the subforms’ property “Navigation bar” to “Parent”. This way the toolbar always refers to the top level form.

1 Like
#11

Hi, sorry to bother you again but, i just found now that in the file.odb that you sent me there is a little problem.
In the “interventions” form, when i save the record, all data except company_ID is saved in the “interventions” table.
Thank you again.

#12

Yes, it’s right. You haven’t set any relations. I don’t know why you need “company_ID” there. You will get the company through “ski_resorts_ID”.

#13

hello @gerardo.mottini,
.
it’s very important that serious consideration is given to structure before a single table is created.
a resort may be purchased by another company.
interventions are made to resorts regardless of company.
.
the interventions table does not require a link to either company or resort.
the ski_resorts table does not require a link to company.
.
what you do need is a junction table which links resorts to companies.
this enables us to assign many companies to many resorts on the basis of current ownership.
.
selecting a company will only show the resorts controlled by said company.
you need to select a specific resort when applying interventions.
.
take a look at the attachment.
I have added a filter table and a small macro which updates that table and refreshes the forms, it’s fired by the listbox ‘Select Resort’.
2 records have been added to the table ‘interventions’ which reference the resort ‘Impianto TAR’
I don’t have time to thoroughly test right now so here’s hoping.
.
PS companies do relocate therefore hard coding addresses may require similar treatment.
Interventions.odb (43.1 KB)

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