Requiring Data Entry in Microsoft Access Fields 2

[BvCF]

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

 

[JoeAtWork]

    [COLOR=red]Dim Ctl As Control[/color]
    Dim Num As Integer
    
    On Error GoTo Err_RequiredData
    
    RequiredData = False
    
    'For Each Required control in the Form
    Num = 0
         
    [COLOR=red]If Ctl.Name = AmtSubmitted1 <> "" Then[/color]

You never set the object variable Ctl to anything.

If you did something like this, it would probably work

    Set Ctl = TheForm.Controls("AmtSubmitted1")
    If nz(Ctl,"") <> "" Then

Or just more directly

    If nz(TheForm.Controls("AmtSubmitted1"),"") <> "" Then

TheForm must have a control actually called "AmtSubmitted1", otherwise it will error.

I use the nz function as a precaution incase the control's value is Null.

 

[BvCF]

Made some revisions but this is still not working as planned (see code below). Not as proficient with all of the nuances of vba code just yet.

Any suggestions as to how I should modify so that if AmountSubmitted1 is not null and DateSubmitted1 is null, then show msg box "Please enter..."



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 Nz(frmTabbed.Controls("AmtSubmitted1"), "") <> "" Then
If DateSubmitted1 = "" Then
Num = 1
End If
End If
'Next Ctl
If Nz(frmTabbed.Controls("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

 

[PHV]

A starting point:

Dim strName As String
If Trim(TheForm.Controls("AmtSubmitted1") & "") <> "" Then
  If Not IsDate(TheForm.Controls("DateSubmitted1")) Then
    strName = "DateSubmitted1"
  End If
End If
If Trim(TheForm.Controls("AmtReceived1") & "") <> "" Then
  If Not IsDate(TheForm.Controls("DateReceived1")) Then
    strName = "DateReceived1"
  End If
End If
If strName <> "" Then
  MsgBox "Data is required in " & strName & "," & vbCr & _
   "please ensure this is entered.", _
   vbInformation, "Required Data..."
  RequiredData = True
Else
  RequiredData = False
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BvCF]

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

 

[PHV]

What about this ?
Private Sub Form_BeforeUpdate(Cancel As Integer)
For i = 1 To 4
If Not IsNull(Me("txtAmtSubmitted" & i)) Then
If Not (IsDate(Me("txtDateSubmitted" & i)) And IsDate(Me("txtFollowupDate" & i))) Then
MsgBox "Date Submitted and Follow Up Date fields should be populated."
Me("txtDateSubmitted" & i)).SetFocus
Cancel = True
Exit Sub
End If
End If
If Not IsNull(Me("txtAmtReceived" & i)) Then
If Not IsDate(Me("txtDateReceived" & i)) Then
MsgBox "Date Received field should be populated."
Me("txtDateReceived" & i)).SetFocus
Cancel = True
Exit Sub
End If
End If
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BvCF]

PHV,

The error message box appears as desired if the end-user populates the amt submitted field but doesn't populate the date submitted and followup date fields. However, upon clicking "OK" another message box appears with a "Run Time error '2115'" error (The macro function set to the Before Update or Validation Rule property for the field is preventing the App from saving the data in the field.)

When I click "Debug", "Me.Bookmark = rs.Bookmark" is highlighted in the After Update event of this sub;

Private Sub List2_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[AcctNo] = " & Str(Nz(Me![List2], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

(Note, List2 is the name of the listbox that contains the accounts that are assigned to the particular end user. The end user can just highlight the next account within the listbox to see the associated controls for that account that are for data entry.)

Then, I closed the application by clicking "X" in the upper right window, another message box appears with the following "Update or cancel update without AddNew or Edit."

Any additional insight?

Thanks in advance.