|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#11
April 10th, 2007, 03:44 PM
posted to microsoft.public.access.forms
|
|||
|
|||
Assign macro on After update event
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 "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: What is the error msg Does your run macro line look like this? Docmd.RunMacro "MacroName" "Anna" wrote: It’s giving an error; I think I’m missing something –a dot or a comma or something else. Thanks again for your help. "Eric Blitzer" wrote: I think you want if me.[status] ="Completed" then docmd.runmacro...... else end if "Anna" wrote: 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. 
|
|
#12
April 10th, 2007, 04:02 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Assign macro on After update event
that's what I have in the Module:
Private Sub Status_AfterUpdate() ifme([Status]="Comp-Completed",DoCmd.RunMacro"mcrAppendPms") End Sub Stil when I chenge the Status in the form it's opens the Module window with error:Syntax error. I'm sorry for making it hard, but what am I missing again? Thank you so much for your time and the help. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: What is the error msg Does your run macro line look like this? Docmd.RunMacro "MacroName" "Anna" wrote: It’s giving an error; I think I’m missing something –a dot or a comma or something else. Thanks again for your help. "Eric Blitzer" wrote: I think you want if me.[status] ="Completed" then docmd.runmacro...... else end if "Anna" wrote: 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.
|
|
#13
April 10th, 2007, 04:22 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Assign macro on After update event
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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: What is the error msg Does your run macro line look like this? Docmd.RunMacro "MacroName" "Anna" wrote: It’s giving an error; I think I’m missing something –a dot or a comma or something else. Thanks again for your help. "Eric Blitzer" wrote: I think you want if me.[status] ="Completed" then docmd.runmacro...... else end if "Anna" wrote: 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.
|
|
#14
April 10th, 2007, 04:24 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Assign macro on After update event
you need a space after if and a . after me. Remove the () and break up into
different lines copy and paste this if me.Status="Comp-Completed" then DoCmd.RunMacro "mcrAppendPms" end if End Sub "Anna" wrote: that's what I have in the Module: Private Sub Status_AfterUpdate() ifme([Status]="Comp-Completed",DoCmd.RunMacro"mcrAppendPms") End Sub Stil when I chenge the Status in the form it's opens the Module window with error:Syntax error. I'm sorry for making it hard, but what am I missing again? Thank you so much for your time and the help. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: What is the error msg Does your run macro line look like this? Docmd.RunMacro "MacroName" "Anna" wrote: It’s giving an error; I think I’m missing something –a dot or a comma or something else. Thanks again for your help. "Eric Blitzer" wrote: I think you want if me.[status] ="Completed" then docmd.runmacro...... else end if "Anna" wrote: 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.
|
|
#15
April 10th, 2007, 04:38 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Assign macro on After update event
As well, the Status in the duplicate PM is not WSCH as it sould be, but it's
changing each time to someting else. "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: What is the error msg Does your run macro line look like this? Docmd.RunMacro "MacroName" "Anna" wrote: It’s giving an error; I think I’m missing something –a dot or a comma or something else. Thanks again for your help. "Eric Blitzer" wrote: I think you want if me.[status] ="Completed" then docmd.runmacro...... else end if "Anna" wrote: 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.
|
|
#16
April 10th, 2007, 05:18 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Assign macro on After update event
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? "Anna" wrote: As well, the Status in the duplicate PM is not WSCH as it sould be, but it's changing each time to someting else. "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: What is the error msg Does your run macro line look like this? Docmd.RunMacro "MacroName" "Anna" wrote: It’s giving an error; I think I’m missing something –a dot or a comma or something else. Thanks again for your help. "Eric Blitzer" wrote: I think you want if me.[status] ="Completed" then docmd.runmacro...... else end if "Anna" wrote: 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.
|
|
#17
April 10th, 2007, 05:36 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Assign macro on After update event
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. "Chris Reveille" wrote: 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? "Anna" wrote: As well, the Status in the duplicate PM is not WSCH as it sould be, but it's changing each time to someting else. "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: What is the error msg Does your run macro line look like this? Docmd.RunMacro "MacroName" "Anna" wrote: It’s giving an error; I think I’m missing something –a dot or a comma or something else. Thanks again for your help. "Eric Blitzer" wrote: I think you want if me.[status] ="Completed" then docmd.runmacro...... else end if "Anna" wrote: 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.
|
|
#18
April 10th, 2007, 05:42 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Assign macro on After update event
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. "Anna" wrote: 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. "Chris Reveille" wrote: 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? "Anna" wrote: As well, the Status in the duplicate PM is not WSCH as it sould be, but it's changing each time to someting else. "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: What is the error msg Does your run macro line look like this? Docmd.RunMacro "MacroName" "Anna" wrote: It’s giving an error; I think I’m missing something –a dot or a comma or something else. Thanks again for your help. "Eric Blitzer" wrote: I think you want if me.[status] ="Completed" then docmd.runmacro...... else end if "Anna" wrote: 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.
|
|
#19
April 10th, 2007, 06:52 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Assign macro on After update event
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" wrote: 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. "Anna" wrote: 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. "Chris Reveille" wrote: 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? "Anna" wrote: As well, the Status in the duplicate PM is not WSCH as it sould be, but it's changing each time to someting else. "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: What is the error msg Does your run macro line look like this? Docmd.RunMacro "MacroName" "Anna" wrote: It’s giving an error; I think I’m missing something –a dot or a comma or something else. Thanks again for your help. "Eric Blitzer" wrote: I think you want if me.[status] ="Completed" then docmd.runmacro...... else end if "Anna" wrote: 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.
|
|
#20
April 10th, 2007, 07:06 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Assign macro on After update event
I don't see why you can't append the current record next scheduled date to
the new record schedulled date and the Next scheduled update + 183 days to the next scheduled date when appending the new record. If you cant' append it then try using an update query to update the dates in the new record. You could also update the status to WSCH too. "Anna" wrote: 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" wrote: 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. "Anna" wrote: 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. "Chris Reveille" wrote: 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? "Anna" wrote: As well, the Status in the duplicate PM is not WSCH as it sould be, but it's changing each time to someting else. "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: 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 "Anna" wrote: 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. "Eric Blitzer" wrote: What is the error msg Does your run macro line look like this? Docmd.RunMacro "MacroName" "Anna" wrote: It’s giving an error; I think I’m missing something –a dot or a comma or something else. Thanks again for your help. "Eric Blitzer" wrote: I think you want if me.[status] ="Completed" then docmd.runmacro...... else end if "Anna" wrote: 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
