Many-to-Many Tables, Forms, Subforms, and ComboBoxes

Okay, I desperately need some help. here's my set-up:

I have a full table of Members in my organization, and I have a table of Seminars. Multiple members attend any given seminar, and each member may attend multiple seminars. I have those tabled linked through a table called linkMembersToSeminars. The keys are MemberID, SeminarID, and ID, respectively.

What I want is a form of Seminars with a subform for Participants and I want to be able to look up members from my Members table to enter into that form/subform as new Seminar Participants and thereby update my linkMembersToSeminars table.

Up to now I've been doing that by creating a Query that simply includes Members' info together with the corresponding Seminar info. From that query I create my form/subform.

All of that is fine and I have a beautiful form/subform. But I can't enter new Seminar Participants into the form 'cause I'm having trouble making a functional ComboBox. My subform of participants right now has ID (from the linkMembersToSeminars table) and then Member info (Last Name, First Name, etc). And I have the Last Name field as a ComboBox.

The ComboBox succesfully opens a list of Members ordered by Last Name but when I try to choose a member I get an error message that says:
The field cannot be updated because another user or process has locked the corresponding record or table. (Error 3164)

But everything else is closed and I can't for the life of me figure out the problem. I want to be able to just type in a last name or scroll through the list and have that member be added to the list of Participants for that Seminar.

Thoughts?

Hi and welcome to the forum

Take a look at the attached example.

Points to note:

The main form is based on tblSeminars

The subform is based on tblAttendance (in your case the link table, if prefer to name my link tables something relating to the physical world - just my preference).

The combo in the subform is based on tblMembers (two columns with the first column being the bound column but set to zero column width).

hth
Chris

In a many-to-many situation you have three distinct objects: a tangible object A and a tangible object B, but also an abstract object C that represent the relationship between the two. This is your link table and it is this table, this abstract object, that your subform must modify. If members received grades at seminars this data would go in this linked table, since this would be an attribute of the relationship between a seminar and a member. See it?

So, on the seminar form the participants subform needs to have ONLY the link table as the record source, needs to be linked to the main form on seminarID, and the subform combo's bound field needs to modify the memberID.
That combo's rowsource needs to independently query the members table returning the memberID as the (hidden) bound field, and probably a concatenation of first and last names of the members, so you have some meaningful data to select from.

Does that make sense?

Ya, what Chris said.

Thanks for both of your help! It was super helpful and my form works great. I now have just smaller questions. For one, in my Participants sub-form, I have my MemberID combo-box ordered by LastName so I can scroll through but what I'd really like to do is to be able to either scroll or type the LastName. I tried to make my LastName field into a combo-box with two fields: MemberID and LastName with the BoundColumn set to 2, but somehow it turns all the LastNames into the MemberID numbers. Ideas?

Also, I've been creating forms with the form wizard but after that is done I can't figure out how to change certain things about the form, like which fields it includes or even if the layout is tabular vs datasheet. Once a form is created is it still possible to change these things?

Thanks again!