I have a table with two fields (institute and address). In a form I am placing a combo box, but I can link only one of the two fields (institute). If a put a second combo with the second field (address), when I chose one field from institute, I don’t get automatically the associated address.
Is there a way to link the two fields, so that when I select one field form institute, I also get the associated address automatically?
Is it really a combo box? A combo box is used to show data and input new data. When reading the description you made I would use a list box.
SELECT
"insitute"||', '||"address",
"ID"
FROM "Table"
ORDER BY "insitute"||', '||"address";
This would be a code for a list box, which will show both: institute and address. The foreign key “ID” will be saved in the table, which is data source for the form.
Thank you, but how do I implement this code in the GUI? Essentially, if I have two entries in the table (INST1, street 1) and (INST2, avenue 2) and if I select INST1 for the institute field I should get directly street 1 in the address field. What sort of button shall I use?
There are no separate fields. The list box will show the content of both fields. The primary key of the table with “institute” will be written in the data source of your form as a foreign key for the “institute”.
In a list box you go to Data → Type of list contents → sql and then put this code into List content. Have a look for the right table name and the right name of the primary key field.
Thank you. I see what is doing: it creates a merger between institute and address with a dash as a separator. What I am looking for is different: I select institute for the INST field of another table, and automatically I get the associated address into the ADR field of that another table:
TABLE 1:
institute address
INST1 street 1
INST2 avenue 2
TABLE 2:
INST ADR
INST1 -> street 1
I think you may need to think about form-subform filtering, as discussed on this topic lots of times past …
Seems you try to duplicate institute and address instead of linking tables with foreign key. I would never design such a database.
The way could be to link forms to sub forms. But why don’t you create a relationship between table 1 and table 2?
I did: the two tables are linked by a foreign key…
So why do you want to copy data from one table to the other when you linked the tables by a foreign key?
Could you add an example?