Update Previous Record From Single View Subform

[jwleonard]

I have a subform that is in single form view; it updates a table that shows workorder statuses. Each record has the status, a start date, and an end date. The way I would like it to work is the user selects a status, then the start date is set to Now(), and the end date is left blank until the next status change. I can't seem to find a way to edit the previous record end date when the new record is added for the next status.
Example:
Record 1 - Status: A, Start: 14 FEB 2007 7:00AM, End: 15 FEB 2007 3:45PM
Record 2 - Status: B, Start: 15 FEB 2007 3:45PM, End:

Notice the End date for record 1 and the Start date for record 2 match becuase they were updated at the same time. The most current end date is left blank until a new status is added.

The goal is for the user to only have to select the appropriate status from a combo box and have the dates fill automatically.

Thanks
Jeff

 

[MStef]

Hello Jeff!

As I can understand you, you have got only one record with empty
END DATE field, (it is always a previous record). If it is so, try to do it
with a UPDATE QUERY, in Form_AfterUpdate event.

 

[jwleonard]

I tried an update query but I have the database normalized and all of the statuses are in the status table. I tried to create the update query with three fields:
-Status ID (Autonumber for each status primary key)
-Workorder ID (Autonumber for the workorder table primary key) Foriegn key for relationship
-End (End date I am trying to add)

I used the criteria as follows:
StatusID: tried to use not equal to current status ID on subform
WorkorderID: Equal to subform workorder ID
End: IsNull / Calculation = Now()

It failed to work for two reasons; because the current status ID is removed from the block on the form to get ready for a new record. The other is that the status ID I need to add the end date will not always be current status ID minus 1. The status ID's are used for all workorders so current minus 1 might be for another workorder.

Hope all that makes sense.

 

[jwleonard]

I have solved my original issue, I used an update query as MSTEF suggested. However, now I have another problem to solve!

Here is what I used:
DoCmd.RunSQL "UPDATE TBLStatus Set [End] = Now() WHERE IsNull([End]) AND (TBLStatus.WorkorderID)=[Forms]![frmWorkorders]![sfrmChangeStatus]![WorkorderID] AND TBLStatus.Start < DateAdd('s',-2,Now())"

This will work most of the time; I haven't had it fail to work yet but the potential is there.

I originally had the last criteria as TBLStatus.Start <> Now(). The problem with that was if Access froze during the update or the update was started at the end of a second then it would not only update the old record but also the new one.

I made the change above and subtracted the 2 seconds from now() to provide some padding for delays in updating. The reason I chose 2 seconds was due to design there is no way to make a change in less than 2 seconds.

Does anyone know of a way to prevent updating the incorrect record if there is a delay of more than two seconds or someone manages to perform two updates less than 2 seconds apart?

Or does anyone know of a better way to do this in general?

 

[jwleonard]

I found another way to make this work and it will work correctly everytime. I created a subform to show the current status. It is a top value that only shows records for the current workorderID and has a hidden field for StatusID. I used <> to statusID as part of my criteria to replace the dateadd in the original query. This works perfectly.