Search code examples
vbams-accessms-access-2016

Check if field is null or blank before closing form on Access using VBA


I've been playing around this with VBA code to check if the field is empty before closing the form. If the field is empty, I want it to display a Yes/No message box. The issue that I'm having is that when I click No after the message appears (if the field is blank), it continues to close the form. Instead, when I click No, I want the focus to set on the field that needs to be addressed.

Here's my code:

Private Sub Close_Click()

If IsNull(cboTxtBx) Then
        MsgBox("Do you want to continue?", vbYesNo, "X is Blank!")
        If vbYes Then
            DoCmd.Close
        Else
            cboTxtBx.SetFocus
        End If
Else
    DoCmd.Close
End If

End Sub

I've also replaced IsNull() with Len(cboTxtBx.value & "") = 0 and still have the same issue.
I've also removed the last DoCmd.Close statement but the form still closes once I hit No.

I'm sure the solution lies within the sequence of my code but I'm not proficient with VBA and I'm struggling to get this code to work.

One small note, cboTxtBx is a combo box. Not sure if that interrupts the code in any way.


Solution

  • After you answer the message box its result is interpreted nowhere, so the code goes on.

    You would have to use it like this to really check the result of the message box:

    If MsgBox("Do you want to continue?", vbYesNo, "X is Blank!") = vbYes Then
        DoCmd.Close
    Else
        cboTxtBx.SetFocus
    End If