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

[Ryan W]

Well that's "more or less" what's happening here.. The user inputs some
data.. and presses "get data" and it fills in these two tables. They then
select 4 samples containing the same TestCode.  The tables are linked by a
Sequence (auto number). So each sample has a sequence, and many analytes
have that same sequence number (PK/FK relationship). Then they match the
report list for the 4 samples, which I'm attempting to 'synchronize' rather
unsuccessfully depending on use.

As I said, right now the logic to select or deselect the same analyte
across the 3 remaining selected samples is spotty if you fix one Sample
list and then attempt to "mass update" the check box list by pasting the
column back over itself (thus triggering AfterUpdate X times, where X is
between 1 and probably 90 or more, depending on test complexity). The
longer the list the worse it gets at matching the list up.

Each sample has to have the same equal amount of analytes selected because
the transform/pivot query goes sideways if data is missing.

On Wed, Feb 21, 2018 at 4:44 PM, Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

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