Implementing Assign/Unassign in Access Form

[whdyck]

I'm using MS Access 2003.

I need to implement an Assign/Unassign form in Access, and I'm wondering how to do it.

I have a Master/Detail situation where the Master table contains an Order record, one column of which stores the number of railcars of a particular commodity ordered. Later the Detail railcar records come in via EDI (one record per car), and they then need to be assigned to the relevant Order record. (They cannot be created at time of Order creation because they contain data that cannot be known at time of Order creation.)

So I'm thinking I need an almost typical Master/Detail Form/Subform where the subform contains the Detail railcars that have been assigned to the Order. But below the assigned subform, I need an "Available" form that lists all Detail railcars that I could yet assign to the Order. Both the Available and Assigned forms would also have a checkbox on each record and a button used to move the Detail railcar records back and forth between the Assigned and Available lists. When ready, I'd like the user to be able to click "Save" to populate/unpopulate the FK column Detail table for the affected records.

I know I need to build two queries:
Assigned: lists all detail records populated with FK to Order record
Unassigned: lists all detail records not having a FK to Order populated

But how do I build the interface that allows me to manage the movement of Detail records back and forth between Assigned and Unassigned before the actual Save occurs? Should I use recordsets?

Thanks for any help you can give.

Wayne