Event Procedures in Forms

[Sridhar.Arcot]

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
Name
Type
Holding %

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]

Hi Sridhar,

You should commit a record to the Shareholders master table, before
attempting to add a related record to the ShareDetail table. In fact, if you
have defined relationships (Tools > Relationships) and enforced referential
integrity (RI), then Access will not allow you to add a record to the
ShareDetail table unless there are matching parent records in each of your
master tables.

Two of the field names you have chosen happen to be reserved words in Access
and/or JET: Name and Type. You should avoid naming anything in Access using
reserved words. I also advise you to avoid using special characters, such as
the % sign, in anything that you assign a name to in Access. Here are some KB
(Knowledge Base) articles for your reference:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

Also, your ShareDetail table appears to have repeated the Name and Type
fields from the Shareholders master table. There should be no need to repeat
this information in a separate table.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

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
Name
Type
Holding %

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?