Solved Working with time entry...

[Garren.Shannon]

Hey all,

Still working on a Ridership database. On my trip entry form, drivers are to put in the start and end times. I have the fields set to 24hr clock which is helpful but I am wondering if there is a way to capture (to help speed up entry) forms of entry and convert them into proper form.

For example, the driver may enter "9 15" instead of "9:15"... or 8 rather than 8:00 am. The trip entry form has two text boxes (Depart and Return) that link to DepartTime and ReturnTime in the Trips database.

Is there any code I can use to interpret maybe a short list of variations of time entry to keep it simple but uniform?

Thanks in advance to the brain trust.

 

[arnelgp]

you can "overlay" those 2 dates with "2 dummy" textbox when it got the focus.
example on DepartTime textbox, overlay another textbox (txtDummyDepart).
when depatTime get focus, shift the focus to txtDummyDepart and enter the number there.
when you Exit txtDummyDepart, whatever is in the textbox get reflected to departTime textbox.

 

[The_Doc_Man]

Is there any code I can use to interpret maybe a short list of variations of time entry to keep it simple but uniform?

When you have that much variation, you need to take one of a few approaches.

1. If your user doesn't enter enough characters, turn the data entry field red and beep and put up a "use format hh:nn" - or something similar

2. If you can't prompt the user, then you will have to make a list of the ways you have seen the input and look for prevalent formatting cues. That "short list of variations" would eventually lead you back to #1 for the extreme cases.

3. When the user sets focus into that field, pop up a message saying "Please use format hh:nn" - not as a message box, but perhaps as an overlay to some adjacent data field. When the user leaves that field, hide that formatting message.

Users are going to be the bane of your existence unless you condition them to not be.

 

[Garren.Shannon]

Wise words doc.

I do have a check for a 5 min increments but that is one crazy line of code. I am using validation rules in the table... but this sucks. Not elegant AT ALL.

Second([DepartTime])=0 And (Minute([DepartTime])=0 Or Minute([DepartTime])=5 Or Minute([DepartTime])=10 Or Minute([DepartTime])=15 Or Minute([DepartTime])=20 Or Minute([DepartTime])=25 Or Minute([DepartTime])=30 Or Minute([DepartTime])=35 Or Minute([DepartTime])=40 Or Minute([DepartTime])=45 Or Minute([DepartTime])=50 Or Minute([DepartTime])=55)

Is there a way to "pad" a : or "00 if it's missing? The field is a date field so I don't think there is a way to do this. I just know the drivers... and yes, they are my bane.

I tried to integrate a Time picker. Struggled with getting it to work right so I abandoned it. I might give that another try. That would definitely cut out a lot of the incorrect data entry.

 

[Eljefegeneo]

I do not know if this will be of any help, but I used this for inputting telephone numbers (10 digit numbers) so that regardless of what the user entered, the correct format would be shown. I put in an unbound text box where the user could enter the ten digits in any format that I could think of. You may need to add one or two to suit your needs. This text box only appeared in a new record. After entering the “telephone number” I had them double click on the unbound box and the correct format would appear in the [Telephone] control. You’ll have to adapt the double click code to suit your format. Since there are many more variations to your needs, i.e. AM vs. PM or 24 hour code. But it is a start.

Function MyReplace(ByVal sInput As String) As String
MyReplace = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(sInput, "_", ""), "@", ""), "$", ""), "%", ""), "!", ""), ".", ""), ")", ""), "(", ""), "-", ""), " ", "")
End Function

Private Sub txtel_DblClick(Cancel As Integer)
Dim sInput As String
  Dim nInput As String
     sInput = Me.txtel
         nInput = MyReplace(sInput)
            Me.Telephone = Format(nInput, "(###) ###-####")
               Me.Email.SetFocus
           Me.txtel.Visible = False
End Sub

 

[Garren.Shannon]

you can "overlay" those 2 dates with "2 dummy" textbox when it got the focus.
example on DepartTime textbox, overlay another textbox (txtDummyDepart).
when depatTime get focus, shift the focus to txtDummyDepart and enter the number there.
when you Exit txtDummyDepart, whatever is in the textbox get reflected to departTime textbox.

Not sure what you mean Arnelgp. What would the dummy text boxes do for correcting the time entry?

 

[Garren.Shannon]

I do not know if this will be of any help, but I used this for inputting telephone numbers (10 digit numbers) so that regardless of what the user entered, the correct format would be shown. I put in an unbound text box where the user could enter the ten digits in any format that I could think of. You may need to add one or two to suit your needs. This text box only appeared in a new record. After entering the “telephone number” I had them double click on the unbound box and the correct format would appear in the [Telephone] control. You’ll have to adapt the double click code to suit your format. Since there are many more variations to your needs, i.e. AM vs. PM or 24 hour code. But it is a start.

Function MyReplace(ByVal sInput As String) As String
MyReplace = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(sInput, "_", ""), "@", ""), "$", ""), "%", ""), "!", ""), ".", ""), ")", ""), "(", ""), "-", ""), " ", "")
End Function

Private Sub txtel_DblClick(Cancel As Integer)
Dim sInput As String
  Dim nInput As String
     sInput = Me.txtel
         nInput = MyReplace(sInput)
            Me.Telephone = Format(nInput, "(###) ###-####")
               Me.Email.SetFocus
           Me.txtel.Visible = False
End Sub

OK... I see what you did there. I guess I could train them to enter only numbers, then assume the last 2 were min's and the remainder hours and work from there. Still, a lot of assumptions. There are so many ways users can screw up entering time. But you're giving me some good ideas. Thanks...

 

[arnelgp]

here is a demo.
when you go to the DepartTime/Return time, another textbox will get focus (the colored one).
when you leave the colored textbox, the original textbox is updated.

 

[CJ_London]

Have you considered using an input mask of 00:00?

also set the format property to 00\:00;;"00:00";"00:00"

if the control source is a a date field, values will be stored correctly

this will show the user what is expected for the data entry, won't allow for non valid times

because it is a 24hr clock you will need to check that the finish time is later than the start time (with further checks if the run time can go over midnight)

 

[Garren.Shannon]

here is a demo.
when you go to the DepartTime/Return time, another textbox will get focus (the colored one).
when you leave the colored textbox, the original textbox is updated.

Interesting Arnelgp... so the overlay unbound text box provides the default template? in this case, 8:00 am? For T1, I don't see in the property sheet how that is accomplished.

 

[Garren.Shannon]

Have you considered using an input mask of 00:00?

also set the format property to 00\:00;;"00:00";"00:00"

if the control source is a a date field, values will be stored correctly

this will show the user what is expected for the data entry, won't allow for non valid times

because it is a 24hr clock you will need to check that the finish time is later than the start time (with further checks if the run time can go over midnight)

Thanks CJ... I do have check to ensure the return time is after the depart time. The input mask is a great suggestion. That I will add.

Where is the format property set? In the table or the form?

 

[CJ_London]

Where is the format property set? In the table or the form?

either - although the general recommendation is to leave table fields unformatted so you don't hide data with the formatting (usual examples are dates (formatting can hide the time element, or visa versa) and decimal numbers (a value of 1.23456 formatted to show 2dp 1.23) will appear to add up incorrectly. So my recommendation would be the form

 

[Garren.Shannon]

Thanks CJ... good advise. I actually set all format checking in the table but your suggestion makes perfect sense so I will modify and move it to the form.

 

[moke123]

I have a time picker class in this example

 

[Garren.Shannon]

OK... that is cool. I tried importing something like this before and missed a critical step that made the thing work kind of wonky. What are the steps to properly import this?

 

[moke123]

import frmTimePicker and clsTimePicker.

add a button next to your textbox for the time field.

in the forms module declarations section put - dim clsTP as clsTimePicker

Option Compare Database
Option Explicit

Dim clsTP As clsTimePicker

In the click event of your button

Private Sub btoClock_Click()

    Set clsTP = New clsTimePicker

    clsTP.InitTimePicker Me.txtTime, 5

End Sub

replace me.txtTime with the name of your textbox. the 2nd argument is the minute incriments going up and down.

 

[arnelgp]

another "simple" timepicker.
Time Picker | Access World Forums (access-programmers.co.uk) (post #2 and #4)

 

[Garren.Shannon]

another "simple" timepicker.
Time Picker | Access World Forums (access-programmers.co.uk) (post #2 and #4)

Thanks Arnelgp

 

[Garren.Shannon]

Thanks Arnelgp

Looking at this... it is very cool in deed. Quite a clean approach.