Problem when detecting form user errors before saving form: Run-time error 2105

[wardw]

I have a form where, before the form data is saved, (a) certain fields must be filled in if other fields are filled in and (b) one specified date must precede another specified date.



I want to ensure that the form can't be saved with those errors present. So I've used the form's Before Update event--Private Sub Form_BeforeUpdate(Cancel As Integer)--I've coded detectors and error messages for those requirements. I've also specified Cancel = True after each of the error detector/message parts of the Sub's code.

Here's a sample of that Sub:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   
    'If Gender or LastName field is blank, display error message.
 If LenB([Gender] & "") = 0 Or LenB([LastName] & "") = 0 Then
        MsgBox "On the Identity tab, please specify both Gender and Last Name."
    'Put the cursor on the appropriate tab and field
        If LenB([Gender] & "") = 0 Then
            Gender.SetFocus
        ElseIf LenB([Gender] & "") > 0 Then
            LastName.SetFocus
        End If
    'Cancel the update
    Cancel = True

    'If FirstPublicationDate is earlier than DeathDate, display error message.
    ElseIf Me.FirstPublicationDate < Me.DeathDate Then
        MsgBox "Date of Death (on Death Age/Date tab) must be earlier" & vbCrLf & _
               "or the same as First Obituary Date (on Obituary tab)."
    'Put the cursor on the appropriate tab and field
        DeathDate.SetFocus
    'Cancel the update
    Cancel = True

  '[snip]

 End If
End Sub

I've also built several command buttons for navigation (Next Record, Previous Record, First Record, Last Record), because I don't want to assume form users will be familiar with the Access navigation at the bottom of the frame. So for each of those buttons, I want the same error detecting/messages and "save prevention" to occur. Here's my code for one of my nav buttons:

Private Sub btnGotoFirstRecord_Click()
      DoCmd.GoToRecord , , acFirst
End Sub

The problem is that if one of my nav buttons is clicked with errors on the form, my error message is displayed all right, but when OK is clicked, a system error message appears:

Run-time error '2105'

You can't go to the specified record.

...and the program stops. If I instead use the standard Access nav arrows, that message doesn't appear and all is well. Is there a way to prevent the system message from appearing?

[Dal Jeanis]

Generally, if I wanted the same edits in a lot of places, I'd put it in a subroutine and call it each place I needed it.

In this case, however, it looks like the form BeforeUpdate event is firing in the right place, but you haven't given your nav buttons a way to detect the fail. And, you aren't causing the update to be attempted before the navigation is attempted.

Consider creating a form-level global variable "gboolUpdateFailed", and setting it False in the Onload and at the beginning of BeforeUpdate, then set it True if Cancel gets set.

In each of your other navigation events, use this before causing any navigation:

If Me.Dirty then 
  Me.Dirty = False
end if

After that, test gboolUpdateFailed before proceeding.

[Missinglinq]

There's no reason to 'tell' your Nav buttons that there is a problem! When you attempt to move to another Record, Access acts to save the Current Record, the Form_BeforeUpdate event fires, and the Validation code in that event will execute.

But what you do need to do, any time that you have more than one Validation occurring, is to follow

Cancel = True

with

Exit Sub

Otherwise, the code will 'drop through' to the next Validation, not giving the user the opportunity to correct the current problem.

Linq ;0)>

[Dal Jeanis]

See, that's why I hang out here. Being self-taught means I'm occasionally completely missing something very basic in "how things are usually done".

[ssanfu]

I'm just curious...

If LenB([Gender] & "") = 0 Then

Why are you using the functionLenB(), instead of Len()?

I have never seen LenB() used. Am I missing something?

[Dal Jeanis]

It's roughly the difference between length in characters (Len) and length in bytes (LenB). Classic plain text ASCII was 1 byte per character, later ASCII 2 bytes, and the international Unicode varies up to (IIRC) 4 bytes for the least used character sets (like Urdu variants). It only matters when you are doing explicit memory allocation - you can use LenB to get the length of storage needed/used by a custom class, for example.

http://www.bigresource.com/VB-LenB-F...Q8cXccyHK.html

http://www.xtremevbtalk.com/showthread.php?t=78494

[ssanfu]

@ Dal
I Googled both of them and know the difference.... I was curious as to why the OP picked LenB instead of the more commonly used Len function.

I just have never seen anyone use LenB. So I was checking to see if there was anything I should be aware of.... didn't appear to be anything special in the code. Always curious..

[Dal Jeanis]

One of those posts held a link to a post that said LenB is slightly faster, since it isn't dividing the variable length byte by two to determine the number of characters. That's the only rational reason I could find to choose it, and I wouldn't do so in that place for that reason alone.

[wardw]

I certainly can't take credit for using LenB instead of Len because I was aware of any advantage for my app; I just read someplace that it had some small advantage, and I figured it couldn't hurt.

[wardw]

Thanks, Dal, and Missinglinq, for your insights. As Dal suggests, I've abandoned the BeforeUpdate event in favor of calling subroutines for each button. Somewhere in the process of creating those, the "You can't go to the specified record" error went away.

[Dal Jeanis]

Well, my suggestion was based on not knowing the easier way, but if it works, and you understand it, then it's all good.