Developing applicaitons requires a bit of thought, and experience. Here is one scenario and the solution.
This sample application looks at how to allocate Items (Fixtures and Fittings) into the Rooms of a soon to be constructed Hotel. The intent being to compile purchase orders, and eventually distribute the physical items to their intended locations.
In this sample application, users can select Items then quickly allocate them to multiple rooms. In the application we have a three tables – Item, Room and Room Item.
And the Main Form (below) is bound to the Room table and the sub form to the Room Item table. The sub form has a check box to show the selected Items. This check box, as we will see, is NOT bound to a column in the Room Item table.
Typically the list of Rooms is provided by the Architect or Project Manager.
In the ideal process the Fitters would populate one room of each type, in this example – a King, a Queen and a Twin room and then, once approved by the client, clone the items to all other rooms. After that there may be the need to add or remove items.
It would be tempting to simply add a Select column to the Item table to allow users to select the multiple items but this will come undone if multiple users are working on the application at the same time.
To resolve this we need a separate table to temporarily record selections. As Maxwell Smart would say… this presents two possibilities.
A shared backend table with two columns – The Item ID and the User ID or a table in the frontend applicaiton that only needs a column for Item ID. In this example I show the latter.
The aSysItemSelect table is a permanent table in the application that temporarily holds records for the selected Items. I use the prefix “aSys” rather than “Usys” so that it can be seen without showing all system objects. Let’s call it an Application System table rather than a User System table.
How do we use this Item Select table? I hear you ask… Let’s now look at the Record Source behind the sub form.
Typically, this RS would have only one table – tblRoomItem. However to display the check box we just a Left Join to the Select table on the Item ID. This means we get all the Room Item records and, where there is a record in aSysItemSelect, the Item Select column will be true, if there is no Select records then this column returns False by handling the otherwise Null using the NZ function. Of course this means that the check box is not updateable so we need write some code.
Placed over the check box is a transparent button that fires code to either add or remove the record for the current Item ID from the aSys table, then requery the form and list box.
Once we have the list of Items, we can navigate to another Room and Paste them in. Either the entire list or, by holding down the Ctrl key, select multiple items in the list and paste just those.
Download the sample application and give it a try.
Note: you may need to remove the “Mark of the Web” attribute, after you unzip it, to enable the code.