Updating two tables using forms

[Student]

I have two master tables Company and Shareholders as follows

Company Shareholders
GVKey ID
Name Name
Type

Further I have a ShareDetail table with shareholdings in companies as
follows

ShareDetail
GVKey
Year
ID
Holding Percentage

My problem is that the Company master is populated but the Shareholder
master is not. I wish to populate both the ShareDetail and Shareholders
table simultaneously when entering data for ShareDetail. In particular
whenever I enter data the procedure (before committing) should check
using Name if the record is present in Shareholders, if yes update only
ShareDetail, if no update both ShareDetail and Shareholders. I will use
a form to enter data. Can anyone tell me the code for accomplishing the
same?

 

[Guest]

The way I would do this is to set up your ID field on your ShareDetail table
as a lookup to a ComboBox with a row source as your ShareHolders table.
Also, set the Limit to List property to Yes.

On the form you use for your ShareDetail data entry, add code in the
NotInList event that will open the form for the ShareHolders table for the
user to enter the new info.

You don't mention it, but I would do the same for the GVKey field so the
user can enter new company info "on the fly".

Hope this helps.

 

[Student]

Hi Francis,

Thanks for the reply that was helpful. Just a quick question when I
write an event to open the Shareholder form (when NotInList) and save
the shareholder record, the new shareholder is still not displayed in
the list in the ShareholderDetail form. How do I ensure that shows the
new entry in the list?

Student