DATA FROM RECORDS ON SUBFORM

[hogan]

I am creating an equipment service database and have a main work order page
with service work order ID as my Primary Key. This form has a subform to
enter multiple workorders for different pieces of equipment under the same
service work order ID. I have a check box in subform to check when that piece
of equipment is completed and also a check box on the main form to show when
all equipment work orders are complete. I am trying to figure out how to
automatically set the value of the complete check box on my main form to -1
when all equipment work orders are markd completed.

I do not know code and strictly using macros. Thanks for your help.

 

[Steve Schapel]

Hogan,

You could potentially use a SetValue action in your macro, or else an
OpenQuery action to run an Update Query to change the value of the main
form's Complet field.

But really this is probably not a good idea. Probably the table behind
the main form should not even have a Complete field. If the main
service work order is complete when all of the component work orders are
complete, then this is derived or calculated data that should not be
stored in a table. The state of completion of the main service work
order can always be easily calculated via a query or in a calculated
field on a form or report, whenever you need it. Hope that makes sense.

 

[hogan]

Steve,

Sounds easy enough. I have another question regarding the individual item
work orders. I have my service manager "closing" the individual work item
work orders after reviewing them for accuracy. Lets say that the main service
work order id is "serv38" and that has 4 serperate "item work orders". When
the last item work order is complete i want to print a report for the
complete "serv38" work order and also let the service manager know as well.

 

[Steve Schapel]

Hogan,

What does "closing" the work order mean, in database terms?

 

[hogan]

After review by my service manager, he would check the "completed" check box
to show that this work order for an individual piece of equipment is
complete.

If all item work orders are completed that would mean that the main work
order is complete and I would want a report to print once the last report is
completed as well as maybe a message box appearing as well. I can create the
message box but how do I determine if all item work orders are complete for a
specific main work order.

I really appreciate your help Steve. Thank you very much.

 

[Steve Schapel]

Hogan,

First of all, make a query based on the item work orders table.

Add the Service Work Order ID field to the query design grid, untick the
'Show' box, and in the Criteria, enter the equivalent of:
[Forms]![NameOfYourWorkOrderForm]![Service Work Order ID]
In the 'Field' row of the query design grid, enter the equivalent of this:
AllDone: Count(*)=Abs(Sum([Complete]))

Make a macro to go on the After Update event of the item work orders
subform - for your msgbox, and sending the email. In the Condition of
the macro, put the equivalent of this:
DLookup("[AllDone]","NameOfYourQuery")<>0

Possible variation: The above will result in the msgbox & email if there
are any changes made to other fields in the item work order data on the
form, subsequent to it being "closed". If this is likely to be
inconvenient, you could instead assign the macro on the After Update
event of the Complete checkbox, and put a RunCommand/SaveRecord action
in at the beginning of the macro.

 

[hogan]

Wow. I will give it a try. Super help. Thank you.

Hogan,

First of all, make a query based on the item work orders table.

Add the Service Work Order ID field to the query design grid, untick the
'Show' box, and in the Criteria, enter the equivalent of:
[Forms]![NameOfYourWorkOrderForm]![Service Work Order ID]
In the 'Field' row of the query design grid, enter the equivalent of this:
AllDone: Count(*)=Abs(Sum([Complete]))

Make a macro to go on the After Update event of the item work orders
subform - for your msgbox, and sending the email. In the Condition of
the macro, put the equivalent of this:
DLookup("[AllDone]","NameOfYourQuery")<>0

Possible variation: The above will result in the msgbox & email if there
are any changes made to other fields in the item work order data on the
form, subsequent to it being "closed". If this is likely to be
inconvenient, you could instead assign the macro on the After Update
event of the Complete checkbox, and put a RunCommand/SaveRecord action
in at the beginning of the macro.

--
Steve Schapel, Microsoft Access MVP

After review by my service manager, he would check the "completed" check box
to show that this work order for an individual piece of equipment is
complete.

If all item work orders are completed that would mean that the main work
order is complete and I would want a report to print once the last report is
completed as well as maybe a message box appearing as well. I can create the
message box but how do I determine if all item work orders are complete for a
specific main work order.

 

[hogan]

Steve I went with putting the macro on the After Update of the complete check
box and went checked I get an error "The object doesn't contain the
Automation object 'Query2'. I have not renamed the query, just left it at the
default name. From what I can see everything was copied and changed
correctly. The condition on the macro is putting brackets around the query
name. Is this correct?

Hogan,

First of all, make a query based on the item work orders table.

Add the Service Work Order ID field to the query design grid, untick the
'Show' box, and in the Criteria, enter the equivalent of:
[Forms]![NameOfYourWorkOrderForm]![Service Work Order ID]
In the 'Field' row of the query design grid, enter the equivalent of this:
AllDone: Count(*)=Abs(Sum([Complete]))

Make a macro to go on the After Update event of the item work orders
subform - for your msgbox, and sending the email. In the Condition of
the macro, put the equivalent of this:
DLookup("[AllDone]","NameOfYourQuery")<>0

Possible variation: The above will result in the msgbox & email if there
are any changes made to other fields in the item work order data on the
form, subsequent to it being "closed". If this is likely to be
inconvenient, you could instead assign the macro on the After Update
event of the Complete checkbox, and put a RunCommand/SaveRecord action
in at the beginning of the macro.

--
Steve Schapel, Microsoft Access MVP

After review by my service manager, he would check the "completed" check box
to show that this work order for an individual piece of equipment is
complete.

If all item work orders are completed that would mean that the main work
order is complete and I would want a report to print once the last report is
completed as well as maybe a message box appearing as well. I can create the
message box but how do I determine if all item work orders are complete for a
specific main work order.

 

[Steve Schapel]

Hogan,

Have you got ""s around the Query2? Maybe could you copy/paste the
Condition from the macro into your reply.

By the way, when you are applying a Condition in a macro, you enter the
condition expression for the first Action that it applies to. If the
condition applies to more than one action, then in the Condition column
for the subsequent actions, you enter an ellipsis (i.e. three dots ... ).

 

[hogan]

Steve, I do have "" now. Still does not work. When i run the macro from the
macro page I get an error saying that saverecord is not available or i'm in a
read only db which i am not. Other options than the saverecord runcommand?

 

[Steve Schapel]

Hogan,

You won't be able to run the macro except from the form, if you have a
RunCommand/SaveRecord action. The form is the only place where there is
a record to save!

Leave the Condition blank for the RunCommand/SaveRecord action.

 

[hogan]

Steve,

I do run the macro from the form and nothing happens. Well, I can say my
msgbox does not appear and I don't know if the record is saving. I do not get
the run command error when run from the form though. The query you created
works perfectly but my msg is not appearing.

 

[Steve Schapel]

Hogan,

I think I'd better get you to give a full rundown of exactly what's in
the macro, like this:

Condition: ...
Action: ...
Arguments: ...
Condition: ...
Action: ...
Arguments: ...
.... etc

And also the SQL view of the query, if you can.

 

[hogan]

Steve,

I was thinking the same.

Action: RunCommand
Argument: SaveRecord

Condition: DLookUp("[All Done]","Query2")<>0
Action: MsgBox
Argument: SERVICE WORK ORDER IS COMPLETE, Yes, Information,

Query2

SELECT Count(*)=Abs(Sum([COMPLETED])) AS [ALL DONE]
FROM [SKU WORK ORDER]
WHERE ((([SKU WORK ORDER].[SERVICE WORKORDER ID])=[Forms]![IN HOUSE SERVICE
WORK ORDER]![SERVICE WORK ORDER ID]))

Thanks for your help.

 

[Steve Schapel]

Hogan,

Well, as far as I can see, this is what I was expecting. So apparently
you have understood and followed my previous suggestions -
congratulations!

The next thing to do is to open your IN HOUSE SERVICE WORK ORDER form at
the required record, and then, at the same time, open Query2 datasheet,
and see what is the value of the ALL DONE field.

 

[hogan]

Steve,

Would these actions be included in the macro?

Hogan,

Well, as far as I can see, this is what I was expecting. So apparently
you have understood and followed my previous suggestions -
congratulations!

The next thing to do is to open your IN HOUSE SERVICE WORK ORDER form at
the required record, and then, at the same time, open Query2 datasheet,
and see what is the value of the ALL DONE field.

--
Steve Schapel, Microsoft Access MVP

Steve,

I was thinking the same.

Action: RunCommand
Argument: SaveRecord

Condition: DLookUp("[All Done]","Query2")<>0
Action: MsgBox
Argument: SERVICE WORK ORDER IS COMPLETE, Yes, Information,

Query2

SELECT Count(*)=Abs(Sum([COMPLETED])) AS [ALL DONE]
FROM [SKU WORK ORDER]
WHERE ((([SKU WORK ORDER].[SERVICE WORKORDER ID])=[Forms]![IN HOUSE SERVICE
WORK ORDER]![SERVICE WORK ORDER ID]))

Thanks for your help.

 

[Steve Schapel]

Hogan,

No. What I am suggesting is a trouble-shooting step. What is the outcome?

 

[hogan]

Steve,

The outcome was -1.

Hogan,

No. What I am suggesting is a trouble-shooting step. What is the outcome?

 

[Steve Schapel]

Hogan,

I am very sorry, I can't see what the problem is here. Your query is
returning a value of -1 so therefore the Condition in your macro should
be met, so therefore you should get the message box. I know you already
know this (!) but in the absence of anything more intelligent to say I
am just repeating the obvious.

On the other hand, there has to be an explanation for why it is not
behaving as expected.

So if it was mine, the next step I would take is put some more MsgBox
actions into the macro to try and trace the process that is happening
here. So I would put one right at the beginning of the macro, with no
Conditions, just to establish that the Event is actually being
triggered. And then after the SaveRecord action, another MsgBox (no
Condition) and for the message put:
=DLookUp("[All Done]","Query2")
.. . . expecting that it will be -1 in the message.

 

[hogan]

Steve, I have tried inserting the messages like you said. I get the first
message when the macro triggers but after that I receive an action failed
box. Error 2950 if that means anything.

I few things i noticed playing around. When I open up the query a parameter
box appears asking for the workorder id. I enter an id and I get either -1 or
0. How does the macro know which workorder id to look at.

Thanks

Hogan,

I am very sorry, I can't see what the problem is here. Your query is
returning a value of -1 so therefore the Condition in your macro should
be met, so therefore you should get the message box. I know you already
know this (!) but in the absence of anything more intelligent to say I
am just repeating the obvious.

On the other hand, there has to be an explanation for why it is not
behaving as expected.

So if it was mine, the next step I would take is put some more MsgBox
actions into the macro to try and trace the process that is happening
here. So I would put one right at the beginning of the macro, with no
Conditions, just to establish that the Event is actually being
triggered. And then after the SaveRecord action, another MsgBox (no
Condition) and for the message put:
=DLookUp("[All Done]","Query2")
.. . . expecting that it will be -1 in the message.

--
Steve Schapel, Microsoft Access MVP

Steve,

The outcome was -1.