Continuous form - how to display info from tables with one to many relationships

[RCurtin]

Hi everyone,
I've tried several different things to get around this problem and have found out a lot about what won't work!

Basically I am building a HR database. The main table is M_CandidateDetails and the primary key is CandidateID. This is a foreign key in all of the other tables: MCandidateEducation, MCandidateEquipExp, MCandidateSector. (I've attached a screenshot of the relationships.)

Goal:
What I want to do is create a continuous form with one record for each candidate. A record will show all of the info pertaining to a candidate. The form is to be used to for searching and filtering candidates.

Attempts..
First I tried creating a query to combine all of the information. (I've attached a screenshot of the query). The only problem with that was if joe bloggs had worked in 3 different industry sectors there would be 3 records for him. (I did have problems with the query not being updatable - solved that though by using the Dynaset property of the form)

Next I tried basing the form only on the M_Candidates table and using listboxes to display the information from the many-side of the various tables. But that didn't work as you cannot use an unbound control on a continuous form.

Then I tried putting in subforms instead of listboxes but you can't use subforms on a continuous form.

I'm out of ideas now- can anyone give any tips?

 

[RuralGuy]

I would think that using ComboBoxes rather than SubForms would work for a search form.

 

[RCurtin]

Thanks for your reply RG.
I did try using list boxes but it didn't work because they weren't bound. How would I get around that problem if I used comboboxes?

 

[RuralGuy]

You are correct that unbound ComboBoxes would not work well on a continuous form. You may wish to rethink the design of this particular function.

 

[RCurtin]

Thanks for that RG. I had come to that conclusion myself but am glad that you posted back so I can rule it out for definite.

What I've done now is changed it so that only information from tables with one to one relationships are shown on the search form - e.g. comments table and CV table.

Then the user can double click on any record to see the full information on a particular candidate. I have set it up that the user can still search for candidates that has experience with particular equipment for instance by changing the underlying record source of the form depending on the items they select in a combo box at the top. (I've attached a screenshot)

Am quite happy with it now! Thanks for the help

 

[RuralGuy]

Excellent! Thanks for posting back with your success.