Recurring dates (1 Viewer)

Jason1

Registered User.
Local time
Today, 07:55
Joined
Mar 12, 2010
Messages
63
Hello,

It's been a while since I have posted on this forum. I am running access 2016, and am really not a pro a making these databases. I am attempting to make a database that will work as a preventative maintenance schedule for our equipment. What I would like to do is have the "PM" (record describing what work is to be performed) regenerate itself with a future date once the current record is completed. I know I am describing this badly.

For example: The PM comes up to be done today. When the maintenance tech completes the PM, and enters the notes in the database the record is saved, but then rescheduled for say 6 months later. So the database would have to generate another record with a new due date based on a specified interval.

Is this possible? and how would you go about getting the database to complete this task?

thanks for the help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
21,357
Hi. Sounds like a simple APPEND query should do it.
 

Jason1

Registered User.
Local time
Today, 07:55
Joined
Mar 12, 2010
Messages
63
DBguy..
Thanks for the response. I'm not familiar with APPEND queries, but I'm sure it is something YouTube can help me figure out. Will the Append query still allow me to keep the old record? I want the completed work to be searchable.

Thanks again
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 07:55
Joined
Oct 17, 2012
Messages
3,276
APPEND queries add new records to tables. They don't touch old ones.
 

Jason1

Registered User.
Local time
Today, 07:55
Joined
Mar 12, 2010
Messages
63
Sounds like it should work then. I'll dig into it more.

Thanks for the help guys
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:55
Joined
Feb 28, 2001
Messages
26,996
Here is a more detailed idea. Tossing it out as a thought-provoker if nothing else.

Put a flag in your data record that says "recurring event." Use other information in the record to show that the scheduled maintenance has been done. Could be a flag, but could be just that there is date in a field set aside for, say, completion date of the event. If you have a "frequency of maintenance" interval (days, weeks, months... unit doesn't actually matter) then you could, at the time that you update the completion date on that record, have code that takes the just-updated record and inserts a new record into the table based on that record. So you would copy the machine info, the "frequency" and "recurring flag" - but use DateAdd to compute the new due date for the new record.

Then you can open a recordset, do an .AddNew to it, populate the new record with the data as I just described, do a .Update on the recordset, and close the recordset. Problem handled.

I didn't write code because I think you are still mulling this over in your mind. But you should be able to look up recordset operations. If you do this from a form, you would be able to see all the fields in the maintenance table at that time. The test for whether machine X already HAS a scheduled maintenance event would be whether a DCount of records for machine X has a "completed date" of 0. O.K., for dates, technically if you have a date, you wouldn't use "0" as such, but you could use CDate(0), which turns out to be something like 1-Jan-1900, CLEARLY NOT a current date. Same difference, really.

Then, if you have to perform an out-of-cycle maintenance event, the only question to be asked is whether this means you would reschedule based on completing the scheduled maintenance early or just mark this as a "non-recurring" maintenance. It is your database so it is also your call.
 

AccessBlaster

Registered User.
Local time
Today, 04:55
Joined
May 22, 2010
Messages
5,823
Conditional formatting might be an option. This requires me to open a form / tab to trigger the updated.

 

Attachments

  • CondFormat.jpg
    CondFormat.jpg
    80.2 KB · Views: 96

Users who are viewing this thread

Top Bottom