Saving records from a from with multi-select listbox

[cmdecker2]

I'm learning. I want to save the values in a form to one table, however
there is a multiselect listbox to select multiple cities.
If I use some sort if data mapping to link Tbl1.ServiceID to Tbl2.ServiceID.
Tbl2 has the ServiceID and CityID only. There is another City table.
then I can pull reports on types of service in what cities.

How do I make the form populate Tbl1 data based on form control names, then
loop through the List box for selected Items and append to Tbl2 data. Is
there an easier way?

I understand Excel VBA, but Access is a whole new animal.

Thanks for the guidance!
Carl

 

[Allen Browne]

Is there an easier way?

Yes! Use a subform instead of a multi-select listbox.

The subform will be bound to Tbl2, so it only shows the records that relate
to the ServiceID in the main form. On the subform, you can use a combo box
to select the CityID (assuming you have a table of cities to act as the
RowSource for the combo). The user can then select a city in as many rows as
needed. No code needed.

If you want to go with the multi-select list box, you have lots of code to
write, populating the ItemsSelected collection of the list box in the
Current event of the form, handling each selection by looping through and
writing the concatenated list to a hidden control, and then handling the
Undo event of the form so you can restore the OldValue if the user undoes
the record. That is a considerable effort to get an inferior result.