[AccessD] Is there a better way? (AfterUpdate event)

[Ryan W]

https://i.imgur.com/hhUUmWw.png (screenshot)

I've got this form where a user selects 4 samples from it on the left side

On the right side are the compounds associated with those samples, in this
example it's a very short list (1 compound).

In the "Rpt" checkbox on the right side I have code that when the box is
toggled, it updates the other 3 selected samples to match the toggle list.

Simple right?


Well imagine these 4 samples have a list of 60 analytes.  If you toggle
them one by one, it works beautifully. But not all the samples may have the
toggle list in the same state:

Eg: Sample 1 as "Silver" on, but Sample 2 has silver "Off".  If you don't
touch sample 1 for silver you still have to go fix it for Sample 2 because
you wanted silver on and it's not on across the board.

So what I was toying with is selecting the entire RPT column on the right,
and re-pasting your "corrected list" back into itself.

Unfortunately this doesn't work right.   The other 3 samples don't always
get the matching target list.  It sporadically leaves some toggled or
untoggled.

I imagine this is the local access db engine (local tables) not being able
to lock/unlock the table fast enough since the AfterUpdate code is executed
upwards of 60 times (if you copy and paste the entire column back in).


Right now this is using DoCmd.RunSQL but I've also tried using a DAO
recordset which is even worse. It tells me various items are not updatable
where at least RunSQL gets me 75% of the way there.


Is there another approach for this that might work better?  The only other
thing I have come up with is to have a Command Button that runs out and
updates the "final list" rather than have the user paste the column back
over itself.  It might work better like that since it would be a single
execute with a self join to match the target lists up.  But I really don't
want to go to a command button if I don't have to.

So.. I'm looking for ideas on what else I might be able to try?