Thank you so much for you help. I have original query that calculate the
AddDate between the Scheduled Date and the Next date acoording to the
Frequency and that updating to the table and the form. Now I created new
macro that will run this query and placed it on the module in the form. Now
it's working, but I need that in the New Pm the Next Scheduled date will
become the Scheduled Date and the New Next Scheduled date will calculate
again according to the frequency. For exemple:
On original record: Scheduled date is 4/1/2007 Frequency:6 months and Next
Scheduled Datwe is 10/1/2007.
What I need is on the duplicated record: Scheduled date is-10/01/2007, the
same frequency, Next Scheduled Date is 04/01/2008.And the Status on the new
record is WSCH as default value (that I set on the main table).
is ti more clear?
Thanks again.
Chris Reveille said:
when you are adding the record is one of the fields the status wich you are
putting WSCH. Or have you tried making the default value of the ststus field
in the form WSCH. Or in the table.
Have you tried running an update query that caclulates the two dates. This
query would run right after the append query.
It is hard for me to vusualize everything that you are doing.
:
I have on the append Query -JP#,JP Name, Equipment, Location, Frequency,
Scheduled_Date and Next_Scheduled_Date.(All those field are existing on the
Form).
In the duplicated record all the fields are working as they sould work, but
the Scheduled date and Next Scheduled date stay empty and not calculating as
they should do.
As well, the Status field in the duplicated record schoul be WSCH(as I
stated in the defaulf value), but it's just picking some other status evry
time from the list, I don't know according to what, that status is wrong.
:
Assumptions
You are using an append query
It is launched when the status is changed to "Completed"
the JP#, JP Name, Equipment, and Location fields in the query reference the
current record on the form
Is this part working?
the scheduled date is the current record's next scheduled update date
Is this part working?
You might have to recalculate that scheduled date and next scheduled date
in the append query
How do you reference the fields in your query. Are you getting them from
the subform?
:
As well, the Status in the duplicate PM is not WSCH as it sould be, but it's
changing each time to someting else.
:
Eric -Thank you so much, the Macro is working !!!
I have another problem, maybe you can help me with that: all this macro is
for database that I’m building for Work Orders. I have Form that have
following fields:
Primary key- PM#-auto number.
Job Plan #
JP# Name
Equipment
Location
Frequency: X months
Status: a list of different statuses
Scheduled date:
Next Scheduled Date: (Query: DateAdd-that calculating the scheduled date
+frequency).
What I am doing is: when the status of the PM is “Completed†it will open a
new record in this form (new row) with the same JP#, JP Name, Equipment,
Location-from the original PM and Next Scheduled Date now become the
Scheduled Date, and the new Next Scheduled date will calculated from the
query as before. The purpose is to have a new PM for the same Equipment that
scheduled to next scheduled date. Is it making sense?
The problem is on the duplicated PM the Next_Scheduled_Date stayed the same,
as well the Scheduled Date. I tried to add the DateAdd calculation to the
Scheduled date on the Append Query in order that it will calculate the new
date according to the frequency, but it's not working. Neither the Next
Scheduled Date. What am I missing again?
I’m really appreciate your help.
:
It should be put in a vb module not in the line of the after update event
if me.[Status]=â€Completed†then
DoCmd.RunMacro â€mcrAppendPmsâ€
end if
:
When I'm living the if open it's giving me "Compile Error Expected: )
But when I'm closing the IIF, it's fiving new Compile error Syntax error.
:
I think I see
Instead of putting on the line of the after update event put a [ and the
click on the three ...
That will open a vb module trhen put
if([Status]=â€Completed†then
DoCmd.RunMacro â€mcrAppendPmsâ€
I do not think the macro name can have a ' in it
rename it to mcrAppendpms
:
At first, thank you so much for the quick response and the help!
the error is: "The expression you entered contains invalid syntax or you
need to enclose your text data in quotes".
My line After Undeta Event says:" =iif
me.([Status]="Comp-Completed",DoCmd.RunMacro"mcrAppendPm's")"
Where am I wrong?
Thanks again.
:
What is the error msg
Does your run macro line look like this?
Docmd.RunMacro "MacroName"
:
It’s giving an error; I think I’m missing something –a dot or a comma or
something else.
Thanks again for your help.
:
I think you want
if me.[status] ="Completed" then
docmd.runmacro......
else
end if
:
I have Macro that Opens Append Query. I would like to assign that Macro into
the Form in After Update Event that would say: when Status is “Completed†run
the Macro, else lives the previous Status.
I started to write the IIF expression, but it’s not working:
=iif([Status]=â€Completedâ€,DoCmd.RunMacroâ€mcrAppendPm’sâ€)
What am I doing wrong?
Thanks a lot for any help.
