Getting Error When Submitting Records on Form

[bconner]

I am testing a Data Entry Form I created to make sure it throws a message box when all required fields aren't populated. So I left all fields blank and when I click submit it throws a runtime error '2465' Microsoft Access can't find the field '|' referred to in your expression.

Below is the code:

Private Sub Command21_Click()
Dim Rs As Recordset
 
If (IsNull(Me.[Request Batch].Value) = False And IsNull(Me.[Provider].Value) = False _
And IsNull(Me.[Requestor].Value) = False And IsNull(Me.[Patient or Client Name].Value) = False _
And IsNull(Me.[Lab Site Name].Value) = False And IsNull(Me.[Lab Site Name].Value) = False And _
IsNull(Me.[Account Number].Value) = False And IsNull(Me.[Refund To].Value) = False _
And IsNull(Me.[Refund Reason].Value) = False And IsNull(Me.[Invoice Number].Value) = False _
And IsNull(Me.[Refund Amount].Value) = False And IsNull(Me.[Date of Service].Value) = False) = True Then
'Updates Refund Letter Table
Set Rs = CurrentDb.OpenRecordset("Main Table", dbOpenDynaset)
Rs.AddNew
Rs![Request Date] = Me.[Request Date]
Rs![Request Batch] = Me.[Request Batch]
Rs![Account Number] = Me.[Account Number]
Rs![Invoice Number] = Me.[Invoice Number]
Rs![Refund To] = Me.[Refund To]
Rs![Refund Amount] = Me.[Refund Amount]
Rs![Group Number] = Me.[Group Number]
Rs![Date of Service] = Me.[Date of Service]
Rs![Provider] = Me.[Provider]
Rs![Requestor] = Me.[Requestor]
Rs![Patient or Client Name] = Me.[Patient or Client Name]
Rs![Lab Site Name] = Me.[Lab Site Name]
Rs![Invoice Balance] = Me.[Invoice Balance]
Rs![Other Balances] = Me.[Other Balances]
Rs![Refund Reason] = Me.[Refund Reason]
Rs![Address on File?] = Me.[Address on File?]
Rs![Other Address] = Me.[Other Address]
Rs![Comments] = Me.Comments
Rs.Update
Rs.Close
Set Rs = Nothing
 
Else
MsgBox ("Please Review and Complete all Required Fields")
End If

the debugger is highlighting the If statement but I can't find anything wrong with it.

 

[ghudson]

There are other ways to ensure your required fields have data. I prefer to use the popular method of keying "required" in the Tag property of each field that I want set as a required field [the Tag property is on the Other tab]. Then I use the below function in the form. I then call the function whenever I need to test that all required fields are completed like in the Form_BeforeUpdate event.

Private Function VerifyRequiredFieldsAreCompleted()
On Error GoTo Err_VerifyRequiredFieldsAreCompleted

    Dim ctl As Control
    
    For Each ctl In Me.Controls
        If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
            If ctl.Tag = "Required" And IsNull(ctl) Or ctl.Tag = "Required" And ctl = "" Then
                ctl.SetFocus
                MsgBox "A required field is null.  You must complete the ''" & ctl.ControlTipText & "'' field.", vbCritical, "Missing Required Data"
                Exit For
            Else
            End If
        End If
    Next

Exit_VerifyRequiredFieldsAreCompleted:
    Exit Function

Err_VerifyRequiredFieldsAreCompleted:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "VerifyRequiredFieldsAreCompleted()"
    Resume Exit_VerifyRequiredFieldsAreCompleted

End Function

 

[bconner]

Thank you very much!