Not so much a question as peer review:
I'm looking for an easier way to do this.
The current method I am using works.
I have a form with a combo box, two listboxes, seven command buttons
The form mimics the concept behind the query-wizard- Find Duplicates form where the available fields are listed in one box and the user does the old select and click thing.
In this case, the CBO allows the user to select an available system and in one list box there are available subparts (ones not already assigned) and in the other box the current system profile. I then have a command buttons to move selections right/left or move all right or left between the listboxes. A reset button to restore back to when the system was first selected, save, cancel, done...
The idea is that when assigning inventory to certain systems, the subpart is checked against a system profile before the entry is recorded into the history table assigning it to a given system. (I have to keep a history of where each inventory item is assigned from cradle to grave). [EDIT If the subpart is not part of the system profile, then the user is warned that this part is not allowed to be assigned to that system - supervisors and the DBA can override with their PIN for special cases.]
So this form helps the user easily create and maintain a system profile.
Here's what I have (amongst many other tables):
[tbl_subparts]
[tbl_systems]
[tbl_systemprofile]
[tbl_systemprofile]![PK]
[tbl_systemprofile]![FK_systems]
[tbl_systemprofile]![FK_subparts]
Form with a cbbox based on [tbl_systems] returns [tbl_systems]![PK]
event driven so that:
RS_CP = query([tbl_systemprofile]![FK_systems]= cbbox)
returns [tbl_systemprofile]![FK_subparts] and [tbl_subparts]![partname]
RS_AP = query([tbl_subparts] returns [tbl_subparts]![PK]<>[RS_CP]![FK_subparts])
returns [tbl_subparts]![PK] and [tbl_subparts]![partname]
In human terms, a list from [tbl_subparts] with the primary key and the part's name
lstBx_AP - will have available subparts
lstbx_CP - has the currently assigned subparts
subparts <> inventory
Once the recordsets are available, I then use the additem method to add the RS_AP to lstbx_ap, and of course RS_CP to lstbx_CP by stepping thru the records and additem([field_a],[field_b))
Now those four:
[>>] moves all lstbx_AP to Lstbx_CP
[<<] moves all lstbx_CP to Lstbx_AP
[>] moves (multi)-selected lstbx_AP to Lstbx_CP
[<] moves (multi)-selected lstbx_CP to Lstbx_AP
and logic to (en/dis)-able the save, reset, and sister buttons (nothing in lstbx_AP then [>] is disabled etc)
The save button, that's magic, user has to enter their pin to confirm the change then, I delete the records that have the current system in the profile table and then step thru the lstbx_cp and add new records based on the selected system and the lstbx_cp entry - the reset button is disabled at that point and both RS_CP and RS_AP are re-queried.
Reset, runs the code to read the records from RS_CP and RS_AP back into the list boxes
I think that's it.