Frustrating many-to-many form

[britesponge]

Hi,
I have three tables in a basic many-to-many setup in order to provide a register for some after school classes.

In this we have a SESSION that can be attended by many children and each CHILD can attend many sessions.

To support this I also have a CHILD_SESSION table containing two columns holding the IDs to the SESSION and CHILD tables.

I have a form where you select the SESSION you wish to register Child records on that has a combo box to select the relevant SESSION and a sub-form that shows the name of the child from the CHILD table where the CHILDID from the CHILD_SESSION table equals the CHILDID from the CHILD table.

All pretty straightforward I think even though Access sure doesn't make it easy.

My problem comes when I want to add another child to the list.
I have set a combo box on the Child name to show the names from the CHILD table so that one can be chosen to add.
However, what happens is that the record added does not show the ID of the CHILD record I chose instead it created a new child record and uses that.
I have tried this by creating the subform first, creating from a query and adding a sub form to the form and I just can't get this to work.
Im more familiar with doing this sort of thing in a 'proper' programming language (for me that is PL/SQL) but the way Access works is stumping me. So after too many tutorial videos and too much forum reading I'm asking the question: What is the correct way to add records to the intersection table while displaying a value from the CHILD table ?
I'm guessing that I could code all this as a VB application but apart from the fact that I don't know VB I also need it to be as Access-only as possible.
Any help would be appreciated.
Many Thanks
BriteSponge

 

[plog]

A form that directly allows edits to data (add/edit/delete) should be based on just one table. To implement a many to many table relationship you use a subform for the juction table (CHILD_SESSION table). So, you should have 3 forms:

Child form that allows you to enter child information into the child table.
Session form that allows you to enter session information into the session table.
ChildSession subform that allows you to enter childsession data into the CHILD_SESSION table.

Now, you can put the ChildSession subform on the Session form so that you can add children to a session. Or you can put the ChildSession subform on the child form so that you can add sessions to a child.

My guess is somewhere in all that you've created a query and based a form on that query. That is not the right way to allow forms to interact with data. 1 form, 1 table.

 

[MarlaC]

plog is right. Also, you need to ensure that your primary key from the child table is the bound column on the combo box where you select a child to add to a session and that the primary key from the session table is the bound column on the combo bx where you select a session to add a child to (assuming either one form for both actions or setting up both scenarios plog described with subforms).

To clarify, assuming tblChild has fields
childID
childFirstName
childLastName

and that you want both first and last name to display in the combo box but the childID to be written to the table, you set up your combo box cboChild with
ControlSource = foreign key field for child in CHILD_SESSIONS, we'll say csChildID
RowSource = Select childID, childFirstName, childLastName from tblChild;
Bound Column = 1
Column Count = 3
Column Widths = 0;1.5";1.5"