Help with form - subform with many to many relationship

[sailorguy]

Hello there! First time posting, long time lurker. But I've gotten too frustrated to continue.

I have started a test db with just the bare minimum to try to narrow down my problem and it still happens:

3 tables (many to many)
Tbl_Groups
GroupID (PK autonumber)
GroupName
GroupType
GroupDescription


Tbl_Users
UserID (PK autonumber)
LastName
FirstName
UserName


Tbl_Junction_UserGroup
UserGroupID (PK) Autonumber
UserID
GroupID

I created a query as follows:

SELECT Tbl_Users.LastName, Tbl_Groups.GroupName, Tbl_Groups.GroupType, Tbl_Groups.GroupDescription, Tbl_Junction_UserGroup.UserGroupId, Tbl_Users.FirstName, Tbl_Users.UserName
FROM Tbl_Users INNER JOIN (Tbl_Groups INNER JOIN Tbl_Junction_UserGroup ON Tbl_Groups.GroupID = Tbl_Junction_UserGroup.GroupId) ON Tbl_Users.UserID = Tbl_Junction_UserGroup.UserId;

Now I created a form and subform. The form record source is Tbl_Users (to show each user record).
The subform SHOULD show me the groups that each User is a member of. The subform has a record source

SELECT Tbl_Groups.GroupName, Tbl_Groups.GroupType, Tbl_Groups.GroupDescription, Tbl_Junction_UserGroup.UserGroupId, Tbl_Junction_UserGroup.UserId FROM Tbl_Groups INNER JOIN Tbl_Junction_UserGroup ON Tbl_Groups.GroupID=Tbl_Junction_UserGroup.GroupId;

with a combo box Row Source

SELECT GroupId, GroupName, GroupType FROM Tbl_Groups ORDER BY GroupName;

This DOES show current group membership, but if I use the combo box to try to add a record (i.e. add the user to another group) I get the following error.

"Control Can't be edited it's bound to AutoNumber field "UserGroupID".

I've tried everything I can think of with no success. I don't no where my problem is...form, subform, combo box, query. Can anyone help? I looked at a sample db from here that appears to do the same thing (with albums and artist) and it appears to work fine.

Thanks!!!

 

[boblarson]

Sounds like just what you said - your combo box is bound to an autonumber field. Remove that binding and you probably will be good to go.

 

[sailorguy]

When I remove the UserGroupID from the control source of the combobox, the query no longer works to show current group membership record. I want each current group membership to be listed in datasheet view(this works), with the combo box on the UserGroupID field which will show the three fields together to let you choose the group you want. (this doesn't work).

 

[sailorguy]

Does anybody else have any ideas?

 

[boblarson]

Something doesn't sound right - can you post a copy of your database (see here for how: http://www.access-programmers.co.uk/forums/showthread.php?t=140587)

 

[sailorguy]

I have attached the database. There are only the 3 relevant tables, 1 query, and form, subform.

You can see what I mean by opening the form and use the combobox on the subform.

I really appreciate all the help!

Thanks

 

[sailorguy]

Anyone have an idea?