It might depend on who your users are, but IMHO an input mask will not help.
Firstly, a quick data entry operator can enter some dates with just 3
keystrokes, e.g.:
4/9
and Access supplies the year. You certainly can't do that kind of thing if
you use an input mask.
An alternative solution is to give the user two unbound text boxes - one for
the date, and the other the time. You then programmatically combine the 2
values and assign them to the hidden text box that is bound to the actual
date/time field.
1. Paste these 2 functions into a standard module, where you can use it with
any form:
Public Function DateTimeCombine(txtDate As TextBox, _
txtTime As TextBox, txtCombined As TextBox) As Boolean
On Error GoTo Err_Handler
'Purpose: Assign the value of txtDate + txtTime to txtCombined.
Dim dt As Date
If Not (IsNull(txtDate) Or IsNull(txtTime)) Then
dt = DateValue(txtDate) + TimeValue(txtTime)
If Abs(DateDiff("s", dt, txtCombined)) = 0 Then
'do nothing
Else
txtCombined = DateValue(txtDate) + TimeValue(txtTime)
DateTimeCombine = True
End If
End If
Exit_Handler:
Exit Function
Err_Handler:
MsgBox Err.Description
Resume Exit_Handler
End Function
Public Function DateTimeSplit(txtDate As TextBox, txtTime As TextBox, _
txtCombined As TextBox, Optional bUseOldValue As Boolean) As Boolean
On Error GoTo Err_Handler
'Purpose: Assign to value of txtCombined to txtDate and txtTime.
Dim varDateTime As Variant
If bUseOldValue And Not IsNull(txtCombined.OldValue) Then
varDateTime = txtCombined.OldValue
Else
varDateTime = txtCombined.Value
End If
If IsNull(varDateTime) Then
txtDate = Null
txtTime = Null
Else
txtDate = DateValue(varDateTime)
txtTime = TimeValue(varDateTime)
End If
Exit_Handler:
Exit Function
Err_Handler:
Call LogError(Err.Number, Err.Description, conMod & ".DateTimeSplit")
Resume Exit_Handler
End Function
2. In the Current event procedure of the form, add this line:
Call DateTimeSplit(Me.txtInteractDate, Me.txtInteractTime,
Me.InteractDateTime, False)
3. In the form's Undo event procedure:
Call DateTimeSplit(Me.txtInteractDate, Me.txtInteractTime,
Me.InteractDateTime, True)
4. Set up the AfterUpdate event procedure of the unbound date text box like
this:
Private Sub txtInteractDate_AfterUpdate()
On Error GoTo Err_Handler
'If user cleared the box, reset to the original date value.
If IsNull(Me.txtInteractDate) Then
If Not IsNull(Me.InteractDateTime.OldValue) Then
Me.txtInteractDate = DateValue(Me.InteractDateTime.OldValue)
End If
End If
Call DateTimeCombine(Me.txtInteractDate, Me.txtInteractTime,
Me.InteractDateTime)
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Exit_Handler
End Sub
5. Set up the AfterUpdate event procedure of the unbound time text box:
Private Sub txtInteractTime_AfterUpdate()
On Error GoTo Err_Handler
'If the user cleared the box, reset to the original time value.
If IsNull(Me.txtInteractTime) Then
If Not IsNull(Me.InteractDateTime.OldValue) Then
Me.txtInteractTime = TimeValue(Me.InteractDateTime.OldValue)
End If
End If
Call DateTimeCombine(Me.txtInteractDate, Me.txtInteractTime,
Me.InteractDateTime)
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Exit_Handler
End Sub
