Error Message Handling

[Paul Cooke]

Hi Guys could someone advise me on this please. I keep getting the error message on some date / time controls even though I have code behind them to stop this...

Const INPUTMASK_VIOLATION = 2279 Or 2113
Dim msg As String

   If DataErr = INPUTMASK_VIOLATION Then
   Select Case Screen.ActiveControl.Name
    Case "txtTreatmentDate"
        Beep
        MsgBox "Please enter 6 digits for dates e.g. 241270 (ddmmyy)." & vbCrLf & "" & vbCrLf & _
        "Please click ok to continue.", vbInformation, "Incorrect Data Entered"
    Case "txtTreatmentTime"
        Beep
        MsgBox "Please enter 4 digits for time e.g. 2359 (HHMM)." & vbCrLf & "" & vbCrLf & _
        "Please click ok to continue.", vbInformation, "Incorrect Data Entered"
    Case Else
        msg = "Incorrect data has been entered in a field, please check and correct data"
        msg = msg & Screen.ActiveControl.Name & "!"
        End Select
        
        response = acDataErrContinue

End If

This code works fine if for example in the date field the users enters just "12/01" (Case "txtTreatmentDate") telling the user to use 6 digits but if I entered a value that is wrong for example 32/12/10 the default error message comes up 'The value you entered is valid for this field'

I thought I had handled this with acdataerrcontinue?

Any help or advice will be gratefully recieved - many thanks

ps the input masks are

1. date - 00/00/00;0;_
2. time - 00:00;0;_

 

[DCrake]

Try checking for IsDate() after user input

 

[Paul Cooke]

Thanks David - I've never seen that before could you explain what you mean by try checking please?

thanks

 

[vbaInet]

Basically, the error is not an input mask error but a data validation error which occurs when Access attempts to save the incorrect date into the date/time field.

Validate the entry in the Before Update event of the control using IsDate() and set Cancel = True if IsDate() is false. Cancel is the only parameter in that event.

 

[Paul Cooke]

Many thanks for the explanation but I must not be writing this correctly would you mind showing me an example of it in code please - thanks

 

[vbaInet]

Perhaps show us what you've written instead?

 

[Paul Cooke]

as below - thanks again

Private Sub txtTreatmentDate_BeforeUpdate(Cancel As Integer)
If (IsDate(txtTreatmentDate)) = False Then
MsgBox "Please enter a valid date using numbers only.", vbInformation
Cancel = True
End If
End Sub

 

[vbaInet]

I guess "32/12/10" is a valid date in the form "yy/mm/dd" or "yy/dd/mm" so IsDate() will be true.

However, are you positive the error is generated from this control and/or field? What is the data type of the field?

 

[Paul Cooke]

yep I am pretty positive the error is coming from that control which is set as Plain Text

(just for information this error is happening on the same type of controls on other related forms as well but I'm thinking if I can sort this one out I can apply the "fix" to the rest).

So to update

If I enter a text value in the control I now get I get my Select Case MsgBox

If I enter less than 6 digits I get my Select Case MsgBox

but if I enter say 35/35/35 this is wnen I still get the default erro message.

I am actually starting to think I am trying to fix a problem that won't actually occur that often so maybe it is ok as it is unless there is an easy fix?

another quick question if you don't mind can I force the control to accpet the data entry in order e.g. 12/01/11 where 12 is the date, 01 is the month and 11 is the year - because at the moment if I enter 311111 and press enter / tab the value changes to 11/11/1931 (the error here is that there are only 30 days in November)

Thanks again for your help and advice

 

[gemma-the-husky]

the problem is that 32/12/10 is not a valid date

the format passes your input mask test - but no doubt your textbox is bound to a date type field - or is formatted as short date - and therefore the ACTUAL VALUE is "'The value you entered is NOT valid for this field' (I presume you meant not valid)

 

[vbaInet]

Actually it is a valid date in the form "10/12/1932".

The problem is with the Format applied. Amended code:

Const INPUTMASK_VIOLATION = 2279
[COLOR=Red]Const INCORRECT_FORMAT = 2113[/COLOR]

If DataErr = INPUTMASK_VIOLATION Then
    Response = acDataErrContinue
    
    Select Case Screen.ActiveControl.Name
        Case "txtTreatmentDate"
            Beep
            MsgBox "Please enter 6 digits for dates e.g. 241270 (ddmmyy)." & vbCrLf & "" & vbCrLf & _
                    "Please click ok to continue.", vbInformation, "Incorrect Data Entered"
        Case "txtTreatmentTime"
            Beep
            MsgBox "Please enter 4 digits for time e.g. 2359 (HHMM)." & vbCrLf & "" & vbCrLf & _
                    "Please click ok to continue.", vbInformation, "Incorrect Data Entered"
        Case Else
            MsgBox "Incorrect data has been entered in a field, please check and correct data" & _
                    Screen.ActiveControl.Name & "!"
    End Select
    
ElseIf DataErr = [COLOR=Red]INCORRECT_FORMAT[/COLOR][COLOR=Red] [/COLOR]Then
    Response = acDataErrContinue
    
    Select Case Screen.ActiveControl.Name
        Case "txtTreatmentDate"
            Beep
            MsgBox "Please enter a valid date" & vbCrLf & "" & vbCrLf & _
                    "Please click ok to continue.", vbInformation, "Incorrect Data Entered"
        Case "txtTreatmentTime"
            Beep
            MsgBox "Please enter a valid time." & vbCrLf & "" & vbCrLf & _
                    "Please click ok to continue.", vbInformation, "Incorrect Data Entered"
        Case Else
            MsgBox "Incorrect data has been entered in a field, please check and correct data" & _
                    Screen.ActiveControl.Name & "!"
    End Select
End If