prevent user leaving a control until a valid entry has been entered

[CJ_London]

This one has got me baffled.

I'm trying to validate an entry in a control in the control before update event. In this case it is a function to ensure a user enters a valid UK date. The reason is because of the differences between UK and US dates and the propensity to change to a us format. e.g. a user enters 29/02/19, access will reinterpret that as 19/02/29 - a valid US date, but not what the user wants - they have made an incorrect entry.

Function isUKDate(optional ctrl as Control) As Boolean
Dim da() As String

 
    if ctrl is nothing then Set ctrl = Screen.ActiveControl
   
    If InStr(ctrl.Text, "/") > 0 Then 'this is dd/mm/yy format
       
        da = Split(ctrl.Text, "/")
        If UBound(da) = 2 Then 'has the right number of elements
            Select Case Val(da(1))
                Case 1, 3, 5, 7, 8, 10, 12
                    isUKDate = da(0) < 31
                Case 4, 6, 9, 11
                    isUKDate = da(0) < 31
                Case 2
                    isUKDate = da(0) < 28 - (da(2) Mod 4 = 0) 'no leapyear on the century, but not going to worry about that
                Case Else
                    isUKDate = False
            End Select
        Else
            isUKDate = False
        End If
       
    ElseIf InStr(ctrl.Text, "-") > 0 Then 'this is dd-mmm-yy format
   
        da = Split(ctrl.Text, "-")
        If UBound(da) = 2 Then 'has the right number of elements
            Select Case da(1)
                Case "Jan", "Mar", "May", "Jul", "Aug", "Oct", "Dec"
                    isUKDate = da(0) < 31
                Case "Apr", "Jun", "Sep", "Nov"
                    isUKDate = da(0) < 31
                Case "Feb"
                    isUKDate = da(0) < 28 - (da(2) Mod 4 = 0) 'no leapyear on the century, but not going to worry about that
                Case Else
                    isUKDate = False
            End Select
        Else
            isUKDate = False
        End If
       
    Else
        isUKDate = False
    End If
   
    If Not isUKDate Then
        MsgBox "Not a  UK date, please reenter", vbOKOnly
       
        'On Error Resume Next
        'ctrl.Text = ""
        'ctrl.Undo
        'ctrl.Cancel = True
       
'        ctrl.Parent.Text2.SetFocus
'        ctrl.SetFocus
       
    Else
        'ctrl = Format(ctrl, "dd-mmm-yyyy")
       
    End If
End Function

So I now have an isUKDate function which reports the date is invalid. So far so good. The problem comes that once the user has clicked the OK button, I cannot get the cursor to stay on the control - you can see my attempts in the commented out section at the bottom. Note that for this to work, the format cannot be set (to a date type) and the calendar has to be disabled.

Anybody any thoughts?

[Missinglinq]

If a Control fails a test...to get the Focus to remain on the Control...in the Control's BeforeUpdate event, use the command

Cancel = True

Linq ;0)>

[June7]

So users should enter dates as DD/MM/YY?

Recommendations I have read are to use UNBOUND textbox for date entry and then use VBA to save the date correctly. Maybe even use a separate textbox for each date part. This is why so many web forms have separate inputs for date parts.

[CJ_London]

@linq - thanks for that, I will give it a try - there are 100's of date fields across a large number of forms so I was hoping to get away with =isUKDate() as the event rather than using [Event Procedure] - which means you don't have the beforeupdate cancel parameter

@June - yes - the potential date range covers a number of years, so the calendar option isn't viable and for the same reason setting a date range would be too wide as to be not worth doing. The client is in the UK so is used to entering dates in that format.

[Micron]

which means you don't have the beforeupdate cancel parameter

I disagree. BeforeUpdate calls the function; function returns False (is not of the correct format), BeforeUpdate cancels because the return value is false, thus you Undo.

[ArviLaanemets]

I am missing here something or what? I also use non-US date format ("dd.mm.yyyy", what is also regional setting for short date for most of users) in my applications - without any problems!

I define the table field as date;
On form I format the control linked to date field as "dd.mm.yyyy".

It's all what is needed - users enter dates without any problems.

[CJ_London]

the problem is not users entering dates - it is entering a 'wrong' one. for example if you enter 30/02/19, access converts to US format of 19/02/30. I want users be required to enter a correct date

[ArviLaanemets]

the problem is not users entering dates - it is entering a 'wrong' one. for example if you enter 30/02/19, access converts to US format of 19/02/30. I want users be required to enter a correct date

And what happens when user enters 01/02/19? Will it be 1st February of 2019, or 19th February of 2001?

OK, I had a closer look at it. It looks like Access doesn't allow "foreign" date formats for form controls - only regional settings formats are allowed. When regional settings are not defined, by default US settings are used.

You set the date format for control as Short Date. The user has to set the short date format in his/her windows regional settings as "DD/MM/YY". After that he/she can enter dates in this format (at least I think it is so).

[accesstos]

You can use the PreviousControl property of the Screen object.

If Not isUKDate Then
        MsgBox "Not a  UK date, please reenter", vbOKOnly
        Set ctrl = Screen.PreviousControl
        ctrl.SetFocus 
        [...]

[Minty]

I'm with Micron;

Private Sub txtDateUnbound_BeforeUpdate(Cancel As Integer)


    If isUKDate() Then Exit Sub
    Cancel = True


End Sub

Only slight fly in the oikment - your date function fails on 28/02/19 should be <=28 I think except for the leap year

[Micron]

I think the way you have it, the cancel won't execute because the exit comes first. But thanks for the moral support!

Private Sub txtDateUnbound_BeforeUpdate(Cancel As Integer)

If Not isUKDate Then
  Cancel = True
  Me.txtDateUnbound.Undo
  Exit Sub

End Sub

[Minty]

@Micron I'm using

IsUKdate = True then Exit

Rather than Not IsUkDate

It does work, honest, I tested it and everything...

[isladogs]

What date was this posted?

Anyway, just to offer an alternative, how about using my 'better date picker' http://www.mendipdatasystems.co.uk/b...ker/4594398118.
This allows users to switch years as well as months and because its a calendar form will prevent any possibility of anyone entering an invalid UK date that can be misinterpreted to a US date.

Talking about leap years, there was a very 'interesting' story yesterday about Office 365 subscriptions in 2020 when there are of course 366 days.
In case anyone missed it, see https://office366extra.com/faq.aspx

[Micron]

That's a good one. I bet they hooked a few!

[CJ_London]

Thanks guys, yeah I missed <28, also<31, but the issue is around cancelling. I suggested an alternate calendar but they don’t want to do that. Previous control won’t work in this case- the last control doesn’t have a next one. I’ll try reversing the ligic per minty’s suggestion - something I often preach but didn’t practice in this occasion