Long Question, short answer??

[bgcogen]

Hi.
I was wondering if someone could answer this question for me??

I have a form that takes in a bunch of data. My company has all these machines that sometimes are turned off. There are 5 different reasons they can be offlanned outage, Forced outage, Routine Inspection, Derated hours and Deactivation.
Basically the user uses a pull-down combo-box to specify which one it is. I then want Access to calculate the time and store it in the approprate field in the Master table.
This is the code I wrote for the form but it doesn't work. Any suggestions??

Private Sub Incident_Type_Change()

Select Case Me.[Incident Type]

Case "Planned outage"
Me.[Planned_outage] = Abs(DateDiff("n", [Time of fault], [Resumption time]))

Case "Forced outage"
Me.[Forced_outage] = Abs(DateDiff("n", [Time of fault], [Resumption time]))

...
...
...
... and so on for the other 3

End Select
End Sub

I also tried it with [OnUpdate]

It may also be because the user selects that type of incident BEFORE they enter in the stop and start times???

Thanks a million,
Declan

 

[David R]

Declan, those fields [Time of fault] and [Resumption time] will exist later on, right? If so, you never need to store the calculation. Base anything that needs the calculated time on a query with the additional field:
Downtime: DateDiff("n",[Time of fault], [Resumption time])
(If you are getting negative time, just reverse the two in the calculation. No need for Abs() at all.)

You've got a data normalization problem which is why you're having difficulty and needing code to solve it. You shouldn't have five fields for Downtime, you should have ONE and a Incident_Type field. Store downtimes in a subtable of your main machine table, related one-to-many.

subtable:
[MachineID] (Foreign Key to main table PK)
[Incident_Type]
[Time_of_Fault]
[Time_of_Resumption]
any other fields related to the specific downtime problem...who fixed it, maybe? who reported?

One more thing: Try to avoid spaces in field names. Either_use_underscores or jam all the words together, with capitals to MakeItLegible.

[This message has been edited by David R (edited 05-03-2002).]