program boolean when adding a new TeamMember to form

[josnow]

I have created a database that some of the following tables: TeamMember, Role, TeamMemberRole, Skill, TeamMemberSkill, TeamMemberSkillNRole.

The TeamMemberRole table takes all of the RoleId under the Role table against the TeamMemberId. So if there are 20 RoleId in the Role table, I have created 20 records for each TeamMemberId. I’ve done the same with the TeamMemberSkill and Skill tables, so if there 30 SkillId, I create 30 records for each TeamMemberId. TeamMemberSkillNRole takes the records from TeamMemberRole and TeamMemberSkill and consolidates them. So for each TeamMemberRoleId a Team TeamMemberSkillId is added to the TeamMemberSkillNRole table by TeamMemberId. So in this case I have 20 TeamMemberRoleId for each TeamMemberId. Each TeamMemberRoleId (by TeamMemberId) would then create 30 records of the TeamMemberSkillId (by the same TeamMemberId) for each TeamMemberRoleId them, thus creating 600 records for each TeamMemberId. So, if I have TeamMemberRoleId 1 through TeamMemberRoleId 20, TeamMemberRoleId 1 would create 30 TeamMemberSkillId against TeamMemberRoleId 1, creating 30 records. I currently create these records manually. It is important to keep each of these tables separate because management wants reports showing Role only and Skill only. Also, each TeamMemberSkill has a SkillLevel assigned to each Skill. Once a skill level is assigned, it is important that the skill level for the skill to be the same. This is why there is a separate table for TeamMemberSkill and the skill level must consistent in TeamMemberSkillNRole.

What type of Boolean would I need when a new TeamMemberId is created on a form to automatically start a program to add the new TeamMemberId to the tables TeamMemberSkill by SkillId, TeamMemberRole by RoleId and TeamMemberSkillNRole which consolidates the TeamMemberSkillId and TeamMemberRoleId? Then where would this program go?

[June7]

Don't know what you mean by needing a boolean to start a program. A procedure is initiated by an event, such as button Click or data control AfterUpdate.

Don't really understand the data structure you describe. If you want to provide db for analysis, follow instructions at bottom of my post.

[josnow]

I'm sending my db right now. Your right, I want an event to run this.

[josnow]

Attached is the db

[June7]

One of the most confusing spider-webbed table relationships I've run into.

What form do you want code behind?

Why do you need code and not use form/subform arrangements?

Code in whatever event could be like (showing text, date, number field types):

CurrentDb.Execute "INSERT INTO tablename(fieldname1, fieldname2, fieldname3) VALUES('" & Me.textbox1 & "', #" & Me.textbox2 & "#, " & Me.textbox3 & ")"

[josnow]

1. Is there a better architecture for this? I need to have 1 role per skill per team member. That is why I created the TeamMemberSkillNRole2. I also need 1 skill per team member. I know it is confusing, but do you have any better suggestions?

2. An event should be used when I create a new TeamMember - the TeamMemberId will create an update in TeamMemberRole with the TeamMember assigned to each RoleId. Then the TeamMemberId will create an update in TeamMemberSkill and assign a TeamMemberId to each SkillId with the default SkillLevel = 1. After that, then the newly created TeamMemberRoleId and TeamMemberSkillId should update TeamMemberRoleNSkill2. Is this too complicated or is the are way to do this?

One of the most confusing spider-webbed table relationships I've run into.

What form do you want code behind?

Why do you need code and not use form/subform arrangements?

Code in whatever event could be like (showing text, date, number field types):

CurrentDb.Execute "INSERT INTO tablename(fieldname1, fieldname2, fieldname3) VALUES('" & Me.textbox1 & "', #" & Me.textbox2 & "#, " & Me.textbox3 & ")"

[June7]

I don't know what, if anything, would be better, just don't understand your data and business practices enough.

However, as an example, if you want record for each Role in TeamMemberRole for a new memberID, then an INSERT SELECT sql action might be what you want:

"INSERT INTO TeamMemberRole(TeamMemberID, RoleID) SELECT " & Me.textboxMemberID & " AS MembID, RoleID FROM Role"