I have 5 users working on a report that displays items in stock in a warehouse. Users can search the stock items by item# and then tag one or more items in order to produce a report for those items.
The problem is that I don't want to put the item-selected column in the central table because then user selections would conflict with each other.
For example, Jim might select item ids 100, 200 and 300. Jane might also select item id 200, but would also see item ids 100 and 300 from Jim - which of course is unacceptable because report results would be unpredictable.
My first approach was, when the search for items screen first opens, I would build a temporary recordset, based on all the items from the original inventory table. Then, users could tag any items they wanted and when the report was ready to build, their selections would be local to just that user.
Problem with this method was the table contains a quarter million records and trying to copy the entire table takes too long. Also, the primary user of this application is overly sensitive to any search that takes more than 10-20 seconds to run.
My second approach was to put the selected items checkbox field in a local user table on each user's front-end database and then join this table to the inventory items table by the item id:
But using this approach is kind of strange because since I have to take all item ids from the left side, I end up with all null (grayed out) values in the checkboxes for the selected-items field on the right side.
Not only that but if a user selects an id and then deselects it, the selected-item field remains on the right-side table as a new record, even though it's a false value.
Bottom line is: I want a way for a user to see a list of real-time items from a table shared by multiple users and be able to tag one or more of those items independently from other users. And I need this list to not be constrained to only a small subset of records but allow the user to scroll the entire list if desired (even if that list is 250K+ records).
About the only other thing I can think of is to get partial recordsets, or pages, as the user scrolls and that way I can build a temporary table of selectable items apart from the main table (and other users).
What if you created a table in your front-end (FE) that has the InventoryID and a Selected Y/N field with the Selected Y/N field's default value No. Create a relationship from this table to the back-end (BE) table based upon InventoryID. Then have a delete query that deletes all of the records from the FE table. Create an append query in the FE that reads all InventoryIDs from the BE table and appends records to the FE table. Then the users form can have it's record source as the FE table where the user can make their selections. The report can be based upon a query that selects from the joined FE and BE tables and selects when the Selected Y/N field is set to Yes. You could put the aforementioned delete query and append query into a macro called autoexec which will run each time the FE DB is opened.