[Solved] Value of a cell to be reported in another table

[tlazzari]

Where I would like the data to be

Dear All,

I have created a simple db with three tables: companies, contacts and contact_company. The final goal is to be able to create a form to input the company name in the company table, the name of the contact in a company in the contacts table and the contacts pecentage ownership of each company in the company_contact table.

In table companies: pk is company_id
In table contact_company: pk is contact_company_id, contact_id is FK, company_id is a FK
In table contacts: pk is contact_id
(see attachment)

I created a Form called Company, which has a subform called contacts_company, which has a subform called contacts (see attachment).
I connected all the master and foreign links but it does't work as I would like to infact, I would like the field contact_id in the table contact_company to be automatically populated when I fill up the contact name. (see attachment)

Does anybody have any advice?

Thanks
Tommaso

[tlazzari]

Just one more point, I have a lot of contacts and companies therefore, let the user choosing from a dropdown list would not be feasible.
Thanks
Regards
Tommaso

[tlazzari]

For those who are interested I solved the issue thanks to a similar solution in an old threat.

The code that did the magic is the following:

Code: Select all

REM  *****  BASIC  *****

Option explicit
Sub update

DIM oForm2 AS OBJECT
DIM oID AS OBJECT
DIM oForm1 AS OBJECT
DIM oIDCloned AS OBJECT
DIM sID AS STRING
DIM sIDCloned AS STRING
DIM oIDcell AS OBJECT
DIM lNameCol

oForm2 = thisComponent.Drawpage.Forms.Form.Form1.getByName("Form2")
oIDcell = oForm2.getColumns.getByName("contact_id")
sID = str(oIDcell.Value)

oForm1 = thisComponent.Drawpage.Forms.Form.getByName("Form1")
oIDCloned = oForm1.getColumns.getByName("contact_id")

lNameCol = oForm1.findColumn("contact_id")

IF sID <> "" THEN 
	oForm1.updateString(lNameCol, sID)
ENDIF

oForm1.updateRow

oForm1.reload

End Sub

Cheers

[charlie.it]

Thanks @tlazzari, for a complete sharing why do you not even place the database file?