Error message pointless

[constantG]

I'm having problems with validating data on a form. I created a command button that sets focus on the next page of a tab control. The validation code is within a module and is called for in the Form's before update event.

The problem is that the correct error message appears from an "On Error" code but when I "OK" the message, the focus is set to the next page regardless.

I'm getting tired now and won't be back until tomorrow. Thanks in advance.

cG

 

[boblarson]

When you do come back, please post the code that you are calling AND the rest of the Before Update event code. We can't help if we can't see what you have. (most of us, at least me, are not mind readers)

 

[constantG]

Sorry Bob,

I thought this might have been a common problem and would be sorted without the need for code. And I was v tired at the time.

Here's the code for the module:

Option Compare Database
Public Function RequiredData(ByVal TheForm As Form) As Boolean
'Check that all TextBox controls have required data entered
    Dim Ctl As Control
    Dim Num As Integer
 
    On Error GoTo Err_RequiredData
 
    RequiredData = False
    Num = 0
    For Each Ctl In TheForm
        If Ctl.ControlType = acTextBox Then
 
            If Ctl = "" Or IsNull(Ctl) Then
                Num = 1
                Exit For
            End If
        End If
    Next Ctl
    If Num = 1 Then
        MsgBox "Data is required in " & Ctl.Name & "," & vbCr & _
        "please ensure this is entered.", _
        vbInformation, "Required Data..."
        RequiredData = True
    Else
        RequiredData = False
    End If
Exit_RequiredData:
    On Error Resume Next
        If Not (Ctl Is Nothing) Then
            Set Ctl = Nothing
        End If
    Exit Function
Err_RequiredData:
    Select Case Err
        Case 0
            Resume Next
        Case Else
            MsgBox "Error: " & Err.Number & vbCrLf & vbCrLf & Err.Description, _
            vbInformation
    End Select
 
End Function

And here's the code in the forms before update event, which calls it:

If RequiredData(Me) Then Cancel = -1

and the click event for the command button is:

Private Sub Command22_Click()
On Error GoTo Err_Command22_Click
DoCmd.RunCommand acCmdSaveRecord
 
If Me.TabCtl0 < Me.TabCtl0.Pages.Count - 1 Then
Me.TabCtl0 = Me.TabCtl0 + 1
Else
Me.TabCtl0 = 0
 
Exit_Command22_Click:
    Exit Sub
 
Err_Command22_Click:
    MsgBox Err.Description
    Resume Exit_Command22_Click
 
End If
 
End Sub

Hope this helps,

Thanks

 

[boblarson]

You should be able to use

Cancel = RequiredData(Me)

to cancel the update. However, you are only canceling the update for the form so it can still move to the other tab. You would want to have some sort of flag come back in your click event to cancel the move if the update has been canceled.