Sub Form problem (1 Viewer)

[Just1]

Hi.

Have a many to many relationship with a junction table to join.
Events - of which there can be many,
Employers - of which there can be many.

I want a form to display an event and the employers involved in that event. And another to display an employer and the events they took part in. When I set up a form using Events table as main form and Employers as sub form, it doesn't seem to work. Am I missing doing something?

Anyone any ideas. Sorry if it seems obvious.
Ta
Just1

 

[missinglinq]

A look at your tables' current structures would be helpful.

 

[boblarson]

When I set up a form using Events table as main form and Employers as sub form, it doesn't seem to work. Am I missing doing something?

Yes, you should be setting it up using the JUNCTION table and just displaying the translated values of the ID's that you stored in the junction table from the two tables. That is, depending on whether you actually set it up right to begin with.

 

[Just1]

Thanks, used the Junction Table and it's working. Not sure how to get the translated values as I just have ID numbers from the two tables in the junction table, so I may need to come back to you if I can't figure that out!! (I'm a relative beginner)

Thanks again
Just1

 

[boblarson]

The controls on the form for the junction table, you would change to Combo boxes or list boxes (no multi-select). Then, by having their RowSource set to a query using only their applicable table, you could have the view of the data and have a way to select the correct option.

There is a way to change the text box to a combo really easily (you have to make some manual adjustments) by using FORMAT > CHANGE TO and selecting combo box. Then, you have to set the rowsource, the number of columns, and the column widths to make them viewable. If you run into problems post back.

 

[Just1]

Hi. Well I've given that a go and I'm getting somewhere - thanks. Just got a couple of problems. Difficult to explain, but here goes.

Junction Table contains EventIDNo and EmployerIDNo

Main form shows Employer Details - key field EmployerIDNo
Sub form needs to show Events linked to that employer, need to be able to select different events so shows all linked to employer.

Changed the controls to combo boxes no problem.
Created a query to show Junction Table's EventIDNo and all fields in Event Table

Then changed RowSource to that query.

I can now view the events details, but they are repeated in the combo list where they have been linked with more than one employer....

Does this make sense? Help please!

I also have two columns of course the same, one shows the EmployerIDNo from the Junction Table and one from the Employer Table.

Thanks

Just1

 

[Just1]

Sorry, that last paragraph I meant EventIDNo appears twice, not EmployerIDNo

 

[Just1]

Hi - don't worry, I've managed to sort it. Hoorah!
Thanks for advice. I'm sure I'll be posting for other help!

Just1