Selection list boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all-

I'm wanting to know if anyone has a good example of how to create Add/Remove
List Boxes like there are in a lot of the Windows products. I'd prefer not
to have to use temp tables if I can get away with it. I'm basically wanting
to all a list of users in one list box to be added/removed from an empty (or
populated with previous, stored selections) table on the right. I'm sure
this has had to of been asked before, just not having any luck finding good
examples. Thanks!
 
The answer will depend on your structure. Presumably you have a lookup table
containing all the possible values, and a junction table that contains the
chosen values. For example, the lookup table lists all the newsletters you
offer, and the junction table contains the newsletters the client has
requested. Then you want these list boxes to show on the Client form, so the
client can add or remove newsletters.

In the Current event of the Client form, set the RowSource of "yes" list box
to something like this:
"SELECT * FROM ClientList WHERE ClientID = " & Me.ClientID & ";"

To get the RowSource statement for the other list box, use the Unmatched
Query wizard to mock up an example. Basically it's an outer join, where the
foreign key is null.

Now, the Add button executes an Append query statement to add the record to
the junction table. The Remove buttons executes a Delete query statement to
remove the record from the junction table. In both cases, you need to
Requery both list boxes to make the change show up.
 
Back
Top