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

[Ryan Wehler]

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