How can I accomplish this task?

[Guest]

I have a form called "frmdata" that has a "commoditynum" field on top and two
subforms - "sfrmrsoavail" (representing all Available RSO records) and
"sfrmrsoselected" (representing all Selected RSO records) for a particular
commoditynumber on top of this form.

I have two tables - tblrsoavail (that stores all RSO Available records) and
tblrsoselect (that stores all selected RSO records for a particular commodity
number)

Recorsource of the main form frmdata is tblselectrso
Recordsource of subform "sfrmrsoavail" is qryrsoavail and recordsource of
subform "sfrmrsoselected"

Here are the Business Requirements:

1. For every commoditynumber on this form, I should be able to Add RSO
Records from Left to Right. I should also be able to Remove RSO Records from
the Selected records on the Right.

2. If I open the table "tblrsoavail" and delete any record from it (for
instance "rso2" then when I open "frmdata" I should not see rso2 in the
Available List. Also, all records in frmdata that had rso2 selected should be
removed automatically.

3. If I Edit any of the records in "tblrsoavail" then "tblrsoselected"
should automatically get changed with the edited values. For instance, If I
change the value of "rso1" to "test" in "tblrsoavail" then when I open
"frmdata", the subform "sfrmrsoavail" should reflect this change AND the
subform "sfrmrsoselected" should also reflect this change for the records
where "rso1" is selected.

Here is a less confusing version of my problem:

Initially, I thought I can accomplish this using 2 listboxes - One on the
left that shows all the available items and the one on the right that shows
all the selected items and a button to add, delete and clear. However, what
puzzles me is if I delete one of the items from the available items and if
that item was selected on the selected items before for that commodity number
- It should be automatically deleted now.

Is this possible at all? Can any expert here help me find a solution to
this problem?

 

[Guest]

Thanks! Can you email me your version to (e-mail address removed) please?
Maybe I can use the logic you have in my application. I will look forward
to your mail.
Thanks!

 

[(PeteCresswell)]

Per vrk1:

Is this possible at all? Can any expert here help me find a solution to
this problem?

I'm no expert, but when I do stuff like this I use just one table and flip a
"Selected" boolean back and forth to make records appear on the "Available" or
"Selected" sides of the form.

Two list boxes do the job for me.

I provide "Select" and "Remove" buttons, but also put code in the DoubleClick
and KeyDown events.

If you really get into the behavior of the form, the code gets kind of
complicated - albeit repetitious.

I tend to base my forms on work tables in C:\Temp and then blow the changes back
up to the DB if/when the user clicks a "Save" button.

I can email you a playpen version if you want.