Two if isNull statement

[Mafi]

I have two combox which i want to select before open command2
and I have it right but the problem I encounter is that, if B2 is selected it overide and open cammand2. Here is code below:

Private Sub Command2_Click()
    If IsNull(B1) Then
        MsgBox ("Please Select B1 To Proceed")
    End If
    If IsNull(B2) Then
        MsgBox ("Please Select B2 To Proceed")
    Else
        DoCmd.RunMacro "NEW ASSETS Query2"
    End If
End Sub

[NeoPa]

Mafi:
I have it right but the problem I encounter is that

How can anyone write that. It can't possibly make any sense. If you had it right then there couldn't be a problem - by definition. Obviously you don't have it right.

I assume you want to run the macro if, and only if, both [B1] & [B2] have values set. To do that you need to make the code reflect that :

Private Sub Command2_Click()
    With Me
        If IsNull(.B1) Then
            Call MsgBox("Please Select B1 To Proceed")
            Call .B1.SetFocus
        ElseIf IsNull(.B2) Then
            Call MsgBox("Please Select B2 To Proceed")
            Call .B2.SetFocus
        Else
            Call DoCmd.RunMacro("NEW ASSETS Query2")
        End If
    End With
End Sub

[Mafi]

I tried but pop up Compile error: end with without with

[PhilOfWalton]

Two points.

In the above code, remove all instances of the word "Call".

I would strongly advise against Macros (Except 'AotoExec'). Firstly they are far less powerful than VBA Code, and equally as important, if everything is in code, it can all be seen in one place, and if there is a problem with the code running, it can simply be stepped through, line by line to see where unexpected values appear.

Using Macros, one has to change from the VBA window to the Macro Window.

Phil

[Mafi]

Oh! I miss something from the code suggested by Neopa that is why am getting the Error. Now works perfectly. Thanks

[NeoPa]

Phil:
In the above code, remove all instances of the word "Call".

That wouldn't be a good idea Phil.

If, for reasons that are beyond me, you should ever feel the need to make the calls the less clear way, then you'd need to remove the parentheses from around the parameters too. Not sure why anyone would choose the more clumsy approach over the less, mind.

You may notice that the original code suffered from having the parentheses without the Call.

[twinnyfo]

Phil:
In the above code, remove all instances of the word "Call".

That wouldn't be a good idea Phil.

@NeoPa,
To help me understand, is there an advantage using

Call MsgBox("Please Select B1 To Proceed")

over using

MsgBox "Please Select B1 To Proceed"

I have always used the latter, and have seen some folks using the Call Statement frequently, but found that method superfluous.

Again, I like to understand why Access Jedis use the methods they use, as sometimes the invisible things help me understand the bigger picture (such as Boolean values "<> False").

[PhilOfWalton]

I always use

MsgBox "Please Select B1 To Proceed"
 

Equally I would use

    With Me
        .B1.SetFocus
    End With
 

I don't see what the "Call" achieves

In fact, in one of my programs, I have to use a user defined MsgBox function (for messages in non Latin scrips, like Russian) and still use the same syntax.

Phil

[NeoPa]

Phil:
I don't see what the "Call" achieves

Let me try to explain.

The reason I recommend using the Call syntax is that it tells the reader immediately that the line branches to a procedure and is not a statement of direct VBA action, nor is it an array reference, which also uses parentheses for the element indicator. It's often helpful to see where, and how often, any code branches off elsewhere to get things done. Using Call is explicit and makes that obvious.

So, while it may not be very common practice, it's certainly a better way to work - particularly if you have to work with others at any stage.

Another recommendation, based on seeing it confuse people a great deal, is that where there is any complication to a procedure call, and of course where it's even possible, named parameters should be used instead of positional parameters. I've lost count of how many questions have been seen here where members have simply lost count of the commas between parameters and one or more has ended up in the wrong place.