Completing Forms

[Robster]

Hope someone can correct me here.

I have a form with various text, date and combo controls. There is a button at the button that runs a macro (Close NB) at the bottom. What I'm trying to do is bring up a msgbox if certain fields are blank and not run the macro. I only want the macro to run if all the fields specified have data in them.
The fields are :
cmb_cliname
cmb_disease
cmb_projectType
cmb_ProposalStatus


The on click code is:
If (Me.cmb_cliname Is Null) Then
MsgBox "Please fill in the relevant details",
ElseIf (Me.cmb_Disease Is Null) Then
MsgBox "Please fill in the relevant details"
ElseIf (Me.cmb_ProjectType Is Null) Then
MsgBox "Please fill in the relevant details"
ElseIf (Me.Cmb_ProposalStatus Is Null) Then
MsgBox "Please fill in the relevant details"

Else: DoCmd.RunMacro CloseNB

End If
End If
End If
End If

Please let me know if I need to clarify further.

Any help appreciated.

Thanks

 

[Frothingslosh]

1. Please use code tags and indentation when pasting code. It makes the code much, MUCH easier to read.
2. I notice the code you provided only includes one IF statement but four End If lines. This will cause the code to fail to compile. If this is part of a larger, nested series of IF statements, then please provide the entire procedure.
3. What error messages are you receiving when you click your button?

 

[Robster]

Hi Frothingslosh,

Thanks for your reply.

I'm afraid I dont know what you mean by code tags as I'm fairly new to this.
I put the End if's in as I was doing an 'If x then Y else If' statement.
Is this not correct?

the error I'm getting is a compile error. Syntax error.


Thanks

 

[Frothingslosh]

For code tags, read THIS. That same link also shows you how to indent your code to make it legible, specifically to make it easier to resolve errors like the one you're having.

To point you in the right direction: ElseIf is *NOT* an IF statement, but rather is part of one.

 

[Robster]

Thanks.
How's this?

If (Me.cmb_cliname Is Null) Then
    MsgBox "Please fill in the relevant details"
    Else
    If (Me.cmb_Disease Is Null) Then
    MsgBox "Please fill in the relevant details"
    Else
    If (Me.cmb_ProjectType Is Null) Then
    MsgBox "Please fill in the relevant details"
    Else
    If (Me.Cmb_ProposalStatus Is Null) Then
    MsgBox "Please fill in the relevant details"
    
    Else: DoCmd.RunMacro CloseNB
        
    End If
    End If
    End If
    End If

 

[Frothingslosh]

Thanks.
How's this?

If (Me.cmb_cliname Is Null) Then
    MsgBox "Please fill in the relevant details"
    Else
    If (Me.cmb_Disease Is Null) Then
    MsgBox "Please fill in the relevant details"
    Else
    If (Me.cmb_ProjectType Is Null) Then
    MsgBox "Please fill in the relevant details"
    Else
    If (Me.Cmb_ProposalStatus Is Null) Then
    MsgBox "Please fill in the relevant details"
 
    Else: DoCmd.RunMacro CloseNB
 
    End If
    End If
    End If
    End If

It's a start, but still wrong. Proper code indentation would be:

    If (Me.cmb_cliname Is Null) Then
        MsgBox "Please fill in the relevant details"
    Else
        If (Me.cmb_Disease Is Null) Then
            MsgBox "Please fill in the relevant details"
        Else
            If (Me.cmb_ProjectType Is Null) Then
                MsgBox "Please fill in the relevant details"
            Else
                If (Me.Cmb_ProposalStatus Is Null) Then
                    MsgBox "Please fill in the relevant details"
                [COLOR=red]Else: DoCmd.RunMacro CloseNB
[/COLOR]                End If
            End If
        End If
    End If

I note that you decided to break the ElseIf conditions down into nested IF/THEN sections, which wasn't really what I was trying to point you to. Your approach does work, but you could just have easily left the ElseIf statements alone as long as you only had one End If, as ElseIf is considered part of the same statement.

That said, you still have errors.

• The parentheses aren't needed here, as you're only testing a single condition at a time. Not really an error, but unnecessary.
• "If x Is Null" is how you would write it in a SQL statement. In VBA, you want to use the IsNull function instead.
• The line I colored red has incorrect syntax - what you're actually doing there is creating a line label called "Else" rather than creating a final condition.

 

[Robster]

Thanks.

I've amended the code and it's now working.

Thanks for your help.