Requiring help with ironing out some issues with my task management DB

[SparkieDEV]

I am stuck on a number of problem(s) of which I would really appreciate some help (I wasn't particularly keen to try this method of adding a task management DB to my project however it seemed to be the only example to fit the bill):

Simulation: I use the Form DB Task List to list all the tasks created (and filter them as I wish etc). On the left (TaskID) I can select any one and open a Form "View Task Details". When I want to relate another task record to the open one, I would like to go into Related Tasks tab, click on Add Related Task, and select from the list (Save and Close). However, I have some issues I cannot overcome by myself:
Issue 1 - Say I have added a task and now want to remove it from the list, I am seemingly unable to do so. I would like a code or option where I can de-select from the "View Task Details" form and update the record based on a requery/refresh
Issue 2 - When I have selected my related task "save and close" I have to manually refresh the open form "view task details" to see that related task on my list
Issue 3 - I cannot seem to add more than one related task to my list
Issue 4 - On viewing the list of "related tasks" on the "view task details" form, I would like to double click on any one of the related tasks in the list box and have that open that specific record


NOTE: There is an error: "The record source SELECT [TBLMEICAAssets].ASSID,......" when you open the Form: DB Task List - This error is known and is only an occurrence because I have exported part of my DB into this sample. therefore this can be ignored.

I have added a sample DB to see and test. Please note that some links may be broken because I have extracted from my live DB.

 

[arnelgp]

1. added IsTaskDeleted field to TBLTask.
this field will be set to True if we delete
the Task.
2. added query, qryTasks that Filter only
tasks that are not "deleted".
3. change all Form's Record Source to qryTasks.

see the changes on your db.

 

[SparkieDEV]

1. added IsTaskDeleted field to TBLTask.
this field will be set to True if we delete
the Task.
2. added query, qryTasks that Filter only
tasks that are not "deleted".
3. change all Form's Record Source to qryTasks.

see the changes on your db.

Thank you for the initial help.
There are some nice touches made, however, my main focus is on the "View Task Details" form itself. The primary function I am trying to achieve here is to create a relationship (not table relationship) between my chosen main task, and several other tasks on the task list. This will primarily be done through the form "view task details".

On Issue 1 - I mentioned that on the "View Task Details" form, I want to have the option to highlight a related task (in the related task list box) and remove it from the listbox. I cannot see this has been done.

I did see that you added a delete record on the DB Task List which was a nice touch thank you.

On Issue 2 - I am still having to refresh the "view task details" form every time I select a new task to the listbox. Just to clarify, when I have open the "view task details" form, I click on the add related task button to link another task to this main task. When a selection has been made and I save+close, I have to manually refresh the "view task details" window to see the changes

On Issue 3 - I am still unable to add more than one related task to my main "task" in the "view task details" listbox

On Issue 4 - On viewing the list of "related tasks" on the "view task details" form, I would like to double click on any one of the related tasks in the list box and have that open that specific record

Thanks.

 

[Pat Hartman]

I don't know what the problem is but I'm pretty sure your parent/child logic is backwards. I don't write macros and I don't use them so I can't help with that but I can tell you what the logic should be.

You seem to be picking task X and then choosing some other task to relate to it. If that is true, then the second task record needs to be updated with the Id of the first task and I don't think that is what is happening. I think you are updating the first task record with the id of the second. You probably have the logic of parent/child working backwards. That would limit the related tasks to only a single task which is what your question is about.

When a table includes a field (called a foreign key) that points to the ID of a different record (in the same table, as in this case or in a different table), that record is the CHILD record. A child can have only one parent (in this context). When you look at it from the other side, a Parent can have many children but the parent records do not point to the child records, the child records point to the parent. Try to get your head around that.

Self-referencing relationships are inherently recursive (infinite levels) and so using them requires that you understand how a recursive structure works and how to "walk the tree". This is deep stuff and requires VBA to handle correctly.

If you intend for the relationship to be simply two levels, go the simplistic route and use two tables with tasks and subtasks.

 

[SparkieDEV]

I have been re-creating that what is within the sample DB "Issues Tracking" within Access. It isn't what I had in mind but is the closest example to what I would like to achieve so piece by piece I have been only re-using what is already written. Except, this has flaws because for some reason I am unable to re-create the exact environment.

However, I am now re-considering that due to the amount of work and unknowns this has developed, whether it is at all worth building a "related tasks" aspect to the task DB.

 

[Pat Hartman]

Apparently you don't have a clear definition in your mind of what "related" means. There's no way to implement a related tasks feature if you don't know what related means in your context. I gave you an out to the recursive structure you are working with (backwards it seems because you can't make it work) but you didn't comment on it.

 

[SparkieDEV]

Pat, Allow me to comment.

I have been considering your example, I have considered others too. I am weighing up the pros and cons of all, and will look at the overall weight of it over the weekend.

I might not be experienced to your level, however, I do have my own expertise in other areas.
Your skill set is unique to you as an individual and using that to give such negative feedback to another that is not so fortunate in the knowledge you have, leads to a poorer experience of using this forum.

Sometimes, wading through the thick of mud to get to the other side yields greater results and greater feelings of accomplishments than walking around the mud.

 

[Pat Hartman]

This isn't a technical issue. The point is that unless you know exactly what a relationship is, you can never implement it no matter what your technical expertise. Believe it or not, the hardest part of any application development is clarifying the rules. Implementation is rarely the issue. Once you can clearly define your version of a relationship, we will be happy to help you implement it.

 

[mike60smart]

Thank you for the initial help.
There are some nice touches made, however, my main focus is on the "View Task Details" form itself. The primary function I am trying to achieve here is to create a relationship (not table relationship) between my chosen main task, and several other tasks on the task list. This will primarily be done through the form "view task details".

On Issue 1 - I mentioned that on the "View Task Details" form, I want to have the option to highlight a related task (in the related task list box) and remove it from the listbox. I cannot see this has been done.

I did see that you added a delete record on the DB Task List which was a nice touch thank you.

On Issue 2 - I am still having to refresh the "view task details" form every time I select a new task to the listbox. Just to clarify, when I have open the "view task details" form, I click on the add related task button to link another task to this main task. When a selection has been made and I save+close, I have to manually refresh the "view task details" window to see the changes

On Issue 3 - I am still unable to add more than one related task to my main "task" in the "view task details" listbox

On Issue 4 - On viewing the list of "related tasks" on the "view task details" form, I would like to double click on any one of the related tasks in the list box and have that open that specific record

Thanks.

Hi
Your logic is all wrong.

If I have a table that contains a number of Tasks ie your tblTasks and I then want to add a related task.

Then you would have a table that is related to tblTasks ie tblRelatedTasks

You only have 1 table so as far as I am aware the process will not work.