How to Edit a formatted Date/Time field

[Wesley Hader]

I have two Date/Time fields[ClockIN] and [ClockOut] in a table called tblTIMES that each store a date and time formatted as (mm/dd/yy hh:nn:ss AMPM).

I use "Format("ClockI n","mm/dd/yyyy")" and "Format("ClockI n","hh:nn:ss AMPM")" to separate the respective Date or Time where I need to do so.

One of those places is a text box on a form that I want to use to edit the Date and/or time of a specific record.

This does not seem to work.

I can successfully display the formatted date or time, but I cannot edit it. Any suggestions?

[NeoPa]

Wesley:
This does not seem to work.

It wouldn't, as the control is not bound to your field if you do it that way. It has a value set which you cannot change (as that's exactly what you've told it to do by setting the ControlSource property to an expression rather than a field from the RecordSource of the form).

Suggestion:
Don't separate the date and the time. Use the Format property of the TextBox to lay the data out as you would like and bind the TextBox to the field itself (directly). That way everything just works naturally ;-)

[Wesley Hader]

I understand, and that totally makes sense! I wasn't thinking that the TextBox has the ability to Format data built in. Thanks!!

[NeoPa]

It's a pleasure Wesley. That's what sharing experience is all about :-)

[Wesley Hader]

I just realized, This does display the data correctly and allow me to change accordingly. But one small glitch is that when you click inside the formatted TextBox, the entire field is then displayed. Not so user friendly. Is there a way to keep the original formatting from showing up when you click in the TextBox?

[NeoPa]

Can you give examples of what you mean. I'm not following your reference terms.

[Wesley Hader]

Sure. I have attached a sample database to show what I mean. On the form "Format Dates" I have four date/time Text Boxes. They are each formatted to show either Time OR Date but just try clicking inside one of the boxes to see what happens. In my original database I need to be able to edit the records by changing the text in the Text Boxes. You will see how this could become a nuisance.

[NeoPa]

Sorry Wesley, I wasn't really asking for a databsase to look through, just a few examples to illustrate what it is you're saying.

If however, you feel the best/only way for me to see this is within the context of a database then I'm afraid you'll need to follow the instructions found in Attach Database (or other work). I still use 2003 so I cannot open your ACCDB file.

[Seth Schrock]

Here is the 2003 version.

[NeoPa]

Thanks Seth. I don't know if Wesley's original was pared down the way this is but somebody's done a fantastic job of posting just what was required. I guess it was probably Wesley showing great intelligence, but if it was you Seth, then it was a very clever move. Whoever it was did an excellent job is what I'm trying to say.

Now I understand your point Wesley, and having seen it, I feel I was probably a bit dumb not to get it from your earlier comments (which explained the situation well enough in hindsight).

In a situation such as this I would recommend a slightly more complex setup :
For each date have three TextBox controls - A bound but invisible control for the whole value plus two unbound ones for showing the Date and Time values separately. Use DateValue() and TimeValue() to set the values in these controls in the Form_Current() event procedure. After either is updated (with valid date or time values respectively) update the value of the bound control to reflect the change.

Does that make sense?

[Seth Schrock]

All I did was Save As Access 2003. I didn't even look at the database.

[NeoPa]

NeoPa:
In a situation such as this I would recommend a slightly more complex setup :
For each date have three TextBox controls - A bound but invisible control for the whole value plus two unbound ones for showing the Date and Time values separately. Use DateValue() and TimeValue() to set the values in these controls in the Form_Current() event procedure. After either is updated (with valid date or time values respectively) update the value of the bound control to reflect the change.

In accordance with the quoted section above I've written some code that illustrates the type of thing I'm talking about. I use completely different control names from your attached example, but it should get you up and started at least. I'll start with a list of the relevant objects :

Controls :
[txtDateIn]; [txtDIDate]; [txtDITime]
[txtDateOut]; [txtDODate]; [txtDOTime]

You should ensure that only valid entries (which presumably include Nulls) are allowed to be entered into the controls. Entering a Null into a control (or clearing the control) should be treated as a wish to clear the whole date/time value.

Option Compare Database
Option Explicit

Private Sub Form_Current()
    With Me
        If IsDate(.txtDateIn) Then
            .txtDIDate = DateValue(.txtDateIn)
            .txtDITime = TimeValue(.txtDateIn)
        Else
            .txtDIDate = Null
            .txtDITime = Null
        End If
        If IsDate(.txtDateOut) Then
            .txtDODate = DateValue(.txtDateOut)
            .txtDOTime = TimeValue(.txtDateOut)
        Else
            .txtDODate = Null
            .txtDOTime = Null
        End If
    End With
End Sub

Private Sub txtDIDate_AfterUpdate()
    With Me
        If IsNull(.txtDIDate) Then
            .txtDITime = Null
            .txtDateIn = Null
        Else
            .txtDateIn = CDate(.txtDIDate & " " & .txtDITime)
        End If
    End With
End Sub

Private Sub txtDITime_AfterUpdate()
    With Me
        If IsNull(.txtDITime) Then
            .txtDIDate = Null
            .txtDateIn = Null
        Else
            .txtDateIn = CDate(.txtDIDate & " " & .txtDITime)
        End If
    End With
End Sub

Private Sub txtDODate_AfterUpdate()
    With Me
        If IsNull(.txtDODate) Then
            .txtDOTime = Null
            .txtDateOut = Null
        Else
            .txtDateOut = CDate(.txtDODate & " " & .txtDOTime)
        End If
    End With
End Sub

Private Sub txtDOTime_AfterUpdate()
    With Me
        If IsNull(.txtDOTime) Then
            .txtDODate = Null
            .txtDateOut = Null
        Else
            .txtDateOut = CDate(.txtDODate & " " & .txtDOTime)
        End If
    End With
End Sub

PS.

Seth:
All I did was Save As Access 2003. I didn't even look at the database.

That was still helpful, definitely, but the main plaudits then to Wesley for a sensible example database.

[Wesley Hader]

Once again, this works perfect! Thank you! Next time I will read the Attach Database Instructions.

[NeoPa]

Not a problem Wesley - particularly considering I only posted the link after you posted the database ;-)

PS. I would suggest that you reset the Best Answer post in this thread and set it to post #12 instead. Now the actual question is clearer I believe that one more directly pertains to, and answers, it.