Require Data Entry in controls if other control is not null

[SpeedThink]

Assistance needed,

Have several text box controls for data entry on my form; Amount Submit, Date Submit, Amount Received, Date Received, and Followup Date. If Amount Submit is populated, then Date Submit should not be empty and Followup Date should not be empty. If Amount Received is populated, then Date Received should not be empty.

However, the following code results in Error 91 - Object variable or with block variable not set. After researching for the past hour, I do not have a clue as to what the problem is.

Any suggestions?

Public Function RequiredData(ByVal TheForm As Form) As Boolean

'Check that select text box controls have required data entered

Dim Ctl As Control
Dim Num As Integer

On Error GoTo Err_RequiredData

RequiredData = False

'For Each Required control in the Form
Num = 0

If Ctl.Name = AmtSubmitted1 <> "" Then
If DateSubmitted1 = "" Then
Num = 1
End If
End If
'Next Ctl
If Ctl.Name = AmtSubmitted2 <> "" Then
If DateSubmitted2 = "" Then
Num = 1
End If
End If

If Ctl.Name = AmtReceived1 <> "" Then
If DateReceived1 = "" Then
Num = 1
End If
End If

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

 

[Moniker]

Just use the form's BeforeUpdate event and perform validation there.

YourFormName_BeforeUpdate (Cancel As Integer)

    If Not(IsNull(Me.AmountSubmit)) Then
        If Not(IsDate(Me.DateSubmit) Or (Me.FollowupDate)) Then
            MsgBox "A submit date and followup date are required."
            Cancel = True
        End If
    End If

    If Not(IsNull(Me.AmountReceived)) Then
        If Not(IsDate(Me.DateReceived)) Then
            MsgBox "A received date is required."
            Cancel = True
        End If
    End If

End Sub

By using the form's BeforeUpdate, nothing will be written to the recordset without the validation. In other words, if the user tries to switch the record (add a new one, move to a different one, etc.), it is not allowed without being validated.

 

[SpeedThink]

Data Entry Validation

After modifying the code, as displayed below, the message box displays the expected error message. But, I receive a run-time error. Specifically, the run-time error is "Run-time error '2001': You canceled the previous operation."

If I enter a value in the "Date Submitted" field, I receive another error message box "Update or Cancel Update without AddNew or Edit."

Any insight as to a resolution to this issue?

Thanks in advance.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (IsNull(Me.txtAmtSubmitted1)) Then
If (IsNull(Me.txtDateSubmitted1) Or (Me.txtFollowupDate1)) Then
MsgBox "Date Submitted and Follow Up Date fields should be populated."
Cancel = True
End If
End If
If Not (IsNull(Me.txtAmtSubmitted2)) Then
If (IsNull(Me.txtDateSubmitted2) Or (Me.txtFollowupDate2)) Then
MsgBox "Date Submitted and Follow Up Date fields should be populated."
Cancel = True
End If
End If
If Not (IsNull(Me.txtAmtSubmitted3)) Then
If (IsNull(Me.txtDateSubmitted3) Or (Me.txtFollowupDate3)) Then
MsgBox "Date Submitted and Follow Up Date fields should be populated."
Cancel = True
End If
End If
If Not (IsNull(Me.txtAmtSubmitted4)) Then
If (IsNull(Me.txtDateSubmitted4) Or (Me.txtFollowupDate4)) Then
MsgBox "Date Submitted and Follow Up Date fields should be populated."
Cancel = True
End If
End If
If Not (IsNull(Me.txtAmtReceived1)) Then
If (IsNull(Me.txtDateReceived1)) Then
MsgBox "Date Received field should be populated."
Cancel = True
End If
End If
If Not (IsNull(Me.txtAmtReceived2)) Then
If (IsNull(Me.txtDateReceived2)) Then
MsgBox "Date Received field should be populated."
Cancel = True
End If
End If
If Not (IsNull(Me.txtAmtReceived3)) Then
If (IsNull(Me.txtDateReceived3)) Then
MsgBox "Date Received field should be populated."
Cancel = True
End If
End If
If Not (IsNull(Me.txtAmtReceived4)) Then
If (IsNull(Me.txtDateReceived4)) Then
MsgBox "Date Received field should be populated."
Cancel = True
End If
End If
End Sub