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

[Stuart McLachlan]

Not sure I follow you, but it seems to me that you are trying to select multiple samples and 
analytes.  Without nowing the underlying data structures, it's hard to say what the best 
approach is, but this seems feasible:

 Instead of the "sample + analyte" and "report" list, why not just have three lists, The first  two 
(samples and analytes) would be selectable.  The "report" list would be display only and show 
the currently selected sample/testcode pairs. . When you select/ deselect an item in either 
the sample or analyte list, requery the "report" list.


On 21 Feb 2018 at 15:36, Ryan W wrote:

> 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?
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>