none
Creating Maintenance Database RRS feed

  • Question

  • Hello.

    I'm currently trying to construct a maintenance database for a non-profit organization.

    The Database is supposed to be able to handle both recurring tasks such as say Cleaning, checking fire extinguishers etc. and one-occation-tasks like for instance buying a new sofa or going to the junkyard.

    I've tried Allen Browne's method of recurring tasks but I couldn't seem to get it to work in a way where you can mark a specific event with "Completed".  I also tried to create a "make-table-query" which just created a mess in the assigned table with like 16000 tasks (no kidding).

    My new idea is to try and create a database that can sort of re-invent the tasks that are marked as "Recurring" and add a new due-date for it if you know what I mean.

    The idea is that:

    1. You have a table, or a form with a subform, with a set of tasks, that are given a certain recurrence intervall based on for instance 4 weeks (4 x week).

    2. You can click on an edit button which will open a form with the information relating to the task.

    And here comes my problem:

    3. You then mark the task as "Complete" and press "Update" and at this point, IF the task is also marked as Recurring, Access gives the task a new due date based on the specified intervall and adds any changes that have been made to the task in a comment field, and IF IT IS NOT marked as Recurring, Access will move the task to an archive-table (or delete it, which ever is easier).

    I absolutely get that this might involve some difficult VBA-coding, but it would also be an AWESOME maintenance database in my opinion.

    I'm kind of at an Intermediate level in Access-programming, but please think "Access-For-Dummies-Guidelines" and I would appreciate any help I can get with this problem.

    Best Regards,

    Viktor

    Sunday, September 24, 2017 8:21 PM

Answers

  • How do I create the "link" between the sub-form and the parent form based on a query?

    The link between a form and subform is created by setting the LinkMasterFields and LinkChildFields properties of the subform control.  This is the control in the parent form which houses the subform.  The properties are set to the relevant primary and foreign key names, which in the example I posted would both be ProjectID.

    You'll find an example of a form/subform which represents a many-to-many relationship type in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the section on 'Building the user interface' includes a Contacts form in which there is a subform in which one or more employers per contact are entered.  The demo also includes examples of basic table and relationships design etc, which you might also find helpful.

    As you want the next scheduled date to be an interval after the current date the SQL statement in the code would be:

       strSQL = "INSERT INTO ProjectTasks(ProjectID,TaskID,ScheduledDate) " & _
            "VALUES(" & Me.ProjectID & "," & Me.TaskID & ",#" & _
            "Format(DateAdd,"ww", Me.Interval, VBA.Date) ,"yyyy-mm-dd") & "#)"

    It's not actually necessary to qualify the Date function with the name of the VBA object library, but doing so avoids any ambiguity (I sometimes find people have unwisely used Date as a column name!).




    Ken Sheridan, Stafford, England

    • Proposed as answer by Chenchen LiModerator Tuesday, September 26, 2017 7:52 AM
    • Marked as answer by Viktor91 Sunday, October 1, 2017 1:50 PM
    Monday, September 25, 2017 6:26 PM

All replies

  • Hi Viktor. My idea would be a table for each task where you will indicate recurring or not and a separate table for the actual tasks completed or due. When you complete a recurring task, a new task is added based on assigned interval. Also, all completed tasks stay where they are, no need to move or delete them. Hope it helps...
    Sunday, September 24, 2017 10:30 PM
  • Let's assume the conceptual model is one of Projects and Tasks, where each project can have multiple tasks, and each task can apply to multiple projects, i.e. a many-to-many relationship type.  The physical model, in broad outline, would comprise the following tables:

    Projects
    ….ProjectID  (PK)
    ….Project

    Tasks
    ….TaskID  (PK)
    ….Task
    ….Interval
    ….Recurring

    For this example we'll assume that Interval is the number of weeks as an integer. Recurring is a Boolean (Yes/No) column.

    To model the relationship type:

    ProjectTasks
    ….ProjectID  (FK)
    ….TaskID  (FK)
    ….ScheduledDate
    ….Completed

    The primary key of ProjectTasks is a composite one of ProjectID, TaskID and ScheduledDate.  Completed is a column of Boolean data type.

    The interface would be a Projects form, in single form view, within which would be a ProjectTasks subform, linked to the parent form on ProjectID, based on the following query:

    SELECT ProjectTasks.*, Tasks.Interval, Tasks.Recurring
    FROM ProjectTasks INNER JOIN Tasks
    ON ProjectTasks.TaskID = Tasks.TaskID
    WHERE NOT Completed
    ORDER BY Task;

    You can, if you prefer, amend the ORDER BY clause to return the rows in ScheduledDate order.  In the AfterUpdate event procedure of a check box control bound to the Completed column would be the following code:

    Dim strSQL As String

    If Me.Completed And Me.Recurring Then

        strSQL = "INSERT INTO ProjectTasks(ProjectID,TaskID,ScheduledDate) " & _
            "VALUES(" & Me.ProjectID & "," & Me.TaskID & ",#" & _
            "Format(DateAdd,"ww", Me.Interval, Me.ScheduledDate) ,"yyyy-mm-dd") & "#)"

         CurrentDb.Execute strSQL, dbFailOnError
         Me.Requery
    End If

    Once the user checks the Completed check box a new row will be inserted into ProjectTasks at the relevant interval in weeks from the ScheduledDate value in the current row.  If you'd prefer in to be from the current date, then call the Date() function in place of the reference to Me.ScheduledDate when calling the DateAdd function.  There is no need for a separate 'Update' button, but if you prefer to add one to the subform, the above code would go in the button's Click event procedure.

    By calling the Requery method the subform's recordset will be reloaded, and will include the new row and exclude the current row.  It is unnecessary to move the current row to an archive table, or to delete the row.  The task history per project is thus retained in the table, and can be viewed independently of the form/subform if necessary.

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Monday, September 25, 2017 11:03 AM Typo corrected.
    Monday, September 25, 2017 10:58 AM
  • Hi.

    Sounds like the thing I'm looking for.

    Any tips on how one can create your suggestion? I'm not really at the point where I can just go Aha, yes, I'll do exactly that and then do it haha.

    So any tips on how this could be created?

    Monday, September 25, 2017 5:42 PM
  • Hi Viktor,

    I will assume you're talking to Ken, so I'll just wait around until he responds. Good luck with your project.

    Monday, September 25, 2017 5:53 PM
  • Hi Ken.

    Thankyou for a speedy and a very detailed description and suggestions on where I can insert alternative calculation "baselines" (ref. to Date() instead of ScheduledDate), from current date would be even better so again, thankyou.

    What you're suggesting looks exactly like what I'm looking for and I'm currently trying to work through what you're describing, but I've ran into a few snags, which are probably very basic stuff to you, but anyways:

    How do I create the "link" between the sub-form and the parent form based on a query?

    My keyboard is running hot from Googling every possible combination of this but I just can't seem to find an explanation for how to do this or where to do this.

    Best Regards,

    Viktor.

    Monday, September 25, 2017 6:09 PM
  • Victor91:

    You asked for tips on how to implement Kens suggestion. It sounds like you don't know how to create a table or a query or a form to enter data. Is that correct?

    Monday, September 25, 2017 6:19 PM
  • How do I create the "link" between the sub-form and the parent form based on a query?

    The link between a form and subform is created by setting the LinkMasterFields and LinkChildFields properties of the subform control.  This is the control in the parent form which houses the subform.  The properties are set to the relevant primary and foreign key names, which in the example I posted would both be ProjectID.

    You'll find an example of a form/subform which represents a many-to-many relationship type in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the section on 'Building the user interface' includes a Contacts form in which there is a subform in which one or more employers per contact are entered.  The demo also includes examples of basic table and relationships design etc, which you might also find helpful.

    As you want the next scheduled date to be an interval after the current date the SQL statement in the code would be:

       strSQL = "INSERT INTO ProjectTasks(ProjectID,TaskID,ScheduledDate) " & _
            "VALUES(" & Me.ProjectID & "," & Me.TaskID & ",#" & _
            "Format(DateAdd,"ww", Me.Interval, VBA.Date) ,"yyyy-mm-dd") & "#)"

    It's not actually necessary to qualify the Date function with the name of the VBA object library, but doing so avoids any ambiguity (I sometimes find people have unwisely used Date as a column name!).




    Ken Sheridan, Stafford, England

    • Proposed as answer by Chenchen LiModerator Tuesday, September 26, 2017 7:52 AM
    • Marked as answer by Viktor91 Sunday, October 1, 2017 1:50 PM
    Monday, September 25, 2017 6:26 PM
  • Hi Lawrence,

    I know how to create a table, query and actually forms aswell. 

    But thankyou for your inputs and for assisting me with my problem.

    Best regards,

    Viktor

    Monday, September 25, 2017 6:46 PM
  • Hi Ken,

    Thankyou for your assistance and for being patient with me.

    Best Regards,

    Viktor

    Monday, September 25, 2017 7:10 PM
  • Hello Viktor,

    I suggest you mark helpful post as answer to close this thread if your issue has been resolved. And i suggest you post new threads for new issues when following Ken's suggestion.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 26, 2017 7:52 AM
    Moderator