Unwanted repeating value in form

  • Thread starter Thread starter James
  • Start date Start date
J

James

I have a subform that is a continuous form with an Event
Procedure in it. Part of the coding is as follows, the
rest is just Dim and Error.


Hours = Me.End_Time - Me.Start_Time

If Hours < 0 Then
AdHours = Hours + 24
Else
AdHours = Hours
End If

Me.Hours = Hours

The problem is that each time a change the Start or End
time for one record in the form all of the Hours are
updated to the same number of hours.

Name Start End Hours
DJ 5:00 PM 9:00 PM 4.00
Tillman 4:40 PM 9:00 PM 4.00
Omolayo 4:50 PM 9:00 PM 4.00
Fadina 4:45 PM 9:00 PM 4.00
Mayo 4:45 PM 9:00 PM 4.00

DJ 5:00 PM 9:00 PM 5.83
Tillman 4:40 PM 9:00 PM 5.83
Omolayo 4:50 PM 9:00 PM 5.83
Fadina 4:45 PM 9:00 PM 5.83
Mayo 4:45 PM 9:00 PM 5.83

Does anyone know what I need to do to fix this? I am fine
for the after midnight test. It is just the rolling Hours
that needs fixing. And please state things simple. I am
new to access and VBA.

Thanks
James
 
Hi James,

It sounds like Hours might be an unbound control. An unbound control can
only have a single value even though it is shown once per row on a
continuous form. Instead of using an unbound control you could create a
calculated field on the query - I would also suggest using the DateDiff
function:

Hours: datediff("H",[End_Time],[Start_Time])
 
James,

It looks like your Hours textbox on the form is an unbound control.
Am I right?

I think the basic concept of what you are trying to do is ill-advised.
My suggestion is to scrap the code stuff, and use an expression in the
ControlSource of the Hours textbox, like this...
=([End Time]-[Start Time]-([Start Time]>[End Time]))*24
That way, the hours will be evaluated separately for each record.

- Steve Schapel, Microsoft Access MVP
 
Back
Top