[7omislav]

First, Happy New Year to all of you

[OBP]

Happy New Year to you too.
What do you want the Subform to do?

[7omislav]

I have some spare time to redesign database for my club and seems that I have stumbled to a wall here..

OK, what i have is a Competitors table and a Competitions table, with the third table of Results, which holds the CompetitorID, CompetitionID and Position.
The Relationships between the Tables is "1 to Many" from the Competitor to the Results table and a "1 to Many" from the Competition to the Results Table.

I need to make a Form to enter competition details with subform where I can choose competitors and enter results.

I also need to make it do the math (I got the answer to that he
Lets say, for example, Jack has won the gold medal in State championship and he also won 4 matches
7(for gold) + 4*1(for victories) = 11 points
(I got the answer to that here a few months ago but started to implement it just now)

With forms wizard I just got subform that prompts me to enter names.
I already have Competitors table full with names and want to choose from existing names and not to enter same names every time...

[OBP]

Ok, use a Query based on your Results Table as the Record Source for your Subform.
Now add a Combo Box based on your Competitors Table (or better still a Query of it) and set it's Control Source to the CompetitorID on your Form.
Now do the same for Competitions set it's Control Source to the CompetitionID.
This will allow you to just Choose data from the other tables to go in to this Form/Table.
You can manipulate the SQL of the Combos to show Competitior Names and Competition Names instead of the IDs by setting the ID columns width to Zero cms.

[7omislav]

I have been messing with the database from our previous discussion here and 'Competition Detail' form is the problem for me

[OBP]

Ok, I have it, You have the main form as Competitions. So that will currently take care of the CompetitionID on the Subform, so you really need the Competitions Combo on the MainForm, so that you can "Find a Competition".

[7omislav]

Yes, I know - so I converted it to 2003 format

[OBP]

OK, I can't get it to do what you need, it keeps crashing.
This is what it needs.
1. On the Subform, delete the Competitions Combo (that is the part I can't do.
2. On the Main/Sub Forms reset the Master Child Links from ID to ID to ID to ID_Competition. (This part I can do.)
3. Add a "Find a Matching Record on my Form " type Combo on to the mainform. (I can do, but there are no records to test it with).

[7omislav]

I have done 1. and 2.
'Competition Results' subform isn't doing the math - I think the VBA code behind it needs tweaking

[OBP]

I will have to get back to you on this tomorrrow, it is my Wife's turn to use the Computer now.

[OBP]

You don't have any Column 2,3,4 or 5 in Combo10.
You have moved it, so it needs to be
me.parent.combo10

[OBP]

Is this version doing what you want?

[7omislav]

We are getting close, I mean - you are getting close - thanks for the help
Scoring was off in your version and I changed it, so I think it is OK now.
What should I do to prevent selecting same competitor twice in Combo12 ?

[OBP]

That is a little tricky, I use another field in the Many to Many Table which combines both the CompetitiionID and the CompetitorID and then set it to Indexed with No Duplicates.
This allows "many" versions of the Competition and Competitor, but only one of them together.
I use VBA to combine them in a hidden field on the subform

[OBP]

Like this.