Unanswered: Running Update Query On Selected Items Only
I have a problem and I am not returning much luck via google. Here goes...
I have a continuous form, which is based on a query, it displays a set of records, I then need to be able to tick a box on certain records and "mass update" the 'Costing Status' Columns of those selected values.
As this database will be used by multiple users at once the checkboxes cannot be bound to the record as this would be a problem with multi users. (Originally I put an extra field in my main table called "selected" and set the buttons to run an update query based on that, I didn't think about the impact of two users in the system).
Another theory was a list box however this presents a "presentation" issue as often our field names are longer than the columns and need to be wrapped over two lines.
I need to somehow tell an update query to run on a select few records.
1. You cannot use an unbound control on a continuous form/subform. If you do so, changing the value of this control in a row will also change its value in all other rows.
2. As your your application is used simultaneously by several users, I suppose that it is split with a back-end containing the data located on a server and that a copy of the front-end resides on every user's computer: this is the classical split access application.
3. If 2 hereabove is true, what you can do is import the values that must be presented in the continuous form in a local table that has an extra column ("Selected"). The checkbox that allows the users to select the rows that must be bulk-updated should be bound to that column. To update the original table, you can use an UPDATE query with a WHERE clause such as:
WHERE Selected = True
4. Remains the problem of a set of rows that would be used by several users at the same moment. To solve this problem, you could add an extra column in the original table (i.e. the one on the B-E). This table would be named "LockedBy" and would contain either NULL if the row is not in use or the user ID of who imported it in the local table of it's F-E. A row with a non NULL value in the "LockedBy" column would not be updatable nor in the B-E, nor in the F-E, except by the user who locked it. This would of course require the building of a complex mechanism to ensure the validity of the data but it's doable, mostly with queries.
5. A special UPDATE query would reset the "LockedBy" column for all rows. This query would only be accessible by the system administrator or a "superuser" and would only be used in very special circumstances. Normally the contents of the column would be updated to NULL when the corresponding row is released, i.e. when the user performs the bulk-update and empties the local table.