Hi all

I have a users table and a roles table, Users can have more than one role so I have another table to give me M2M relationship.

I have a form / subform (datasheet view) that shows the user and their roles - that works fine.

But... it fails when I try to add another role by filling out the datasheet in the subform. This is a dropdown taking values from the roles table.

The error I get is obsure with no help - 'Cannot add record(s); join key of table <name> not in recordsetset'.

My question is how do I link a dropdown selection to the user and store the data in the m2m join table (called tblUser2Role by the way)


UPDATE
I've dragged and dropped the link table onto my users form and it works OK, in so far as I can add roles. BUT... it's using the ID's not the text values. So maybe the question is how do I link M2M with a subform.

Thanks Andy

that M2M relationship doesn't sound kosher to me. Assuming that the table that links users and roles is called tblUserRoles, then... i feel you should have a 12M between tblUser and tblUserRole, and a 12M between tblRoles and tblUserRoles. Does that make sense?

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."

You are correct with the data model, tha is how is hangs together.

Wish I could draw it.

Andy

are you using VBA at all?

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."

No

I know some VBA, I'm nearly there, by not using the table but a query, returning name then ID. But the list seems to contain every instance of a role, what I'm trying to say is that if for example 6 people have the role of admin, then admin appears in the list 6 times.

A

are you normalizing your data? what i mean is... ei. is your tblUsers consisting of idUser (a number) and txtUser (actual text)? The same scheme for tblRoles. Then you just refer to the ID's in the tblUserRole. You follow me? i'm not getting off the subject... this is actually going to lead somewhere.

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."

Yes

Data fully normalised. Let me try and explain data

tblUser
UserID PK (not autonumber as NTID is unique)
UserName
Phone
ect

tblRole
RoleID PK autonumber
RoleName


tblUser2Role
UserID PK number
RoleID PK number

This is the join

tblRole INNER JOIN (tblEndUser INNER JOIN tblUser2Role ON tblEndUser.EndUserID = tblUser2Role.EndUserID) ON tblRole.RoleID = tblUser2Role.RoleID

Hope this makes sense.

Andy

ok... are you using a combobox in your main form and that filters the subform?

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."

No

As I roll through the Users recordset the the subform changes to show their roles. I want to have a dropdown in the subform (which is showing in datasheet mode) to select the name of another role and have the userID and Roleid written into the tblUser2Role.

A

the subform is being fed by tblUser2Role right?

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."