Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

User entry validation problem. 2

Status
Not open for further replies.
sparkbyte

sparkbyte

Technical User
Sep 20, 2002
879
US
I keep getting

run-time eooro '2108'
You must save the field before you execute the gotocontrol action, the gotocontrol method, or the setfocus method.

How do I fulfill this requirement and still force the user to re-enter the data??

Code: 
Select Case Len(Me.FileNumber)

    Case Is < 7
        Dim strResult As String
        strResult = MsgBox("Not a Valid File Number", 0 + 48 + 65536, "ERROR")
        
        Select Case strResult
            Case vbOK, vbRetry, vbYes, vbNo
                MsgBox "Please Correct the File Number", 0 + 48 + 65536, "ERROR"
                DoCmd.Save acDefault
                DoCmd.GoToRecord , "frmTrackingTable", acNewRec
                Me.FileNumber.SetFocus
                Exit Sub
            Case vbCancel, vbAbort, vbIgnore
                Exit Sub
            Case Else
                Exit Sub
        End Select
    
    Case 8 To 13
        
        If left(Me.FileNumber, 3) Like "[a-zA-Z][a-zA-Z][a-zA-Z]" Then
            strResult = MsgBox("Not a Valid File Number", 0 + 48 + 65536, "ERROR")
            Select Case strResult
                Case vbOK, vbRetry, vbYes, vbNo
                    MsgBox "Please Correct the File Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.SetFocus
                    Exit Sub
                Case vbCancel, vbAbort, vbIgnore
                    Exit Sub
                Case Else
                    Exit Sub
            End Select
        ElseIf left(Me.FileNumber, 1) Like "[a-zA-Z]" Then
            strResult = MsgBox("Not a Valid Reciept Number", 0 + 48 + 65536, "ERROR")
            Select Case strResult
                Case vbOK, vbRetry, vbYes, vbNo
                    MsgBox "Please Correct the Reciept Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.SetFocus
                    Exit Sub
                Case vbCancel, vbAbort, vbIgnore
                    Exit Sub
                Case Else
                    Exit Sub
            End Select
        End If
        
    Case Is > 13
        
        strResult = MsgBox("Not a Valid File Number", 0 + 48 + 65536, "ERROR")
            Select Case strResult
                Case vbOK, vbRetry, vbYes, vbNo
                    MsgBox "Please Correct the File Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.DefaultValue = ""
                    DoCmd.Save acDefault,
                    DoCmd.GoToRecord , , acPrevious
                    Me.FileNumber.SetFocus
                    Exit Sub
                Case vbCancel, vbAbort, vbIgnore
                    Exit Sub
                Case Else
                    Exit Sub
            End Select
    Case Else
        'Good File Number
End Select


Thanks

John Fuhrman
 
Sort by date Sort by votes
How are ya sparkbyte . . .

Validation is typically performed in the forms [blue]Before Update[/blue] event, where you can use the [blue]Cancel[/blue] arguement. Your code should be moved to this event.

Also ... you have quite a bit of over-kill in your code. Here's a trimmed example for [blue]Case Is < 7[/blue]:
Code: 
[blue]Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim dl As String
   
   dl = vbNewLine & vbNewLine
   
   Select Case Len(Me.FileNumber)
      Case Is < 7
         MsgBox "Not a Valid File Number" & dl & _
                "Please Correct the File Number ...", _
                vbExclamation + vbOKOnly, _
                "ERROR!"
         [purple][b]Cancel = True[/b][/purple]
         Me.FileNumber.SetFocus
         Exit Sub
      Case 8 To 13
         [green]'[/green]
      Case Is > 13
         [green]'[/green]
   End Select

End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
Still getting the same error.

Here is the complete code.

The Highlighted code is where the error is happening.

I am forcing the error by entering

1Z4A935R0356128508

in both the trackingnumber and filenumber fields.

It is a BOGUS UPS tracking Number.

Code: 
Private Sub FileNumber_BeforeUpdate(Cancel As Integer)

Dim TrackingDate As Date

Me.TrackingDate = Now
    
If UCase(Me.FileNumber) = UCase(".box.end.") Then
    DoCmd.GoToRecord , , acNewRec
    Me.BoxNumber.DefaultValue = ""
    Me.FileNumber.DefaultValue = ""
    Me.FileNumber.Value = ""
    Me.BoxNumber.Value = ""
    Me.TrackingDate.Value = ""
    Me.BoxNumber.SetFocus
    Exit Sub
Else
    Me.BoxNumber.DefaultValue = """" & Me.BoxNumber & """"
End If

Dim dl As String
dl = vbNewLine & vbNewLine
Dim strResult As String

Select Case Len(Me.FileNumber)
    Case Is < 7
         Cancel = True
         MsgBox "Not a Valid File Number" & dl & _
                "Please Correct the File Number ...", _
                vbExclamation + vbOKOnly, _
                "ERROR!"
         Me.FileNumber.SetFocus
         Exit Sub
      
    Case 8 To 13
        Cancel = True
           If left(Me.FileNumber, 3) Like "[a-zA-Z][a-zA-Z][a-zA-Z]" Then
            strResult = MsgBox("Not a Valid File Number" & dl & _
                           "Please Correct the File Number ...", _
                           vbExclamation + vbOKOnly, _
                           "ERROR!")
            Select Case strResult
                Case vbOK, vbRetry, vbYes, vbNo
                    Me.FileNumber.SetFocus
                    Exit Sub
                Case vbCancel, vbAbort, vbIgnore
                    Exit Sub
                Case Else
                    Exit Sub
            End Select
        
        ElseIf left(Me.FileNumber, 1) Like "[a-zA-Z]" Then
            strResult = MsgBox("Not a Valid File Number" & dl & _
                           "Please Correct the File Number ...", _
                           vbExclamation + vbOKOnly, _
                           "ERROR!")
            Select Case strResult
                Case vbOK, vbRetry, vbYes, vbNo
                    Me.FileNumber.SetFocus
                    Exit Sub
                Case vbCancel, vbAbort, vbIgnore
                    Exit Sub
                Case Else
                    Exit Sub
            End Select
        End If

    Case Is > 13
        Cancel = True
            MsgBox "Not a Valid File Number" & dl & _
                   "Please Correct the File Number ...", _
                   vbExclamation + vbOKOnly, _
                   "ERROR!"
[highlight]            Me.FileNumber.SetFocus[/highlight] 
            Exit Sub
End Select

endit:
Exit Sub

err_Handler:
    Select Case Err
        Case 2501
            'Do Nothing
        Case Else
            MsgBox _
            "An unexpected error has been detected" & Chr(13) & _
            "Error Number: " & Err.Number & " , " & Err.Description & Chr(13) & _
            vbCrLf & "Please note the above details before contacting support"
    End Select
Resume endit
End Sub

I also have a bit of code to error out on entries where the trackingnumber and filenumber equal each other. But, one problem at a time.:)


Thanks

John Fuhrman
 
Upvote 0 Downvote
As you set Cancel to True you don't need to use the SetFocus method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thanks PH, That did it.

Now for some reason after correcting that peice another is broke that was working before.

Run-time error '2115': said:
The macro or function set to the BeforeUpdate or
ValidationRule property for this field is preventing
Mailroom Tracking from saving the data in the field.
Click to expand...
Code: 
If Len(Me.BoxNumber) < 13 Then
    If UCase(Me.BoxNumber.Value) = left(UCase(Me.BoxNumber.Value), 3) Then
        [red]Me.BoxNumber.Value = UCase("nbc|" & left(fOSUserName, 3) & "|" & Date & "|" & Time)[/red]
        Exit Sub
    Else
    Cancel = True
    MsgBox "Not a Valid Tracking Number" & dl & _
           "Please Correct the Tracking Number ...", _
           vbExclamation + vbOKOnly, _
           "ERROR!"
    End If
    Exit Sub
End If


Thanks

John Fuhrman
 
Upvote 0 Downvote
I have tried this also..

Code: 
Private Sub BoxNumber_BeforeUpdate(Cancel As Integer)
On Error GoTo err_Handler

Dim dl As String
dl = vbNewLine & vbNewLine
Dim strResult As String
[red]
    If left(UCase(Me.BoxNumber.Value), 3) = "NBC" Then
        Cancel = True
        Me.BoxNumber.Value = UCase("nbc|" & left(fOSUserName, 3) & "|" & Date & "|" & Time)
        Exit Sub
    Else
        'Do Nothing
    End If
[/red]    
If Len(Me.BoxNumber) < 13 Then
    Cancel = True
    MsgBox "Not a Valid Tracking Number" & dl & _
           "Please Correct the Tracking Number ...", _
           vbExclamation + vbOKOnly, _
           "ERROR!"
    Exit Sub
End If

endit:
Exit Sub

err_Handler:
    Select Case Err
        Case 2501
            'Do Nothing
        Case Else
            MsgBox _
            "An unexpected error has been detected" & Chr(13) & _
            "Error Number: " & Err.Number & " , " & Err.Description & Chr(13) & _
            vbCrLf & "Please note the above details before contacting support"
    End Select
Resume endit

End Sub


Thanks

John Fuhrman
 
Upvote 0 Downvote
OK, guys this one is closed.

Found the right combination.

here is the final solution.

Comments welcomed!!!!!!

Code: 
Private Sub BoxNumber_AfterUpdate()
    If left(UCase(Me.BoxNumber.Value), 3) = "NBC" Then
        Me.BoxNumber.Value = UCase("nbc|" & left(fOSUserName, 3) & "|" & Date & "|" & Time)
        Exit Sub
    Else
        'Do Nothing
    End If

End Sub

Code: 
Private Sub BoxNumber_BeforeUpdate(Cancel As Integer)
On Error GoTo err_Handler

Dim dl As String
dl = vbNewLine & vbNewLine
Dim strResult As String

    
If Len(Me.BoxNumber) < 13 Then
    If left(UCase(Me.BoxNumber.Value), 3) = "NBC" Then
        Exit Sub
    Else
        'Do Nothing
    End If
    
    Cancel = True
    MsgBox "Not a Valid Tracking Number" & dl & _
           "Please Correct the Tracking Number ...", _
           vbExclamation + vbOKOnly, _
           "ERROR!"
    Exit Sub
End If

endit:
Exit Sub

err_Handler:
    Select Case Err
        Case 2501
            'Do Nothing
        Case Else
            MsgBox _
            "An unexpected error has been detected" & Chr(13) & _
            "Error Number: " & Err.Number & " , " & Err.Description & Chr(13) & _
            vbCrLf & "Please note the above details before contacting support"
    End Select
Resume endit

End Sub

Code: 
Dim TrackingDate As Date
Dim dl As String
dl = vbNewLine & vbNewLine
Dim strResult As String

Me.TrackingDate = Now

Select Case Len(Me.FileNumber)
    Case Is < 7
         Cancel = True
         MsgBox Me.FileNumber & " is not a Valid File Number" & dl & _
                "Please Correct the File Number ...", _
                vbExclamation + vbOKOnly, _
                "ERROR!"
         Exit Sub
    Case 8 To 13
            If IsAlpha(left(Me.FileNumber, 3)) Then
            ' Do Nothing
            ElseIf IsAlpha(left(Me.FileNumber, 1)) Then
            'Do Nothing
            Else
                Cancel = True
                strResult = MsgBox(Me.FileNumber & " is not a Valid File Number" & dl & _
                           "Please Correct the File Number ...", _
                           vbExclamation + vbOKOnly, _
                           "ERROR!")
                    Select Case strResult
                        Case vbOK, vbRetry, vbYes, vbNo
                            Exit Sub
                        Case vbCancel, vbAbort, vbIgnore
                            Exit Sub
                        Case Else
                            Exit Sub
                    End Select
            End If
    Case Is > 13
        Cancel = True
            MsgBox Me.FileNumber & " is not a Valid File Number" & dl & _
                   "Please Correct the File Number ...", _
                   vbExclamation + vbOKOnly, _
                   "ERROR!"
            Exit Sub
    End Select

If UCase(Me.FileNumber) = UCase(".box.end.") Then
    DoCmd.GoToRecord , , acNewRec
    Me.BoxNumber.DefaultValue = ""
    Me.FileNumber.DefaultValue = ""
    Me.FileNumber.Value = ""
    Me.BoxNumber.Value = ""
    Me.TrackingDate.Value = ""
    Me.BoxNumber.SetFocus
    Exit Sub
Else
    Me.BoxNumber.DefaultValue = """" & Me.BoxNumber & """"
End If

endit:
Exit Sub

err_Handler:
    Select Case Err
        Case 2501
            'Do Nothing
        Case Else
            MsgBox _
            "An unexpected error has been detected" & Chr(13) & _
            "Error Number: " & Err.Number & " , " & Err.Description & Chr(13) & _
            vbCrLf & "Please note the above details before contacting support"
    End Select
Resume endit
End Sub

Thanks

John Fuhrman
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Concatenation problem 3
Replies
5
Views
874
Moss100
Moss100
Moss100
  • Locked
  • Question
Filter vba problem
Replies
1
Views
500
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Access send email attachments - problem when null or empty
Replies
7
Views
2K
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Stop user changing size of Access Application (not form)
Replies
1
Views
502
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Adodo problem
Replies
7
Views
260
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top