Available/Selected list in access

[David Villalon]

Hi All,

I'm looking for help to build a particular type of control in an access db.

I'm sure you've all seen it. You have two columns of values. The one on
the left has available values (to be taken from table a), and the right
column has selected values. There are arrows in between to move the choices
back and forth to each side.

The purpose is for an order entry screen.

For example, I enter an order information, and I want to be able to see a
full list of about 50 products as "available", and add them to the order by
selecting them and clicking on the arrow.

Behind the scenes are three tables. Orders, Products, and a many-many
interseciton product-orders.

Any guidance appreciated.

 

[Jeff Boyce]

David

An order form, with OrderID field, a listbox on the left, a listbox on the
right, command buttons between.

The left listbox lists a "available" choices (but you have to decide if you
want to exclude any that are "selected") -- create a query against the list
of products (and you could use a "not in" join to the OrderedItems table,
where OrderID = the form's OrderID).

The right listbox contains Products in this Order (a query).

After each button click, you'll need to requery both listboxes to display
what's changed.

In the ">" (select this Product) code-behind-button, get the ProductID of
the Product in the left listbox that was highlighted and create a new record
in the OrderedItems table, using the OrderID from the form. You could use a
query that refers to the form, or write the SQL statement and run it.

In the "<" (de-select this Product) code-behind..., simply delete the
OrderedItem.

Good luck

Jeff Boyce
<Access MVP>