Search code examples
vbams-accessms-access-forms

Issue with checking for blank input boxes and sending appropriate message in VBA


I'm having an issue with my code in VBA properly checking for blank fields and giving the appropriate message in that event and I can't figure out what I've done wrong. My issue is that only the client name blank field checker actually works, the others do nothing at all. Here is a sample of my code.

'Error Handling
If (Me!ClientName) = "" Then
    MsgBox "Client Name cannot be blank"
    End

Else
    If IsNumeric(ClientName.Value) = True Then
        MsgBox "Client Name must be letters only."
        End
        Exit Sub
    End If
End If

If (Me!ClientDoB) = "" Then
    MsgBox "Client Date of Birth cannot be blank"
    End
End If

If (Me!ClientAdd) = "" Then
    MsgBox "Client Address cannot be blank"
    End
End If

If (Me!ClientCity) = "" Then
    MsgBox "Client City cannot be blank"
    End
Else
    If IsNumeric(ClientCity.Value) = True Then
        MsgBox "Client City must be letters only."
        End
        Exit Sub
    End If
End If

If (Me!ClientSt) = "" Then
    MsgBox "Client State cannot be blank"
    End
End If

If (Me!ClientZC) = "" Then
    MsgBox "Client Zip Code cannot be blank"
    End
Else
    If IsNumeric(ClientZC.Value) = False Then
        MsgBox "Client Zip Code must be numbers only."
        End
        Exit Sub
    End If
End If

Solution

  • Consider using an if-elseif structure, such as:

    If Me.clientname & "" = "" Then
        MsgBox "Client Name cannot be blank"
    ElseIf Me.clientname Like "*[!A-Za-z]*" Then
        MsgBox "Client Name must contain letters only."
    ElseIf Me.clientdob & "" = "" Then
        MsgBox "Client Date of Birth cannot be blank"
    ElseIf Me.clientadd & "" = "" Then
        MsgBox "Client Address cannot be blank"
    ElseIf Me.clientcity & "" = "" Then
        MsgBox "Client City cannot be blank"
    ElseIf Me.clientcity Like "*[!A-Za-z]*" Then
        MsgBox "Client City must contain letters only."
    ElseIf Me.clientst & "" = "" Then
        MsgBox "Client State cannot be blank"
    ElseIf Me.clientzc & "" = "" Then
        MsgBox "Client Zip Code cannot be blank"
    ElseIf Me.clientzc Like "*[!0-9]*" Then
        MsgBox "Client Zip Code must be numbers only."
    End If