Custom form - complex model of data

[josnow]

I am trying to build a form with the following information:

Tables:
Table Role: RoleId, Role
Table TeamMember: TeamMemberId, LastName, FirstName
Table SopReviewer: SopReviewerId, TeamMemberId, RoleId (TeamMemberId = TeamMember.TeamMemberId and RoleId = Role.RoleId)
Table Sop: SopId, SopName, SopOwnerId, RoleId, SopReviewerId (SopOwnerId = TeamMember.TeamMemberId, RoleId = Role.RoleId and SopReviewerId = SopReviewer.SopReviewerId)

Attributes on the Custom Form:
SopId, SopName, SopOwnerId, LastName (based on Sop.SopOwnerId), RoleId, Role (Based on Sop.RoleId), SopReviewerId, TeamMemberId (based on SopReviewer.TeamMemberId), LastName (based on SopReviewer.TeamMemberId).

I have been able to build the form up until I try to include the LastName based on SopReviewer.TeamMemberId. I am trying to build a combo box with the LastName with the caption Sop Reviewer Last Name. The combo box doesn’t want to connect to the SopReviewer table but wants to connect to the Sop table. Does anyone know how to change this so I can pull this information from a different table? Additionally, I want to only pull the TeamMemberId based where Sop.RoleId = SopReviewer.RoleId. Is this possible? Can anyone help me?

[orange]

What is the rowsource of the combo?

see http://www.techonthenet.com/access/comboboxes/