Macro that will create a new record in the form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I’m building a database of 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 want to do 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 make sense?
I want to build a macro, but I don’t know how to do it.

Is any one can help me please? Thanks.
 
Anna,

I think the best approach here is to use an Append Query to add a new
record to the table, with the required data. If you want to automate
this with a macro, then you would use an OpenQuery action in the macro
to run the Append.
 
Thank you for your help. I have never sew Append Query, could you give me a
hand here?
Thanks again.
 
Anna,

Make a new Query, based on your table. Add the fields you want
replicated into the query design grid. In the Criteria of the PM#
column, put the equivalent of this:
[Forms]![NameOfForm]![PM#]
From the Query menu, select Append, andf then nominate the same table
as the one to append to.
If the [PM#] appears in the grid in the Append To row, delete it, but
leave the others there.
Close and save the query.

Now you can make a macro, using the OpenQuery action, and nominate this
append query that you have just made. At the beginning of the macro, I
would also suggest a RunCommand/SaveRecord action.
Assign this query on a suitable event. In this case, it is probably the
After Update event of the Status field when you mark it as "completed".

By the way, it is not a good idea to have a # as part of the name of a
field. Change these if you can.
 
Steve, thank you so much for your help!! The macro is working perfect and
it's duplicating the PM that I need. But I still have 2 problems:
1.I can't find the Append Query in the Expression builder in the After
Update event in the Form, so I can't assist it to work on the Status change.
Maybe I'm missing something?
2. 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?
Thank you again for your huge help.


Steve Schapel said:
Anna,

Make a new Query, based on your table. Add the fields you want
replicated into the query design grid. In the Criteria of the PM#
column, put the equivalent of this:
[Forms]![NameOfForm]![PM#]
From the Query menu, select Append, andf then nominate the same table
as the one to append to.
If the [PM#] appears in the grid in the Append To row, delete it, but
leave the others there.
Close and save the query.

Now you can make a macro, using the OpenQuery action, and nominate this
append query that you have just made. At the beginning of the macro, I
would also suggest a RunCommand/SaveRecord action.
Assign this query on a suitable event. In this case, it is probably the
After Update event of the Status field when you mark it as "completed".

By the way, it is not a good idea to have a # as part of the name of a
field. Change these if you can.

--
Steve Schapel, Microsoft Access MVP
Thank you for your help. I have never sew Append Query, could you give me a
hand here?
Thanks again.
Click to expand...
Click to expand...
 
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.


Steve Schapel said:
Anna,

Make a new Query, based on your table. Add the fields you want
replicated into the query design grid. In the Criteria of the PM#
column, put the equivalent of this:
[Forms]![NameOfForm]![PM#]
From the Query menu, select Append, andf then nominate the same table
as the one to append to.
If the [PM#] appears in the grid in the Append To row, delete it, but
leave the others there.
Close and save the query.

Now you can make a macro, using the OpenQuery action, and nominate this
append query that you have just made. At the beginning of the macro, I
would also suggest a RunCommand/SaveRecord action.
Assign this query on a suitable event. In this case, it is probably the
After Update event of the Status field when you mark it as "completed".

By the way, it is not a good idea to have a # as part of the name of a
field. Change these if you can.

--
Steve Schapel, Microsoft Access MVP
Thank you for your help. I have never sew Append Query, could you give me a
hand here?
Thanks again.
Click to expand...
Click to expand...
 
Anna,

Can you go to the design view of the append query, select SQL from the
View menu, and then copy/paste the SQL of the query into your reply
here? Thanks. Can't really advise without seeing the details of what
you are doing.
 
Back
Top