subforms in the opposite way

[Davide77]

Hallo, I have a form with subform based on two tables. Tables are: Organization and Person. An Organization can have more Persons.
I nested the subform in the opposite way, that means: the main form is the person form and the subform is the organization form. Now when I create a new person and then I would like to write its organization (in the subform control) access tells me that "I cannot assign a value to this object". Does someone as an idea?

 

[DeanWilliams]

Hi,

You do not need to use a subform for this. Using a subform in your database, it would be correct to do it the other way round: i.e: the Organization as the main form and the person in the subform. This is so you can show the main organization and list all the related people. Subforms are usually used to display the many records related to the main object.

If you want a person form, you just need to use something like a ComboBox or ListBox to choose the Organization, you do not need a subform.

If you are using a subform to show more information you can use a combobox or listbox with more than 1 column and base it on a query with the relevant info.

HTH,

Dean

 

[Davide77]

Hallo Dean,
how can I show in the person form the organization where the person works?

 

[DeanWilliams]

OK, so you should have an OrganisationID field in your Person table (from now on tblPerson) to link the person to the Organisation (tblOrganisation). As one solution you could:

1) Add a combobox which is bound to this OrganisationID field (ControlSource should be tblPerson.OrganisationID)

2) Set it's RowSource to a query based on the tblOrganisation table with just the OrgID and the OrgName) fields). As long as the first field is the actual ID.

3) Set the ComboBox's ColumnCount Property to 2 so that the combobox can display the name as well.

4) Set the ColumnWidth of the combobox to 0. This will make the first column hidden and the second column, the name column the width of the whole combobox.

5) Make sure the BoundColumn of the ComboBox is 1. (The hidden ID).

Now you should be able to select the persons organisation by name, but the actual data that will change in tblPerson is the OrganisationID.

HTH,

Let me know if it works, I may have missed something as I am working from memory.

Dean

 

[Davide77]

Dean, this works till the organization are defined somewhere else. But the real problem it comes when I want to add a new person and assign to it a new organization. I cannot create a new organization through the combobox and neither through the subform.

 

[Jinan74]

hello there,
i have a small db that shows u how 2 solve this, but how can i send it?

 

[Davide77]

Hallo Jinan74,

you can send it to my email adress: Davide.Seddio@ict-4d.org

thanks