List Box question

[smadden]

I have a simple Access database with 4 tables so far. Here is my
question:

Talble 2 lists "vulns" and there descriptions. Each has its own
primary key and relates to table 1.
Table 3 lists "controls" and there descriptions. Each has its own
primary key. Many of each can relate to many in table 2.

Table 4 is a Merge Table, listing each primary key from table 2 and
its relation (primary key) from table 3.

I have a form in which you can type in the primary key (in the first
text box) from table 2. The next text box automatically provides the
description of this key. The next thing is a combo box to select the
record from table 3 which relates to it. (This populates the Merge
table.) However, if the key from table 2 has more than one record
from table 3, you must click the next button, and fill the values in
again with the additional record from table 3.
What I would like to do is have a list box that contains all possible
keys from table three, click which ones apply, (possibly visually
placing these keys in another list box), and have it populate the
Merge Table accordingly. Can anyone point me in the correct direction
if the question is clear enough? Thanks!

[rquintal]

On Jun 5, 11:12 am, smadden <[e-mail]:

I have a simple Access database with 4 tables so far. Here is my
question:

Talble 2 lists "vulns" and there descriptions. Each has its own
primary key and relates to table 1.
Table 3 lists "controls" and there descriptions. Each has its own
primary key. Many of each can relate to many in table 2.

Table 4 is a Merge Table, listing each primary key from table 2 and
its relation (primary key) from table 3.

I have a form in which you can type in the primary key (in the first
text box) from table 2. The next text box automatically provides the
description of this key. The next thing is a combo box to select the
record from table 3 which relates to it. (This populates the Merge
table.) However, if the key from table 2 has more than one record
from table 3, you must click the next button, and fill the values in
again with the additional record from table 3.
What I would like to do is have a list box that contains all possible
keys from table three, click which ones apply, (possibly visually
placing these keys in another list box), and have it populate the
Merge Table accordingly. Can anyone point me in the correct direction
if the question is clear enough? Thanks!

I would setup 2 listboxes, based on table 3, with inverted filter
criteria from a subquery that send the data to the correct listbox.

The first listbox contains table 3 records which are NOT in table 4 as
filtered by your table 2 combobox. The second listbox contains those
which are present in table 4. A Click on a row of the first listbox
fires an unsert query to add the table 2 and table 3 keys to a new
record in table 4. Then the click requeries listbox1 and listbox2.

Clicking in the listbox2 row runs a delete query on the table 4, again
with the keys from tables 2 and 3 as the criteria, then requeries the
listboxes.

It's not multiselect as you requested but it does work as fast as you
can click the desired items. That's probably faster than
multiselecting then clicking a DoIt command button.

HTH

[smadden]

On Jun 5, 9:36 am, "[e-mail]" <[e-mail]>
wrote:

On Jun 5, 11:12 am, smadden <[e-mail]:

I have a simple Access database with 4 tables so far. Here is my
question:

Talble 2 lists "vulns" and there descriptions. Each has its own
primary key and relates to table 1.
Table 3 lists "controls" and there descriptions. Each has its own
primary key. Many of each can relate to many in table 2.

Table 4 is a Merge Table, listing each primary key from table 2 and
its relation (primary key) from table 3.

I have a form in which you can type in the primary key (in the first
text box) from table 2. The next text box automatically provides the
description of this key. The next thing is a combo box to select the
record from table 3 which relates to it. (This populates the Merge
table.) However, if the key from table 2 has more than one record
from table 3, you must click the next button, and fill the values in
again with the additional record from table 3.
What I would like to do is have a list box that contains all possible
keys from table three, click which ones apply, (possibly visually
placing these keys in another list box), and have it populate the
Merge Table accordingly. Can anyone point me in the correct direction
if the question is clear enough? Thanks!

I would setup 2 listboxes, based on table 3, with inverted filter
criteria from a subquery that send the data to the correct listbox.

The first listbox contains table 3 records which are NOT in table 4 as
filtered by your table 2 combobox. The second listbox contains those
which are present in table 4. A Click on a row of the first listbox
fires an unsert query to add the table 2 and table 3 keys to a new
record in table 4. Then the click requeries listbox1 and listbox2.

Clicking in the listbox2 row runs a delete query on the table 4, again
with the keys from tables 2 and 3 as the criteria, then requeries the
listboxes.

It's not multiselect as you requested but it does work as fast as you
can click the desired items. That's probably faster than
multiselecting then clicking a DoIt command button.

HTH- Hide quoted text -

- Show quoted text -

thanks, I'll give it a try