Utilizing Multi values in a form format

[gilinnc]

Hey everyone - I appreciate all of your input on my current issue.

Currently I have 3 Tables:

Table1_RawData contains collection of data
Table2_PgmData contains event information
Table3_EmpData contains employee data

I would like to create a form that will illustrate a record selected in the Table2, then pull Table3 employee name as a lookup / multi value check box to select each employee that is participating in the event from Table 2. Then click on "Add" button on the form to populate the event selected from Table2 and added a record to Table1, for each employee selected (Multi-Value check box) on the form, the event they attended.

The data entry person complains that it is time consuming to add an event to each employee individually whereas if a group of employees attended the same event - it would be great to select an event and pick from a list of employee to be added to the database (table1).

Is above possible - I am not able to gather any clues from Google Searches to come up with a solution.



This is a solution I needed last year...sorry folks for the delayed challenge here....

Gil

[ranman256]

use a tPicked table (it has the fields: ID, Pick (checkbox)
run delete query to empty this tbl
run an append query to add tEmpData to the tPicked
open form
the subform joins tPIcked table to the tEmpData table to see all names.
the picking subform will allow users to choose the records via checkbox.

when done, user clicks button,
this runs append query to add records checked in tPicked table to the final table

[gilinnc]

I will give it a try next week. Thanks.

[isladogs]

Or use a multi select listbox listing all employees together with a combobox for the events.
Select an event then all employees to be added to the event.
Use a button click to run an append query adding each employee to that event

[gilinnc]

Ridders52 (Colin) - thank you for your suggestions - your sounds a bit simpler to use. I will give both concept a try next week. The reason why I mention Multi-value check box is it is easier to place a check box than holding down the control key to select multiple names from the list box - or am I wrong?

Forgive me everyone that I am just beginning to understand some of the added features in Access 2010. If you throw me a DB2 system - I would fly! but ... I am grounded with MS products. Haven't got my wings yet...working on it...

Take care..

[isladogs]

Personally I don't like & never use anything involving multivalue fields and that includes multivalue checkboxes
I do use the multiselect listbox regularly and it works extremely well

Allen Browne's website has example code for using in such cases - see http://allenbrowne.com/ser-50.html

[June7]

AFAIK, combobox can only be mulit-select when it is bound to a multi-value field. Don't advise use of multi-value field.

So RawData is the junction table to relate employees with events? Then use a form/subform arrangement. Main form bound to PgmData and subform bound to RawData with a combobox to select employee in each new record. Don't see how selecting a single employee from a combobox dropdown (or typing into the box) to populate a single record is that much more work than 'clicking/selecting' from a multi-select control. And no code or pick table needed.