How to "pull" fields from another table

[Hoib]

OK, here's my set up.

LayoutA based on tbl_Session_Ops. This records daily activity (bags of food given out). Foreign key here set to ::tbl_HH_Members:RecNo. Relationship set between the two TOs using RecNo.

Portal set to ::tbl_HH_Members which has field_LastName and field_FirstName along with RecNo as primary key using auto-enter serial no.

I have a design which drops-down ::HH_Members:field_LastName which works. Problem is ::HH_Members:field_FirstName doesn't follow in. It's blank. I've tried a myriad of different techniques to have FirstName automatically follow the drop-down selected LastName.

Probably something very simple. I'm dying to get this part completed. Can someone suggest a technique.

H

[LaRetta]

Hi Hoib,

Instead of selecting the Member's last name, select their ID. When in Sessions where you want to select the member, place the RecordNo (Members foreign key) field from your Sessions table. To this field attach a value list (pop-up is suggested over drop-down). The value list should be set up as:

Records from field, all values. In the top left pane, select RecordNo from Members. In the right pane, select FullName from Members. If there isn't a full name calculation field, create it in your Members table first by concatenating both first and last as: FirstName & " " & LastName (or LastName & ", " & FirstName). In the value list specification, check 'display values only from second field. With this setup, you can select the Member to assign to the session from the pop-up. If this relationships is one-to-many (one Member can have many Sessions) then portal on your Sessions layout isn't necessary - just your Sessions::RecordNo field.

With this setup, selecting the full name of the Member will insert the Member's primary key into the Sessions::RecordNo field. BTW, it is confusing to use RecordNo and it is easier to understand if you use something which identifies the table from which that key originates. What if later you need another foreign key in the Sessions table? What are you going to call THAT one? Suggest MemberID, MemberNo etc and in Sessions, suggest SessionID. Some Developers preface it with pk or fk. To me it is clear that if MemberID is in the Members table then it is the primary key and if MemberID exists in a different table than Members, it is the foreign key.

If this is not what you need, please explain a bit more. :)

Edited June 14, 2012 by LaRetta