Re: If all tick boxes in an order = yes then me=complete? How do I

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Nikos Yannacopoulos (nyannacoREMOVETHISBIT_at_in.gr)
Date: 02/10/05 

Date: Thu, 10 Feb 2005 13:46:52 +0200

Richard,

I can be much more specific if you tell me what event you have decided
to use to trigger the update. Pls also provide as much details as
possible (table/field names, form/subform and control names if involved).

Nikos

Richard Horne wrote:
> Nikos - I think I actually need to store Order_Complete as a value in the
> database as I will have queries that actually query complete orders and thus
> I need a permanently stored record of which orders are complete.
>
> Your description below sounds plausible but I don't know how and where to
> implement DSum and DCount - my access skills haven't stretched quite THAT far
> yet.
>
> Could you elaborate further?
>
> "Nikos Yannacopoulos" wrote:
>
>
>>Richard,
>>
>>Strictly speaking you don't need to store it in the header table, since
>>it can be xtracted from data already stored in the detail table whenever
>>required. A quick and dirty check would be to DCount number of detail
>>records for a particular order, then DSum the Item_Complete field for
>>the same records, exploiting the fact that Access returns 0 for False,
>>-1 for True when a boolean field is used in a mathematical calculation
>>(thus the quick and dirty), sop if DCount = - DSum then the order is
>>complete.
>>
>>It may be argued though that it's worth adding a boolean field at header
>>level so querying/reporting is made simpler henceforth (and the check
>>can well be the same as above), but you have to decide on a mechanism to
>>update it (a query / piece of code run periodically, or when an order is
>>processed through a form, or what?), i.e. you have to decide on the
>>event that triggers the updating.
>>
>>HTH,
>>Nikos
>>
>>Richard Horne wrote:
>>
>>>Before I go on I just want to say a big thank you to all the people on the
>>>access forum who have helped me so far. A year ago I knew nothing about
>>>access now my skills have drastically improved and a large part of that is
>>>down the fantastic help I have had from people on here. So a big thank you to
>>>you all.
>>>
>>>Now to the matter at hand.
>>>
>>>I have created an order system which follows the classical structure of 2
>>>tables. Orders and Order_Details.
>>>Orders contains details of an order and Order_Details contains all the items
>>>in that order.
>>>Orders has a tick box called Order_Complete and each item linked to that
>>>order in Order_Details has a tick box called Item_Complete.
>>>
>>>I want Order_Complete to automatically become ticked when all the items in
>>>that order are complete. So at a basic level I need to code:
>>>
>>>For any one order, if Item_Complete = yes for all items then Order_Complete
>>>= Yes.
>>>
>>>So how do I code this? Do I store Order_Complete as a Yes/No field, or have
>>>it as an automatically generated text label on a form?
>>

Relevant Pages

  • Re: Killed by Triggers
    ... When I removed the trigger the error disappeared. ... The second situation is when a detail record was attempted, after the header ... If you don't get an error message, ... UPDATE TABLE01 SET CREATEDBY = CURRENT_USER, ...
    (microsoft.public.sqlserver.security)
  • Implementation design advice needed
    ... I have a system that stores data in a propietary format that has a number of ... external views and tables in order to transpose the data into client ... When data is updated according to the header table I need ... Change my BUS/AUS trigger logic and use a temp global table to store each ...
    (comp.databases.oracle.server)
  • Re: AfterUpdate?
    ... still buffered at the header level. ... the detail fields afterupdate triggered event's subsequent SELECT, ... Changing a control's Value via VBA code does not trigger any ...
    (microsoft.public.access.formscoding)
  • Re: Killed by Triggers
    ... If a trigger fails, it will rollback the operation that caused the trigger ... to the header table the error message was "String or Binary Data would be ... The second situation is when a detail record was attempted, ... UPDATE TABLE01 SET CREATEDBY = CURRENT_USER, ...
    (microsoft.public.sqlserver.security)
  • Re: Killed by Triggers
    ... CURRENT_USER is probably too long for my CREATEDBY field. ... MODIFYing and EXECUTing a disabled trigger enables it. ... Kalen Delaney, SQL Server MVP ... to the header table the error message was "String or Binary Data would be ...
    (microsoft.public.sqlserver.security)