Page 1 of 1

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

PostPosted: Mon Nov 13, 2017 9:40 am
by tlazzari
contact_id porting.png
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

Re: Value of a cell to be reported in another table

PostPosted: Mon Nov 13, 2017 12:47 pm
by 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

Re: Value of a cell to be reported in another table

PostPosted: Thu Nov 16, 2017 6:14 am
by 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   Expand viewCollapse view
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

Re: Value of a cell to be reported in another table

PostPosted: Thu Nov 16, 2017 11:37 am
by charlie.it
Thanks @tlazzari, for a complete sharing why do you not even place the database file?