OnError 1

[Guest_imported]

I have an access database that keeps tracks of commissions paid to our salespeople at my compnay. When the database opens, a "Main Menu" form appears as do a series of 2 InputBoxes to limit the dates to return in reports and forms. The first InputBox asks for the beginning date wanted and the second asks for the ending date. This works fine, but I have an input mask of "mm/dd/yyyy" because that is how they appear in the queries and tables. If you violate the input mask a run-time error appears with the options of End, Debug, and Help. I don't want this to appear to the user. Rather, I want a message box or something to warn them of their violation and force them to correct it. I know that I can use the OnError Method, but don't know exactly where to put it or how it works. Any help would be appreciated. Here is my code:

Private Sub Form_Open(Cancel As Integer)
Dim strDATEV As String, strDATEV2 As String

DoCmd.OpenForm "frmTEXT", acNormal, , , , acHidden

strDATEV = InputBox("WHAT IS THE BEGINNING DATE?", _
        "PLEASE ENTER FIRST DATE", (Date - 7))
        
Forms![frmTEXT].txtDATEV.SetFocus

Forms![frmTEXT].txtDATEV.Text = strDATEV
        
strDATEV2 = InputBox("WHAT IS THE ENDING DATE?", "PLEASE ENTER SECOND DATE", _
        Date)

Forms![frmTEXT].txtDATEV2.SetFocus
Forms![frmTEXT].txtDATEV2.Text = strDATEV2
End Sub

Thanks

 

[segmentationfault]

You could insert an

 On Error Goto (Whatever error handling label you choose)

but that probably is not the best solution. It is a catch-all that can be more trouble the benefit.

I would suggest adding some lines to force the input into the desired format, and if that is not possible, then request the input again. If you use a form and a textbox, you can even remove bad input beforehand. Even better yet, I think Access has some input validation properties that would do this for you.. but I've only used Access twice.

If you want some code to only accept input in the proper format, I'll be happy to post some.

 

[Guest_imported]

I think your suggestions might work, so I would like to take a look at what code you have.

 

[Guest_imported]

Example:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strDATEV As String, strDATEV2 As String
DoCmd.OpenForm "frmTEXT", acNormal, , , , acHidden

strDATEV = InputBox("WHAT IS THE BEGINNING DATE?", _
"PLEASE ENTER FIRST DATE", (Date - 7))

Forms![frmTEXT].txtDATEV.SetFocus

Forms![frmTEXT].txtDATEV.Text = strDATEV

strDATEV2 = InputBox("WHAT IS THE ENDING DATE?", "PLEASE ENTER SECOND DATE", _
Date)

Forms![frmTEXT].txtDATEV2.SetFocus
Forms![frmTEXT].txtDATEV2.Text = strDATEV2

Exit Sub
ErrorHandler:
'your code goes here
Resume Next 'resume code at next line
End Sub

 

[Guest_imported]

I tried the code and it sort of worked, but not really. I think that the Exit Sub is messing things up. When I put it where you said, access would just stop doing the sub and not display the msgbox. When I took it out, the msgbox would appear, but did not return to the InputBox to correctly input a value. Any suggestions? Here is my code:

Private Sub Form_Open(Cancel As Integer)
Dim strDATEV As String, strDATEV2 As String
On Error GoTo ERRORHANDLER
    
DoCmd.OpenForm "frmTEXT", acNormal, , , , acHidden

strDATEV = InputBox("WHAT IS THE BEGINNING DATE?", _
        "PLEASE ENTER FIRST DATE", (Date - 7))
        
Forms![frmTEXT].txtDATEV.SetFocus

Forms![frmTEXT].txtDATEV.Text = strDATEV
        
strDATEV2 = InputBox("WHAT IS THE ENDING DATE?", "PLEASE ENTER SECOND DATE", _
        Date)

Forms![frmTEXT].txtDATEV2.SetFocus
Forms![frmTEXT].txtDATEV2.Text = strDATEV2

Exit Sub

ERRORHANDLER:
MsgBox "YOU MUST ENTER A DATE IN THE MM/DD/YYYY FORMAT!", , "NOT A VALID FORMAT"
Resume Next


End Sub

Thanks

 

[segmentationfault]

The problem you are having is because your error handler does nothing toward correcting the invalid date before resuming the program.

I'm working on a date validation function. Back in a flash.

 

[segmentationfault]

I hope this works for you.

 ...

While Not DateValidation(strDATAEV)
   strDATEV = InputBox("WHAT IS THE BEGINNING DATE?", _
   "PLEASE ENTER FIRST DATE", (Date - 7))
Wend

 ...

While Not DateValidation(strDATEV2)
  strDATEV2 = InputBox("WHAT IS THE ENDING DATE?", _
  "PLEASE ENTER SECOND DATE", Date)
Wend

 ...

And insert this function in the module or form somewhere.

Function DateValidation(s As String)

    Dim slash1 As Integer
    Dim slash2 As Integer
    
    ' 10 characters in
    ' "mm/dd/yyyy"
    If Len(s) <> 10 Then
        DateValidation = False
        Exit Function
    End If
    
    slash1 = InStr(1, s, &quot;/&quot;)
    ' Assert that the first slash
    ' is present and that it is
    ' the third character (&quot;mm/&quot;)
    If slash1 = 0 Or slash1 <> 3 Then
        DateValidation = False
        Exit Function
    End If
    
    slash2 = InStr(slash1 + 1, s, &quot;/&quot;)
    ' Assert that the second slash
    ' is present and that it is
    ' the sixth character (&quot;mm/dd/&quot;)
    If slash2 = 0 Or slash2 <> 6 Then
        DateValidation = False
        Exit Function
    End If

    ' Assert that &quot;mm&quot; is numeric
    ' and a valid month
    Dim m As String
    m = Left(s, slash1 - 1)
    If Not IsNumeric(m) Then
        DateValidation = False
        Exit Function
    ElseIf Val(m) > 12 Or _
        Val(m) < 1 Then
        DateValidation = False
        Exit Function
    End If
    
    ' Assert that &quot;dd&quot; is numeric
    ' and between 1 and 31
    ' (Sorry February)
    Dim d As String
    d = Mid(s, slash1 + 1, (slash2 - slash1 - 1))
    If Not IsNumeric(d) Then
        DateValidation = False
        Exit Function
    ElseIf Val(d) > 31 Or Val(d) < 1 Then
        DateValidation = False
        Exit Function
    End If
    
    ' Assert that &quot;yyyy&quot; is numeric
    If Not IsNumeric(Right(s, Len(s) - slash2)) Then
        DateValidation = False
        Exit Function
    End If
    
    DateValidation = True
End Function

 

[segmentationfault]

Just thought I would add that while this code is much longer, it allows you to specifically address the issue of date inputs. The "On Error" clauses catch every possible error and try to deal with them in exactly the same way, which I don't like at all.

Also, they persist even after execution leaves that original sub or function, which can make debugging your VBA very difficult.

What's even worse is if someone else (clueless user?) uses your macro and months from now something goes awry (missing files, changing dates, new file formats, etc.) the macro might try to handle new errors with entirely inappropriate handlers. Unless specifically coded to alert the user, this could happen invisibly, making it difficult for them to notify you that something is wrong with it.

 

[Guest_imported]

I got a compile error that said : "ByRef argument type mismatch". It highlighted the (strDATEV) next to the DateValidation. I tried changing the data type but it still came up with the message. Any thoughts? Also, where do I put the while/wends? Before my msgbox? Thanks

 

[segmentationfault]

Hm, I thought that uninitialized strings would be "", but I guess they might not be? Try putting strDATEV = "" and strDATEV2 = "" right after you create them. String is the correct type, so don't change that.

The while/wends should surround the inputboxes as I posted them. I should have been more clear.

 

[Guest_imported]

It still came up with the same compile error message, so I am not sure what to do. I am not really familiar with the DateValidation method.

 

[Guest_imported]

Now I have a serious problem. It finally didn't have the compile error because I changed the variable in the DateValidation argument to match the variable declaration. (i copy and pasted what you wrote, and there was an extra "a" (DATEAV) that totally slipped past me.) Now I opened my form and the first InputBox came up, but will not disappear with cancel or ok button. so I cannot edit anything.

 

[Guest_imported]

Nevermind. I figured it out. The code you wrote requires the 0's to be there. I had 7/4/2002, but I needed to change it to 07/04/2002. Is there a way to get around this in your code so that it will accept the 7/4/2002? Thanks.

 

[Guest_imported]

OK. I think I almost figured it out. slash1 has to be equal to 2 or 3 and slash2 has to be equal to 4,5, or 6. s has to be >= 8. Changing s was easy, but I don't want to mess up the slashes too badly. How would I go about doing that?

 

[Guest_imported]

I think I figured it out, but I know there are probably a few holes in this for invalid values to get through. Here is where I changed your could so that it would work without 0's:

    ' 10 characters in
    ' "mm/dd/yyyy"
    If Len(s) < 8 Then
        DateValidation = False
        Exit Function
    End If
    
    slash1 = InStr(1, s, &quot;/&quot;)
    ' Assert that the first slash
    ' is present and that it is
    ' the third character (&quot;mm/&quot;)
    If slash1 < 2 Or slash1 > 3 Then
        DateValidation = False
        Exit Function
    End If
    
    slash2 = InStr(slash1 + 1, s, &quot;/&quot;)
    ' Assert that the second slash
    ' is present and that it is
    ' the sixth character (&quot;mm/dd/&quot;)
    If slash2 = 0 Or slash2 < 4 Or slash2 > 6 Then
        DateValidation = False
        Exit Function
    End If

Please let me know if you think there may be a better way. Other than that, your code was awesome. Thank you so much. I wouldn't have even known where to begin with that code. Thanks again.

 

[segmentationfault]

Heh, going for a posting record today?

So the specifications are that it should accept mm/dd/yyyy, m/dd/yyyy, m/dd/yyyy, and m/d/yyyy provided that 1 <= mm <= 12 and 1 <= dd <= 31.

It will be easier for me to provide this from scratch than to look over what you changed. It looks like you would accept &quot;1/020/1999&quot; which might cause a problem with the database.

 

[segmentationfault]

Give this a try.

Hm, actually, this is a pretty useful function. If a solution to this is not already in a FAQ somewhere, it would probably be good to keep handy. I know I have written functions to validate dates at least half a dozen times...

Function DateValidation(s As String)

    Dim slash1 As Integer
    Dim slash2 As Integer
    
    Dim d As String
    Dim m As String
    
    slash1 = InStr(1, s, "/")
    slash2 = InStr(slash1 + 1, s, "/")
    
    If Len(s) = 8 Then
        ' Format must be
        ' m/d/yyyy
        
        ' Assert that the first slash
        ' is the second character
        If slash1 <> 2 Then
            DateValidation = False
            Exit Function
        End If
        
        ' Assert that the second slash
        ' is the fourth character
        If slash2 <> 4 Then
            DateValidation = False
            Exit Function
        End If
    
        m = Left(s, 1)
        d = Mid(s, 3, 1)
    ElseIf Len(s) = 9 Then
        ' Format may be
        ' mm/d/yyyy or m/dd/yyyy
        
        ' Assert that the first slash
        ' is character 2 or 3
        If slash1 <> 2 And slash1 <> 3 Then
            DateValidation = False
            Exit Function
        End If
        
        ' Assert that the second slash
        ' is character 5
        If slash2 <> 5 Then
            DateValidation = False
            Exit Function
        End If
        
        m = Left(s, slash1 - 1)
        d = Mid(s, slash1 + 1, (slash2 - slash1 - 1))
    ElseIf Len(s) = 10 Then
        ' Format must be
        ' mm/dd/yyyy
        
        ' Assert that the first slash
        ' is character 3
        If slash1 <> 3 Then
            DateValidation = False
            Exit Function
        End If
        
        ' Assert that the second slash
        ' is character 6
        If slash2 <> 6 Then
            DateValidation = False
            Exit Function
        End If
        
        m = Left(s, 2)
        d = Mid(s, 4, 2)
    Else
        DateValidation = False
        Exit Function
    End If
    
    ' Assert that the month and day values
    ' are legitimate
    If Val(m) < 1 Or Val(m) > 12 _
        Or Val(d) < 1 Or Val(d) > 31 Then
        DateValidation = False
    Else
        DateValidation = True
    End If
    
End Function

 

[Guest_imported]

Beautiful. This is perfect. Thank you so much. And I don't believe this is a posting record. I am sure someone has more. Thanks again.