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

[Guest]

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?

 

[Nikos Yannacopoulos]

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

 

[Guest]

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]

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

 

[Guest]

Nikos -
Form Orders contains:

Order_ID
Customer_Name
Order_Date
Ship_Name
Ship_Address1,Ship_Address2,Ship_Address3,Ship_Address,4,Ship_Address5
Customers_Order_Number
Quotation_Number
Complete
Complete_Date
Extra_Info
Delivery_Terms
Despatch_By
Despatch_Date

Order_Details Containts:
Order_Items_Id
Orders_ID
Item_Number
Item_Type
Description
Type_Colour_Size
Quantity
Quantity_Delviered
Quantity_Remaining
Price
Denomination
NSN
Pattern
Delivery_Week_Commencing
Delivery_Note
Item_Total_Cost
Quantity_Despatched
Item_Complete
Deliver

I want Order_Complete in Orders to be ticked if all the items in the order
have Item_Complete = Yes.

 

[Nikos Yannacopoulos]

Richard,

You are still not clarifying which event you want to use for the update,
but from your answer it looks like you update order / details through a
form/ subform, so I would say the change of any order detail is a good
event for the job. In Access terms, we shall use the subform's
AfterUpdate event. Open the subform in design view, select the form
object (Edit > Select Form) and display the properties window; go to tab
Events, and put the cursor next to On AfterUpdate; click on the little
button that appears to the right, and select Code Builder. You will be
taken to the VBA screen, and the cursor will be between two lines (taht
Access puts there automatically) which read:

Private Sub Form_AfterUpdate()

End Sub

Paste the following code between them, where the cursor is:

strCriteria1 = "[Orders_ID]=" & Me.Orders_ID
cnt = DCount("[Item_Complete]", "Order_Details", strCriteria1)
icpl = -DSum("[Item_Complete]", "Order_Details", strCriteria1)
If icpl = cnt Then
ocpl = True
dcpl = Date
Else
ocpl = False
dcpl = "Null"
End If
strCriteria2 = "[Order_ID]=" & Me.Orders_ID
strSQL = "UPDATE Orders SET Complete = " & ocpl & ", Complete_Date = "
& dcpl
strSQL = strSQL & " WHERE " & strCriteria2
CurrentDb.Execute strSQL
Me.Parent.Refresh

Return to the form design window, save and exit. It should be OK now.

Notes:
1. In lack of better information, I have assumed that the names of the
fields in the tabkles are the same as the names of the controls on the
form / subform.
I have assumed Order_ID to be numeric; code will need slight changes if
text.
2. In your post you say the ID field is called Order_ID in the Orders
table, but Orders_ID in the Order_Details table. In case this is just a
typo and they are both called Order_ID (as I would expect), use the
following code instead:

strCriteria = "[Order_ID]=" & Me.Order_ID
cnt = DCount("[Item_Complete]", "Order_Details", strCriteria)
icpl = -DSum("[Item_Complete]", "Order_Details", strCriteria)
If icpl = cnt Then
ocpl = True
dcpl = Date
Else
ocpl = False
dcpl = "Null"
End If
strSQL = "UPDATE Orders SET Complete = " & ocpl & ", Complete_Date = "
& dcpl
strSQL = strSQL & " WHERE " & strCriteria
CurrentDb.Execute strSQL
Me.Parent.Refresh

HTH,
Nikos