Generate a Due date with a start date from 1 table and a completion date from another

[jjake]

I have 2 Tables.

Tbl1
TaskID
Task
EquipmentID
Frequency
StartDate

Tbl2
TaskCompletionID
TaskID
DateCompleted

Frm1 is a filtered form with subfrm1 which shows all tasks associated with a specific piece of equipment with a query to also display a field "DueDate". All fields are locked

frm2 is an editable version of frm1 & subfrm1 that pulls data from tbl1. It allows me to add new tasks as well as edit existing tasks. Here i have a field with the name "StartDate"

frm3 & subfrm3 is an editable form which displays a specific record from subfrm1. It allows me to put in completion dates of the specific task. Subfrm 1 then filters these results by "MAxofcompletiondate" to only show the latest result.

My problem is if i add a new Task on frm2/subfrm2 and give it a start date, frm1/Subfrm1 filter the result and will not display it because it does not have a completion date yet. This poses a problem when it comes to a user entering data because they then cannot enter a completion date because the record will not display until it has one. Maybe i set my forms up incorrectly? Or how would i filter the results in frm1/subfrm1 so that if a start date is entered but it doesn't have a completion date, it would still show.

Hopefully this makes sense.

 

[CJ_London]

Hopefully this makes sense.

Not really, -kind of see the problem, but nothing provided to help see where the problem is. Suggest post a screenshot of your relationships in the first instance.

Also bear in mind the general rule is one form, one table. If your forms have multi table queries they are probably not updatable.

 

[jjake]

I have attached 3 pictures. If you look at picture Equipment tasks (Editable form(Table)) you can see that entry TaskID# 2 is present. If you go to picture Equipment with completions (Non Editable version(Query)), the entry is no longer present because I haven't given it a completion date yet per Task Details so it's filtered before I can.

The task details is viewed by clicking on the task, but if it's not there to begin with I can't click it.

 

[CJ_London]

from your descriptions in your first post which is frm1, frm2 and frm3?

In principle there is nothing wrong with your relationships although you are not enforcing referential integrity - which means you could create a record in PMTask which is not associated with a record in equipment.

can you explain this in more detail - what is the recordsource? what are the filters?

frm1/Subfrm1 filter the result and will not display it because it does not have a completion date yet

I suspect you need tomake the join between PMTask and PMTaskDetails a left join

 

[jjake]

frm1 = "Equipment with completions" - This is the first viewable form which is filtered by the equipmentID. It will only show tasks related to that piece of equipment and allows the user to view the due dates side by side. It's control source is from a query to allow the MaxOfcompletionDate to work. From this form the user has 2 options. They click the task itself (Which opens "Task Details") To add completions dates to a set task. Or they click "Edit" which opens "Equipment Tasks" to allow them to add new tasks or edit.

frm2 = "Equipment Tasks" - This form is an editable form that comes straight from the table. It allows the user to add new tasks or edit existing ones. If a user adds a new task from this form. ( I Think my whole problem here arises from the fact the start date has no effect on the due date)

e.g.
I add a new task, and I assume a start date. Essentially there should be no completion date yet because it hasn't been performed. So the due date should really calculate from the start date, not the completion date. My problem is I have a continuous form. So I can either show results calculated from the start date, or from the completion date. I decided on the completion date.

Frm3 - "Task Details" - This form allows the user to enter completion dates per the specific task and uses this data to display in frm1. It uses "Maxofcompletiondate" to get the latest result.

 

[CJ_London]

recordsource and filters?

 

[jjake]

recordsource and filters?

frm1 on open filter by equipmentID, recordsource - Equipment (Table)
Subfrm1 recordsource -

SELECT PMTask.TaskID, PMTask.EquipmentID, PMTask.Task, PMTask.StartDate, PMTask.Frequency, Max(PMTaskDetails.CompletionDate) AS MaxOfCompletionDate
FROM PMTask INNER JOIN PMTaskDetails ON PMTask.TaskID = PMTaskDetails.TaskID
GROUP BY PMTask.TaskID, PMTask.EquipmentID, PMTask.Task, PMTask.StartDate, PMTask.Frequency;

frm2 on open filter by equipmentID, recordsource - Equipment (Table)
subfrm2 recordsource - PMTask (Table)

frm3 on open filter by TaskID, recordsource - PMTask(Table)
subfrm3 recordsource - PMTaskdetails (Table)

 

[CJ_London]

as I thought - on your subform recordsource, change the INNER JOIN to LEFT JOIN

 

[jjake]

Ok so that worked great, on my test database. On my main database it's a very similar setup but more to the SQL. The problem I have on this one is that the records with completions dates open in taskdetail. But the records without a completion date open up to a blank page.

SELECT tblPMTasks.TaskID, TblEquipment.EQUIPMENTNAME, tblPMTasks.ID, tblPMTasks.PMCATeGORY, tblPMTasks.PMTYPE, tblPMTasks.TASK, tblPMTasks.TaskProcedure, tblPMTasks.Notes, tblPMTasks.Frequency, tblPMFrequency.Count, tblPMFrequency.FrequencyID, Max(tblPMTaskCompletions.CompletionDate) AS MaxOfCompletionDate
FROM ((tblPMFrequency INNER JOIN tblPMTasks ON tblPMFrequency.FrequencyID = tblPMTasks.Frequency) LEFT JOIN tblPMTaskCompletions ON tblPMTasks.TaskID = tblPMTaskCompletions.TaskID) LEFT JOIN TblEquipment ON tblPMTasks.ID = TblEquipment.ID
GROUP BY tblPMTasks.TaskID, TblEquipment.EQUIPMENTNAME, tblPMTasks.ID, tblPMTasks.PMCATeGORY, tblPMTasks.PMTYPE, tblPMTasks.TASK, tblPMTasks.TaskProcedure, tblPMTasks.Notes, tblPMTasks.Frequency, tblPMFrequency.Count, tblPMFrequency.FrequencyID
ORDER BY tblPMTasks.TaskID, tblPMTasks.PMCATeGORY;

 

[jjake]

Never mind I got it figured out.

<H1 style='FONT-SIZE: 18pt; FONT-FAMILY: "Trebuchet MS", Helvetica, sans-serif; WHITE-SPACE: normal; WORD-SPACING: 0px; TEXT-TRANSFORM: none; FONT-WEIGHT: normal; COLOR: rgb(0,0,102); FONT-STYLE: normal; ORPHANS: 2; WIDOWS: 2; LETTER-SPACING: normal; LINE-HEIGHT: normal; TEXT-INDENT: 0px; font-variant-ligatures: normal; font-variant-caps: normal; -webkit-text-stroke-width: 0px'>Why does my form go completely blank?

The entire Detail section of a Microsoft Access form goes blank sometimes. The text boxes you see in design view disappear when you go to use the form, leaving nothing but empty space.
The Cause

It happens when both these conditions are met:

• There are no records to display, and
• No new records can be added.