Can I create an option group that allows multiple selections?

[NickyJay]

I have a field on one table, OrganisationInfo that links to the options from another table, TargetGroups, and I want the user to be able to select one or more option from this 2nd table, i.e. 16yrs, 17yrs, 18yrs, etc.

I also want to be able to run queries from the data entered, can anyone help?

 

[JeremyNYC]

Without knowing what your tables look like, we won't be able to tell you how to build your queries. But for the form, I would suggest using a frame around some check boxes. Radio buttons, which are what you normally find in an option group, indicate to users that they should only choose one option. Check boxes indicate that it's OK to choose more than one. Then you'll be able to check the value of each check box and build your sql accordingly.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[NickyJay]

The 2nd table, TargetGroups, has two fields of client ages an organisation deals with;

TGID TargetGroup
1 Aged 16
2 Aged 17
3 Aged 18
4 Ages 19 - 24
5 Ages 25 - 49
6 Aged 50+
7 All Ages

I want to put this as a subform on my main form, and the user to be able to put a check against all of the groups that apply to an organisation.

I've tried various other ways round this, including having a continuous subform on the main with dropdown menus for the Target Groups, however this method adds duplicates to the Target Groups table!

 

[KevinMoore]

It seems to me that you need a change at a structural level. Since you have a many-to-many relationship between Organizations and TargetGroups.

I would create another table called something like "OrganizationTargetGroups" with 2 fields, ORGID (or whatever your key to the org table is) and TGID. Developing querys that would return "All TargetGroups per Organization" or "All Organizations per TargetGroup" is then pretty easy -- You use all three tables in the queries.

Dealing with it on a form is a little more tricky, especially depending on how much "bulletproofing" you need. Probably the easiest way would be to use the sub-form wizard on the Organization form and reference the "OrganizationTargetGroups" table. You would link (in the wizard) using the ORGID, so the only field left to show on the sub-form would be the TGID. You could then make the TGID field a combo box using TargetGroups as its recordsource (and set the columnwidths correctly) so that it would show the targetgroup text (e.g. "Aged 16&quot.

I know this is only general help and hopefully I understood your problem, but maybe it will help point you in the right direction. Good Luck!

 

[NickyJay]

Hi Kevin,
Thanks for the info, I've tried it and it works so far - the only problem I have is not being able to change the TGID into a combo box and pulling the TargetGroups descriptions as recordsource.

When I try, access comes back with an error due to the property type (to link the tables the TGID is an autonumber on the TGtbl, and a number on the OrgTGtbl). Is there any way around this?

 

[KevinMoore]

I'm assuming that you're using a third table to link between the two (called OrgTGtbl), right? Make sure that in that table both of the fields (ORGID and TGID) are set as "Number" (Long Integer) and not autonumber. I would also make the primary key for that table the whole record (i.e. both fields) --- This will keep users from being able to assign an organization to the same target group more than once.

I do this kind of thing with Access all the time: It can be a real pain, but it can definitely work.