Form based on many to many relationship

[Anita]

I have a database containing 3 tables

events
contacts
and a junction table

I have created a many to many relationship between these tables - one
contact may attend many events OR one event may attract many contacts - I
want to be able to look at either or.

I then create a query to show contacts in the first column and the event
they have attended in the second column, and then another query that shows
events in the first column and contacts in the second column.

However, when I create a form based on the query, and try to add a new
record in the subform, it says 'field cannot be updated' - what am I doing
wrong? the queries seem to work fine.

Thanks

Anita

 

[Ken Snell \(MVP\)]

Your form should be based ONLY on the junction table. Use queries as the Row
Source for combo boxes to allow you to select values for the events and the
contacts fields, where the events combo box's Row Source query is based ONLY
on the events table, and the contacts combo box's Row Source query is based
ONLY on the contacts table.

 

[Anita]

Hi Ken - thanks for your speedy response.

I've created a new form based on the junction table but not sure how to get
the other fields I need to display - I've created a combo box using the combo
box wizard button from the toolbox and changed the rowsource as you suggested
(although I have only selected the query from the drop down list, not sure if
I have to add any thing else to this?) - that does allow me to choose an
event from the list but the event id displayed on the form from the junction
table doesn't change when I select a different event which I would have
expected? - Anyway, presuming I have the event I want selected from the combo
box, how do I get the form to display the 'many' contacts in a tabular
subform that have attended that event?

Also - do I have to use a combo box for the event field? as I may have
hundreds of events in the list and it may take a while to scroll through them
all - I was thinking along the lines of a form that would show the first
event from the query and then use the 'Find' button to go to the one I want,
or the New Record button to add a new one - hope that all makes sense.

Thanks again

Anita

 

[Ken Snell \(MVP\)]

To assist me in fully understanding the setup that you are wanting to use,
please describe the type and location of controls on the form. It sounds as
if you might want to use a combo box for the event in the form's FormHeader
to filter the form's data to the selected event, and then have the form
display all the contacts associated with that event? Is this what you're
seeking to do?

 

[Anita]

Yes - that is what I want to do! Still trying to work it out!

i.e another example would be books and authors - show an author in the
parent form and books in the subform and vice versa.

Hope you can help.

 

[Steve Schapel]

Anita,

Not sure if this will help, in a non-specific way, but you might find
this article of interest.
http://accesstips.datamanagementsolutions.biz/many.htm

So, using your books and authors example, you could have a main form
based on the Authors table, and a subform based on a query that includes
both the Books and Authorships tables. Or you could have a main form
based on the Books table, with a subform based on a query that includes
Authors and Authorships tables.

 

[Ken Snell \(MVP\)]

Steve has given one suggestion similar to what I was originally considering.

The other method is to have the form be based on the junction table, and use
an unbound combo box in the form's FormHeader section. You'd use the combo
box to select the desired event for which you want to display its
information on the form.

What you can do is use an SQL query similar to this as the main form's
RecordSource (this is generic, so it would need to be changed to conform to
your actual control and field names):

SELECT * FROM YourJunctionTable
WHERE EventID = Forms!NameOfYourForm!EventsComboBoxName.Value;

Then, you'd put controls on the form, in its Detail section, that are bound
to the fields in the YourJunctionTable. NOTE: The control that is bound to
the EventsID field should not be Visible on the form; set its Visible
property to No.

Then, use the combo box's AfterUpdate event to requery the form's record
source:

Private Sub EventsComboBoxName_AfterUpdate()
Me.Requery
End Sub


--

Ken Snell
<MS ACCESS MVP>