Moving Data between 2 listboxes

[rdurkin]

Hello,



I have having some problems with Access 2002. I have a form with 2
listboxes. Both have values populated from queries, and are also bound
using the foreign key of the forms recordset. I need to move values
between the 2 lists using command buttons by adding or removing the "key
value" for the record(in the listbox query). Basically I'm
linking/unlinking the records in the list boxes to the current record of
the parent form.



So far I have sucessfully created the listboxes and well as the queries
to populate them. I have started to write the code to move the data.
For example. item is seleced in listbox1; command1 is clicked;
listbox1.value(bound control) is then updated with a value that will
eliminate it from listbox1 and add it to listbox2 based on the queries
that populate the 2 listboxes. I need the click event to update the
"foreign key" of the selected record to match the primary key of the
parent form.



I planned to use the requery method to repopulate the listboxes at the
end of the command1_click event, thus updating the users view.



I have used some message box prompts and the VB debug watch window to
monitor the updating of the value I need to change, everything looks ok.
I can't seem to find a way to save the change. Is there and update or
save method for a listbox? That would probably do the trick.



One more thing. The queries are all dynaset with no locks.



Any hints or help would be greatly appreciated. Thanks in advance!



rdurkin

 

[Kelvin]

Eveything you have seems fine. The only thing is the Requery command. If
you requery the whole form (Me.Requery) the listboxes don't get updated.
This only updates the record source of the form. You need to specifically
requery each listbox.

To update the foreign key use an update statement like

UPDATE tblList SET tblList.[foreignKey] = Me.[PrimaryKey] where
tblList.[ListID]=Me.ListBox1

Good luck.

Kelvin