Applying a filter of unknown elements using array. Or hiding select records from user

Question

Using a split database, everyone gets a front end with a local table I use as a 'cart' like in online shopping.

I'm copying records to a local table from stock. I don't want the record I copied across to be allowed to be transferred over again making duplicates. I also don't want to delete the original record, just modify it.

So I want them to edit the records copy locally then hit a button that will update the record on the database back end. If they don't hit the button and close the front end, no changes are made. Assume the temp table is wiped on start up.

To stop duplicate records I want to hide select records from the particular user of the front end database only. So if the Access app crashes the record isn't hidden for all users.

Idea: What If I add a Stock_ID (hidden) field to the local table? Then I can poll the column and if any Stock_ID matches the ID of the record I want to copy a message box says Error, record already exists and cancels the record copy?

Answer 1

I think you're saying you want to show the front end user only those stock records whose Stock_ID values are not present in the local table.

If that is correct, you can use an "unmatched query" to display those stock records.

SELECT s.*
FROM
    stock AS s
    LEFT JOIN [local] AS l
    ON s.Stock_ID = l.Stock_ID
WHERE l.Stock_ID Is Null;

The Access query designer has a query wizard for this task. It should be worth a look.