asked on

MS Access VBA Code for fields that are null and prevent the code from continuing

I have a command button on my form that will trigger an email and also put a date/time stamp in a field on my form. The problem I am trying to work through is that there are 2 fields on the form that need to have a value in them before the email and date/time stamp can be triggered. How can I write the code to check these 2 fields and IF they are null, or one of them is null, have a pop up notify the user and then have the cursor be put into the first null field so the user has to enter a value. And then also have the code stop at that point so the email isn't sent. That will force the user to enter a value and then hit the command button again. The problem I am having is IF one of the 2 fields is null then how do I have the cursor setfocus to the field that is null? I don't know if the code is correct so far either. I just need to figure out IF only ONE field is null how to set focus to that field. But IF both fields are null I want it to set the focus to the first field. But either way I need the code to stop there and exit the command buttons code. There is more code than what I posted but that will only fire if it passes this IF statement. I need it to exit the code if it cannot make it past this IF statement.

    If Me.cboTissueType = "TissueA" And (IsNull(Me.txtFieldA) Or IsNull(Me.txtFieldB)) Then
        MsgBox "You need to enter a value in....."
        'Code here to set focus to the first field if they are both null OR the one field if only ONE is null. Just need to figure out how to find out  which field is null so it knows which field to set the focus on.
    End If

Open in new window

SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lawrence Salvucci

ASKER

So if I added my top part of the IF statement it would look like this, correct?

    If Me.cboTissueReceived = "Skin Biopsy" Or Me.cboTissueReceived = "Human Skin Biopsy" Then
        With Me.txtTissueMeasurement
            If IsNull(.Value) Then
                .SetFocus
                MsgBox "BlahBlah1 is not filled out, cannot continue", , "Missing information"
                Exit Sub
            End If
        End With
        With Me.txtGrossingTechnician
            If IsNull(.Value) Then
                .SetFocus
                MsgBox "BlahBlah2 is not filled out, cannot continue", , "Missing information"
                Exit Sub
            End If
        End With
    End If

Open in new window

crystal (strive4peace) - Microsoft MVP, Access

no, test for controls filled FIRST ... and replace "BlahBlah" with what you want to say ;)

Lawrence Salvucci

ASKER

ok but I only want to test those control IF the cboTissueReceived has one of those 2 values in my IF statement. If it doesn't then it doesn't need to test those controls. Wouldn't I set it up like that?

crystal (strive4peace) - Microsoft MVP, Access

aah, didn't notice the first IF was testing something different. What if Me.cboTissueReceived is Null?

Lawrence Salvucci

ASKER

That's a good question. It shouldn't be null but only those values should then trigger the other 2 controls to see if they are null. I can make that control a required field.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial