[AccessD] Is there a better way? (AfterUpdate event)
Ryan W
wrwehler at gmail.com
Wed Feb 21 19:24:50 CST 2018
A test can be comprised of many analytes.
So if we test for metals we test for lead, copper, zinc, aluminum etc
Sample 1 aluminum 50
Sample 1 copper 300
Sample 1 zinc 0
So then repeat for sample 2,3,4 with different values.
Pivot on the 4 sample id names using the detected value as the result
Sent from my iPhone
> On Feb 21, 2018, at 7:10 PM, Stuart McLachlan <stuart at lexacorp.com.pg> wrote:
>
> 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
>
>
>
> --
> 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