limit contents of unbound control on subform

[Giles]

I can't seem to restrict the records in an unbound combo box control in a subform. The background:

I've built a contact database to track meetings with individuals from customer companies. Each company can have several individuals, each individual can go to several meetings and each meeting could involve individuals from more than one company.

I've used the following main structure (to avoid complicating things I'll leave out the bells and whistles):

Tables:
- Companies (primary key: CompanyID) - contains address details
- Individuals (primary key: IndividualID) - contains individuals' details including the company they work for
- Contact history (primary key: ContactID) - contains the date and subject of the meeting
- Individual-Event junction (primary keys: ContactID and IndividualID): identifies which individuals were present at each contact event

My main form is based on the Companies table. It contains a subform (in 'continuous forms' view) based on a select query, showing each contact event for that company. What I want it to do is to show the date and subject (no problem) of the meeting and a combo box identifying the individuals involved.

Here (at last) is my problem: how do I limit the combo box so that it shows only the individuals involved in the meeting? I've tried basing it on a query using the four tables above but the result is that it shows a whole bunch of people who don't work for the company and had nothing to do with the meeting.

I am, as you'll probably have guessed, painfully self-taught and confess that I don't fully understand how the structure of a query affects the result in a form. I'm assuming I should use a query as the row source for the combo box, in which case my question may resolve to, "How do I construct the combo-box query?" - but maybe I shouldn't even be using a query in the first place? I've tried modifying the query on which the subform is based but that causes other problems.

Um..... help!

 

[Pat Hartman]

A combo isn't the right tool to use to show the contact list. The problem with trying to use an unbound combo in a subform is that the RowSource can reflect the criteria of only a single record at a time. So, if you put a requery of the combo in the Current event of the subform, it should properly refresh as you click into a record. The query for the combo, needs to use selection criteria that refers to the key field on the subform.

Select ....
From ....
Where SomeField = Forms!YourMainForm!YourSubform.Form!SomeField;

Then in the subform's Current event:
Me.YourCbo.Requery

The combo will look funny to the users because the same value will always show for each row of the subform. A better solution would be another subform that is synchronized with the existing subform. The subforms would not be nested, they would appear side-by-side. When you click on a row in subformA, your code would requery subformB to synchronize it so that it shows only the data related to subformA.

 

[Giles]

Pat,

It looks like there's some sound advice in what you've said - it certainly makes sense! I particularly like the sound of the synchronised subforms, which may well get araound the problem that sparked all this off: the fact that I can't have a subform within a subform when it's set to "continuous forms" view.

I'll have a go at things along the lines you've suggested and see what happens - many thanks.