I created a financial form and report which include the name of the company, the year, and the revenue. The form is based off of my Financial table called "tblFinancial". This table includes:
Transaction#.......Autonumber, (primary key)
EntityID...............Number,(Autonumber, primary key in another table called tblEntity)
EntityName..........Text
Year....................Number
Revenue..............Currency
With transaction# autonumber as my primary key, I can have multiple years and revenue values for the same entity.
In my table, in design view, under the Lookup tab, BOTH my EntityName and my EntityID have the following:
Display Control: Combo Box
Row Source Type: Table/Query
Row Source(for EntityID): SELECT tblFinancial.EntityID FROM tblFinancial;
Row Source(for EntityName):SELECT tblFinancial.EntityName FROM tblFinancial;
These combo boxes allow me to choose the EntityName and EntityID from a drop-down box in my tables, queries, and forms. My EntityName field has a direct coorelation/relationship to my EntityID. For example:
EntityName: CompanyA......CompanyB........CompanyC........Comp anyD
EntityID:..........1...................2.................. ...3....................4.......
Here's my problem: I want to be able to just choose the EntityName from the combo box in a form, table, etc. so that it will automatically know the EntityID so that I won't have to input the EntityID each time I want to add Revenue for a different year. I'm guessing I won't need a combo box for the EntityID but I'd like to keep the combo box for the EntityName. How can I make some sort of relationship so that when I choose the EntityName it will also include its EntityID Number in a form?
Thanks a lot.
-Luke