Use Combo Box in (Sub) Form to Update Table Field and Create new List box.

[Kennertoy]

I am a casual Access user with limited experience. I am (modifying) working on an existing DB (Access 2010). I have a data table/form per person requirement to modify. First to understand what I am doing. I have a table of information for volunteers (200+), - name, address, phone, email address, association of organization, etc., AND included in the table is volunteer events (25-50) to record each person's participation. On the form for each volunteer with basic info, I listed all events in a sub-form that has the event name, and a Yes/No radio button that updates the record for each volunteer when selected. That's the original view.



The data entry person (me) gets information on occasion that requires an update to the event participation for each person. This could be one event per person or many events. It would be nice to see (as above) all of the events that the person has volunteered for on the sub-form. (now you see all the events with the RB (yes) that the person volunteered over the year)

The first change I want is below.

Now each year the events change and I used to go in and change the sub-form events to show the latest. Now it's a lot of work. NOW, I would like to use an event table, and show with a Combo box on each persons record. I could just keep adding events in the table, as each year they don't always change but add additional events to volunteer. There would be a sub-form on the volunteer record input form (as before but new), that has a combo box with all the events. When the event(s) is selected (1 or many) then I would like to - (1) update the (participation) field of the event table, (2) create a new event list (box?) on the sub-form for each person record based on data selection of the combo box (1, 2 10 event selections) after any changes (+ or -) of events after the entry click.

I have made the event table, (event(txt), dates(date), participation(y/n), sub-form Combo box is showing events and with allowed simple selection (1,or,1+), but I cannot update the participation field and create a list based on the event selection. Do I have to use code?

Sorry for the long explanation, but I have noticed that the forum questioner sometimes is too brief, and the expert has to ask for more information to get a clearer picture. Not to say that I have been clear in my explanation!!!
Thanks for your time and information.

[June7]

Are you saying you want a combobox that allows selection of multiple items from the dropdown list? This is only for use with multi-value fields. I do not recommend multi-value fields.

Not quite sure what you want. Do you want combobox to show only those events that volunteer is already associated with? If so, how do you expect to associate volunteer with new events if the event is not listed in combobox?

[Kennertoy]

When selected I want to update the table field to participation to 'YES'. Then the LIST BOX on the same sub-form will automatically show the selected event. Ultimately shown for the volunteer that is shown.

[June7]

I don't understand the subform data source. Is this a table of records that associate each event with each volunteer? Or is this just a table of events with no associations to volunteers? The latter is not conventional data entry structure. Normally, the subform would be a list of events that have been associated with volunteer. Then a combobox would list all available events for selection to create a new record.

[Kennertoy]

Hi, Thanks for a reply,
I want the combo box to display only all the annual name of events (I created a table - event name, start date, participation fields). I have the combo box showing all of the events by name of the table field. When selected one or many events I want it to updates two things. 1) update the participation field of the table above, to 'yes', 2) Update a list box to show the volunteer has been registered with those events.

I have it working up to a point. If I go into the table and check the 'Participation' field of an event, then go back to the sub form where I have the combo and list box. the list box shows the event I manually checked. I just cannot figure out to update it from the selection of the event in the combo box.

Once I have each volunteer associated with the event table, each volunteer will show in the list box the events he/she has been registered including all changes made previously or as the volunteer form is updated.

[Kennertoy]

I thought I could use a sub-form on the volunteer form to manage the event participation. As I said in my first post I used a subform with all the annual events for the recording. It was not table driven, and each year I had to change each of the events manually. With my pee brain I thought I could put it all into a table. I would just keep adding events each year and they would be visible in a combo box for each volunteer. I have set a relationship one (volunteer table) to many (event table). So this is the first step in the overall process. As I explained in the reply above (Moderator), it is working partially. Can't I associate the Event sub-form (table), with the volunteer form (table)?

[June7]

Assuming volunteers could be involved with many events and each event will have many volunteers, this is a many-to-many relationship. Need 3 tables:

tblVolunteers
VolunteerID {primary key}

tblEvents
EventID {primary key}

tblEventVolunteers
VolunteerID {foreign key}
EventID {foreign key}

Then options for data entry are:

1. single form bound to tblEventVolunteers with comboboxes to select volunteers and events

2. main form bound to tblEvents and subform bound to tblEventVolunteers with a combobox to select volunteer

3. main form bound to tblVolunteers and subform bound to tblEventVolunteers with a combobox to select event

[Kennertoy]

Great thank you June 7,
I have set up the relationships and went with your option 3. I have the sub-form operational. I used a List Box because I don't want to allow any additions of events by anyone. (other than myself). The list box is showing the events based on a query of the events table. I have added another list box that I will populate based on the selection of event (?), for the volunteer (bound to tblvolunteers). I have added on the sub form a command button to confirm event selection(s) and write to the tblEventVolunteers.

As I understand using Primary keys and Foreign keys, the tblEventVoluteers will hold the pointers for the Event and the Volunteer. Does this sound OK? I guess I have to use code to write to tblEventvolunteer when the command button is clicked?
What would the data look like for these pointers for a selection of an event and a volunteer. I thought that since the ID of both the event and the volunteer would be stored in the tblEventVolunteer, so that if you ran a query it would just pick the data you code from tblVolunteer and tblEvent. Am I on the right track? Thanks again for your help to lead a novice along.

[June7]

No code is required for entering records into tblEventVolunteer. Record is automatically committed to table when form closes or move to another record or run code.

Sounds like on right track regarding query.

[Kennertoy]

Thanks again June7
I am forging on. I had the list box in the sub form and as we discussed. I had it populated from the tblevent/volunteer via a query. I had the records sorted in my event table by event. Now I could see the events in the list box fine when opening the form. BUT in the design mode - which I have to use the selected data to populate the tblevent/volunteer would have to be bound? So I re-created a new list box using control source and the box is bound now, but the list is not sorted alphabetically as it is in the tbl. Any ideas?

Thanks for help.

[June7]

Apply sort criteria in the listbox RowSource sql.

[Kennertoy]

Thanks again June7

I have tried that and still cannot get the sort. I am finding it's just a bit more than I can chew at this time. So I am going to modify the sub form/table as I described in the initial post. BUT it never rains it pours. I have to clear all the last years volunteer events to have a new years event participation. I thought it would be just run an update query. Ha Ha. I ran the query I have for each event for participation reports, and tried to run an update against the records that showed up from my select query - criteria = yes, to update to , 1) NULL, or 2) NO, and neither works. The select query shows the records ok but when I run the update nothing happens. If I run the select query and show the participation field I can change manually and update the table. Is it because it is a yes/no field? Any ideas.

Should I close the original thread and open a new one with this problem/help request?

Thanks again.

[June7]

I don't understand the issue. Sorting records in listbox is simple with WHERE clause in the listbox RowSource sql - same for a combobox.

Why do you need to delete records?

Yes/No field can be edited with UPDATE query.

UPDATE tablename SET fieldname=False;

[Kennertoy]

Hi June 7,
Still trying to get thru it. I used the following for sorting the List Box:

select from [tbl event]![Event] [Order by Event]

Does not work.

For Editing the Yes/No field - see attached pix's.


The update to does not delete the 'Yes' field to 'false'.

Thanks again for the help

[June7]

Syntax for RowSource sql is wrong, try: SELECT [Event] FROM [tbl event] ORDER BY [Event];

Use the query builder to get the correct structure. Click the ellipsis (...) in the RowSource property to open builder.

Update query works for me. Doesn't hurt but don't need the WHERE condition.