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

Wed Feb 21 17:22:36 CST 2018

Let's confirm the terminology, it will help :)

How does the user input data.  Directly into a table, or through a form.  If a form, what is the 
data source of that form.

Are "these two tables" as shon in your linked image, actual tables. If so are they temporary 
tables created for this prupose or are they the actual primary data tables.

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

suggests that you shoulld definitely be just picking Sample and Testcode/Analyte and using 
those two parameters to select the appropriate data for reporting.

I hope that Run, Sample and TestCodes are three separate related tables in your database :)

On 21 Feb 2018 at 17:03, Ryan W wrote:

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