Adding multiple criteria to record (1 Viewer)

[Malcy]

I need to be able to apply various specialisation criteria to a volunteer record. There are currently about 50 possible options but list will expand. Already have separated this particular aspect into tblVol, tblSpec and tblVolSpec so data is normalised.
I thought of continuous sub-form based on temporary table showing volunteer name (from tblVol) and all the specialisations (from tblSpec) with a tick box on each record so you could go through list and tick as needed, then append ticked records to tblVolSpec.
However when you tick one record all the records get ticked.
Am unwilling to manually create form with all existing specialisations since it will mean each time they add a new one they will need me to redo the form.
Not really had this issue before - anyone with any ideas?
Thanks and best wishes

Malcolm

 

[JamesMcS]

So you've got a subform based on a temporary table, and when you tick one record, they all get ticked? I'm assuming each record is different? How is the temporary table created?

 

[Malcy]

An append query adds all records from tblSpec and also the volunteer ID comes in from the form so each record in the temporary table has a volunteer ID, a spec ID and a yes/no field. This table (via a query) populates the sub form.
And yes, tick one they all get ticked even through there are say 50 different unique records (25 -10, 25-11, 15-12 etc)

 

[JamesMcS]

Can you upload it? I can't see how that can happen off the top of my head...

 

[spikepl]

If it is to id what specialisations the volunteers have, and only happens occasionally, that means that you want to attach mulitple paramaters to eash single person, and for that you could have a list (perhaps listbox) displaying all volunteers, and a listbox with multiselect enabled, showing all the current specialisations of a given volunteer. A more fancy way of assigning this you can steal from one of the various wizards: two listboxes, when clicking on an item in one removes it, and ads it to the second listbox, which would then show the totalt list of specs for the selected volunteer so far.

 

[spikepl]

and to DISPLAY a concise overview list, if needed, you could concoct a crosstab query, with specialisations across and names down

 

[Malcy]

OK Thanks guys. Not sure I can upload since even my test data is sensitive.
Fundamentally when a volunteer is entered (usual contact details ++ etc) their areas of specialisation need to be recorded.
So I have a separate table holding fundamentally the Volunteer ID and the Specialisation ID as a sort of link table - very long narrow list of numbers linking the two.
I can then query that table to either ask what specialisations does volunteer 127 have or alternatively show me all the volunteers who have 29 listed as one of their specialisations.
The queries will be driven by seach forms and will not need crosstab stuff since forms showning lists of possibilities with button to show further detail is optimal solution for project.
I did not think this would be complex!!!!

 

[JamesMcS]

It's not - I'm just struggling to se how a subform based on a table would check ALL the records when you just click the one. Is the checkbox bound to the field in the underlying table? that might be the problem.

You're going about this the right way, by having separate tables for volunteers, specialities and a join table - I wouldn't go with the idea of having fields for each specialty in the volunteer table (if I read correctly above) as you'll be back to having the same issue of adding fields in whenever another one is added.

 

[spikepl]

ok so your issue is how to connect a volunteer with the various specializations. That's what a multiselect listbox is good for, or two listboxes as outlined previously

 

[JamesMcS]

I still think the subform idea is the way to go, bound to a table of volunteer numbers and specialty numbers.....

 

[spikepl]

SUbform seems overkill: One listbox from which to select a given volunteer, and one multichoice listbox to display/modify the selected volunteer's specs. That's it.

 

[Malcy]

Thanks guys
Found it. I had forgotten to bind the checkbox to the relevant field in the table. As soon as I did that then it started to behave as I expected!!
I guess I still need a few more coffees this Monday morning to kick brain into gear