Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 11th, 2005, 08:03 AM
Authorized User
 
Join Date: Jun 2003
Location: Bromsgrove, Worcestershire, United Kingdom.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default Subform on many to many data

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
 
Old August 11th, 2005, 08:25 AM
Authorized User
 
Join Date: Jun 2004
Location: , , USA.
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.........."
 
Old August 11th, 2005, 08:30 AM
Authorized User
 
Join Date: Jun 2003
Location: Bromsgrove, Worcestershire, United Kingdom.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Wish I could draw it.

Andy
 
Old August 11th, 2005, 08:39 AM
Authorized User
 
Join Date: Jun 2004
Location: , , USA.
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

are you using VBA at all?

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."
 
Old August 11th, 2005, 08:44 AM
Authorized User
 
Join Date: Jun 2003
Location: Bromsgrove, Worcestershire, United Kingdom.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 11th, 2005, 08:55 AM
Authorized User
 
Join Date: Jun 2004
Location: , , USA.
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.........."
 
Old August 11th, 2005, 09:08 AM
Authorized User
 
Join Date: Jun 2003
Location: Bromsgrove, Worcestershire, United Kingdom.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 11th, 2005, 09:15 AM
Authorized User
 
Join Date: Jun 2004
Location: , , USA.
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Julio Cesar

-"Bueller.......... Bueller.......... Bueller.......... Bueller.........."
 
Old August 11th, 2005, 09:21 AM
Authorized User
 
Join Date: Jun 2003
Location: Bromsgrove, Worcestershire, United Kingdom.
Posts: 59
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old August 11th, 2005, 09:26 AM
Authorized User
 
Join Date: Jun 2004
Location: , , USA.
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

the subform is being fed by tblUser2Role right?

Julio Cesar

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Navigating data in a subform Bob Pierce Access VBA 2 February 1st, 2007 10:58 AM
count data from subform Vince_421 Access 1 May 27th, 2006 11:51 AM
Subform not displaying data hikinfool Access 8 February 24th, 2006 06:11 PM
subform data in report exhibitor Access 4 May 16th, 2005 12:19 PM
Display data in a subform marcin2k Access VBA 1 March 1st, 2005 04:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.