form for multiple tables, multi yes/no fields... (1 Viewer)

[Ronanm]

I have 3 tables as below...

Fields Level 1 - Level 10 are Yes/No fields. I need to set it up so that in a form when I had a new role, there are the 10 Yes/No fields available to assign the checks to.

Each Role will have 21 Profiles (which don't change) and each of the Profiles will have either Level 1 to Level 10 ticked...

I want a form so that when I add a new Rolem there will be the 10 Yes/No fields available for me to populate, please help, I'm not sure if code is needed or wether my relationships are wrong. Hope it makes sence...

Regards

Ronan

Role RoleID RoleName

RoleDetails RoleID ProfileID level1 level2 level3 level4 level5 level6 level7 level8 level9 level10

Profile ProfileID Area Type Objective Understanding Applying Leading Expert

 

[vbaInet]

If your Role Details is setup the way you have written it, that is the fields are levels 1 to 10, then all you need is a subform for Role Details (linked to Roles form via RoleID) and another subform for Profile.

However, it would adviseable to create yet another table to hold the Role Levels which will have two fields, RoleID and Level. The PKs will be both fields (i.e. composite keys).

 

[Ronanm]

Many thanks for the reply, I will give it a go this weekend, and see if I can work it out. Thanks again

Ronan

 

[vbaInet]

Just drop us a message if you need anything explained.

 

[Ronanm]

Thanks for that
Mmmnnn.
The level (could actually be one combo box as all is needed is to choose one level based on each of the 21 profiles for the level(Each Role has 21 profiles and each profile has 4 levels 1-4 (just changed this))).

So Role1 has 21 profiles and Role2 etc also has 21 profiles. I manually added new Roles and respective profiles for each, and all looks fine in the form. However when I try to add a new Rolef rom scratch from a form, I don't get the 21 profiles with their repective 21 levels to choose from.

So I'm presuming I haven't set up the relationships right, rather than needing to have it programmatically add the fields...

cheers

Ronan

 

[vbaInet]

The level (could actually be one combo box as all is needed is to choose one level based on each of the 21 profiles for the level(Each Role has 21 profiles and each profile has 4 levels 1-4 (just changed this))).

That's right, a combo box for the levels to link to that particular Role and Profile.

So Role1 has 21 profiles and Role2 etc also has 21 profiles. I manually added new Roles and respective profiles for each, and all looks fine in the form. However when I try to add a new Rolef rom scratch from a form, I don't get the 21 profiles with their repective 21 levels to choose from.

You need a subform to display the Role Details. The subform's record source will be the Role Details and Profiles tables as a query.

So I'm presuming I haven't set up the relationships right, rather than needing to have it programmatically add the fields...

I haven't seen your relationships so I can't speculate but in any case you will still need to add the 21 profiles for every NEW role programatically.

 

[Ronanm]

Arrgh. Programatically. New learning curve begun then ) Thanks for the advice.

Ronan

 

[vbaInet]

You've not done any vba programming before?

 

[Ronanm]

You've not done any vba programming before?

Done a little VBA. But not something I have really gotten stuck into. I suppose I could do an update query too. Have it so when I add a new record to the Roles table, it will automatically add this to the RoleDetails table, 20 times with the corresponding 20 RoleProfileIDs. Sound like a plan?

 

[Ronanm]

All done, I created an append query to update the details table with 20 objective ID's for each new role added and assigned that to a macro to run when the user presses the add role button.

Thanks

Ronan