Part must have works order issued box ticked before part allows the shipped ticked box being ticke

[Rainbows5.]

Hi
i have 2 tables i called " internal works order part2" and the other one called " order details "
i have noticed that the feild called "issued" in the works order table has not been ticked for whatever reason
both tables refer to a unique number like PCO00321 Etc etc. this number is the part number
how can we get it that the feild called "shipped" in the order details tables for that same part will not allow it to be ticked without the "issued" tick box to be ticked with a message stating why it cannot be ticked

PCO	Issued
PCO00321	No
PCO00322	No
PCO00323	No
PCO00324	No
PCO00325	No

BatchNo	shipped
PCO00321	Yes
PCO00322	Yes
PCO00323	Yes
PCO00324	Yes
PCO00325	Yes

thanks


steve

[ArviLaanemets]

how can we get it that the feild called "shipped" in the order details tables for that same part will not allow it to be ticked without the "issued" tick box to be ticked with a message stating why it cannot be ticked

Based on your post it looks like you allow users to work with Tables directly (a big NO! NO!). When this is the case, you cant!
When user enters data through form only (a work order form with work order details subform), then you can use Form Events to disable/hide the control (check box?) for shipped field in case the Issued field is not ticked, and enable/unhide it in case the Issued field is ticked.

You also can consider a design, where instead 2 fields to check, you have a single smallint field, which can have values like:
0 when the order row is not issued;
1 when order row is issued, but not shipped;
2 when order row is issued, and shipped.
In work order subform, this field will be linked to combo box, which displays for those values texts, like "Not Issued", "Issued", or "Issued and Shipped". All the hassle with determining, which field can be edited or not, will be then avoided, because the possibility to select "Shipped" only doesn't exist!.

[jojowhite]

You will need to create a Data Macro (After Update) on table "order details" in order that " internal works order part2" table will get updated.
open the "order details" table in design view then. on the Ribbon->Table Design->Create Data Macro
(see Image 1).


the Actual Macro:



see the attached demo db. open "order details" table and tick/untick the checkbox and save.
now see the other table.

[CJ_London]

To add to post2, tick boxes give little meaningful information- it is often better to store a value such as a date so you know when issued and when shipped. If you already have that information in another field, you don’t need the tick box - though you might use one to populate the date with a little bit of code to make it easier for the user.

[jojowhite]

oh, i am wrong.
you don't want the "shipped" field in "order details" to be Ticked when
corresponding record on the other table is Not Ticked.

you still use Data Macro. this time use Before Change.



The Actual Macro:


Result:

[Ken Sheridan]

I would suggest that you apply a CHECK CONSTRAINT to each table:

CurrentProject.Connection.Execute "ALTER TABLE [internal works order part2] ADD CONSTRAINT ShipOnlyIssued CHECK((SELECT PCO FROM [internal works order part2] INNER JOIN [order details] ON [internal works order part2].PCO = [order details].BatchNo WHERE Issued = FALSE AND Shipped = TRUE)IS NULL)"

Do the same for the order details table, changing the constraint name to something different.

If you need to drop a constraint the syntax is:

CurrentProject.Connection.Execute "ALTER TABLE [internal works order part2] DROP CONSTRAINT ShipOnlyIssued"

Note that in Access you cannot apply or drop a CHECK CONSTRAINT by executing the DDL statement in SQL view, only in code as above. The code can be entered in the immediate window, but be sure the table is not open when you do. In a split database this must be done in the back end of course.