13:00 through 13:59 is apparently less than 12:59

[UserX]

A while ago now, June7 provided this code to me to allow users to update a table through a textbox, and then to update the listbox from which the record to update is selected.

CurrentDb.Execute "UPDATE tblSign_in SET TimeHelped=#" & Me.Txt69 & "# WHERE ID=" & Me.Lst60.Column(10)

This code works wonderfully. As time has passed, my db has evolved and I needed to add some reinforcement. I needed to force some common sense upon users. The following modified code forces users to update the record with a time in the "TimeHelped" field that occurs after the "Time_In" time.

If Me.Txt69 & "# WHERE ID=" & Me.Lst60.Column(10) > Me.Lst60.Column(6, Lst60.ListIndex + 1) Then
     CurrentDb.Execute "UPDATE tblSign_in SET TimeHelped=#" & Me.Txt69 & "# WHERE ID=" & Me.Lst60.Column(10)
     Else
     MsgBox "Time Helped cannot be before Time In.", vbOKOnly
     End If

Because time values are just serial numbers, the users must specify times using military format, to capture the correct time of day after 12:59 PM. Oddly though, Access doesn't recognize that any time between 13:00 and 13:59 are greater than 00:01 through 12:59. See for yourself in the attached sample db.

To verify my concern, open the db and then perform the following steps:

1. Enter a military time value within the 1300 hour
2. Click on Update
3. See error msg box appear

Now try steps one and two above with a military time after 13:59, and you'll see the time updated with what you put into the box.

My modified code will update using the 1300 hour only if the "Time_In" value is in the 1300 hour. I.E. if "Time_In" is 13:00, and you type in 13:01 into the box, the above code will update the record to show a "TimeHelped" value of 13:01.

Would anyone care to shed some light on this subject? I'd like to be able to use the 1300 hour.

[June7]

I don't understand form/subform structure. Both are bound to the same table. Why use subform?

That If Then condition expression doesn't make any sense to me. Try:

If Me.Txt69 > CDate(Me.Lst60.Column(6, Lst60.ListIndex + 1)) Then

Have you step debugged? Use Debug.Print lines to display values in the immediate window. Refer to link at bottom of my post for debugging guidelines.

[UserX]

In short, I didn't want to rebuild the existing database.

And now, the complicated answer...

When I started the job for which this db is used, I was put in charge of the database the existing staff referred to as "sign-in". The office used an off-site DBA, whom I imagine created the original version. As time went on, I saw the need to change things around. So, with that I dove in trying to understand VBA and general Access designing. The subform was something that already existed, so instead of creating a new way to sign customers in, I just used the existing form as a subform. If this poor structure bothers you, I'm 100% certain you'd have a breakdown had you seen the way the db was when I began several years ago.

Anyway, thanks for the tip. The suggest code fix fixed my problem.