"Add all" from Combo Box to Table

[Guest]

I have a Combo Box that when a selection from the list, the data selected is
saved in the table that the form, holding the combo box, is based on (In my
example, it a city is selected and entered in the table then it will be
excluded from the combo box list). The Raw Source of the combo box is as
follow:

SELECT tblCity.ID, tblCity.City
FROM tblCity
WHERE (((tblCity.City) Not In (select city from tblRepCity)))
ORDER BY tblCity.City;

The After Update event of the combo box reads:

Private Sub ComCity_AfterUpdate()
Me.frmRepSubCitySub.Requery
Forms.frmRepSubCity.Requery
Me.Refresh
End Sub

How can I manage to add an "Add All" option from the combo box list so if
selected all the data listed in my combo box will store in my table instead
of have to make selections one-by-one? I have tried using several combination
of UNION but nothing is working. Feel free to re-write my SQL statement for
clarity.



Thank you,
Silvio

 

[Guest]

Take a look at

http://www.mvps.org/access/forms/frm0043.htm

Daniel

 

[Guest]

Daniel that method all it does for me is to place the word (All) in my tables
of names, it does not copy all the city names listed in my combo box. :-(

 

[Douglas J. Steele]

From the way you're describing your form, it sounds as though the combo box
is bound. That creates complications.

You'll need to put logic into the combo box's BeforeUpdate event to
determine whether or not you've selected the Add All entry. If you have,
you'll need to write SQL to copy the entries from tblCity to tblRepCity,
then set Cancel = True so that the Add All entry isn't added to tblRepCity.