Check for mandatory fields before adding new record

[ittechguy]

I have a simple form which displays all the customers in tblCustomer and allows users to enter new customers. The form looks like this:





The VBA code is below:

Private Sub cmdSubmit_Click()    Dim db As Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblCustomer")
    rs.AddNew
    rs("OrganizationFK").Value = Me.cboOrganization
    rs("ShopNameFK").Value = Me.cboShopName
    rs("OfficeSymFK").Value = Me.cboOfficeSym
    rs("RankFK").Value = Me.cboRank
    rs("LastName").Value = Me.txtLastName
    rs("FirstName").Value = Me.txtFirstName
    rs("PhoneNum").Value = Me.txtPhoneNum
    rs("Email").Value = Me.txtEmail
    rs.Update
    Me.frmAddCust.Requery
End Sub

I'm trying to make it so that it will not add a new record if all mandatory fields have not been filled in. I'm most confused by how some fields can be null. For an example, if it is a Shop then Mandatory fields would be OrganizationFK, ShopNameFK, OfficeSymFK, PhoneNum, and Email. This is because the record is not for a specific person, but an entire shop.

If it is an actual person, then all fields except for OfficeSymFK must be filled in. OfficeSymFK being an optional field because not all shops have an Office Symbol.

That said, I think the if statements would start at LastName and FirstName. Perhaps also RankFK. Those are the only fields which can be null. Basically, if any one of those fields are not null then it must check to make sure that all other fields except OfficeSymFK is also not null.

Whats the best way to do this?

[ittechguy]

I figured it out! I had to create a boolean variable.

Option Compare Database

Private Sub Form_Current()
Me.Form.Requery
End Sub
Private Sub cboOrganization_AfterUpdate()
    Me.cboShopName.Requery
    Me.cboOfficeSym.Requery
End Sub


Private Sub cmdSubmit_Click()
     Dim db As Database
     Dim rs As DAO.Recordset
     Dim isPerson As Boolean
     isPerson = False
     isPerson = isPerson Or Not Me.cboRank.Value = "" And Not IsNull(Me.cboRank.Value)
     isPerson = isPerson Or Not Me.txtLastName.Value = "" And Not IsNull(Me.txtLastName.Value)
     isPerson = isPerson Or Not Me.txtFirstName.Value = "" And Not IsNull(Me.txtFirstName.Value)
If isPerson Then
    If Not IsNull(Me.txtPhoneNum.Value) _
    And Not IsNull(Me.txtEmail.Value) _
    And Not IsNull(Me.cboOrganization.Value) _
    And Not IsNull(Me.cboShopName.Value) Then
    Call AddEntry
Else
    MsgBox "Respectfully, **** You." & vbCrLf & vbCrLf
End If
Else
If Not IsNull(Me.txtPhoneNum.Value) _
And Not IsNull(Me.txtEmail.Value) _
And Not IsNull(Me.cboOrganization.Value) _
And Not IsNull(Me.cboShopName.Value) Then
Call AddEntry
Else
MsgBox "Respectfully, **** You." & vbCrLf & vbCrLf
End If
End If


    
End Sub
 
'Separate sub for adding the actual entry
Private Sub AddEntry()
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblCustomer")
        If DCount("*", "tblCustomer", "[Email]='" & Me.txtEmail & "'") > 0 Then
MsgBox "**** You." & vbCrLf & vbCrLf
Else
    rs.AddNew
    rs("OrganizationFK").Value = Me.cboOrganization
    rs("ShopNameFK").Value = Me.cboShopName
    rs("OfficeSymFK").Value = Me.cboOfficeSym
    rs("RankFK").Value = Me.cboRank
    rs("LastName").Value = Me.txtLastName
    rs("FirstName").Value = Me.txtFirstName
    rs("PhoneNum").Value = Me.txtPhoneNum
    rs("Email").Value = Me.txtEmail
    rs.Update
    Me.frmAddCust.Requery
End If
End Sub