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

Stuart McLachlan stuart at lexacorp.com.pg
Wed Feb 21 19:10:51 CST 2018


I'm getting confused. What is the difference between an Analyte and a Test?

It seems to me that you want to end up with a grid of

Results for Run x,  Test Codes "abc","xyz"

sample     ABC     XYZ
Sample1     1.2      27
Sample2     1.5      36
Sample3     1.4      24
 

Do I have that right?









On 21 Feb 2018 at 17:33, Ryan Wehler wrote:

> What you´re seeing are temp tables based closely on real tables. 
> 
> I am not sure I follow on what you are suggesting. We are picking the
> samples which are related to a specific test (thus picking that test
> already) and then selecting the analyte list for one sample (at a
> time, since my code isn´t reliable for this yet)
> 
> 
> 
> 
> 
> > On Feb 21, 2018, at 5:22 PM, Stuart McLachlan
> > <stuart at lexacorp.com.pg> wrote:
> > 
> > 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
> >> 
> > 
> > 
> > -- 
> > 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





More information about the AccessD mailing list