If, Then, ElseIf?

[Samantha]

I am running Access 2010, and don't do this enough to really know what I'm doing. I have googled this and searched these threads for most of the day in an attempt to solve this issue. Frankly, I could be going about it completely wrong.
In laymen's terms I am attempted to run this code on a onclick event for a merge procedure. I am attempting to make it say: If the project type is TM and the TM Rate is null then display message box, exit the procedure and set focus to the control.

'Check TM Rate for a value
    If (([Project_Type]) = "1") Then
        ElseIf IsNull(TMRate) Or TMRate = "" Then
    MsgBox "Please fill in the Time and Materials Rate before proceeding", vbOKOnly
    TMRate.SetFocus
    GoTo Exit_Procedure
    End If

Thanks for your time!:banghead:

 

[azfen]

If [Project_Type] = "1" And (IsNull(TMRate) = True Or TMRate = "") Then
MsgBox "Please fill in the Time and Materials Rate before proceeding", vbOKOnly
TMRate.SetFocus
GoTo Exit_Procedure
End If

 

[Samantha]

Thanks for the response! I made the changes as specified and commented out the first If, then block. It still skips right over the code and continues to execute. I posted more of the code thinking it may have some indication of what the problem is. No errors are thrown.

Private Sub MergeBttn_Click()
    'On Error GoTo Error_Handler
    
    'Declare variables for storing strings.
    Dim AddyLineVar As String, SalutationVar As String
    Dim DeliveryAdd As String, PmName As String, CustCompany As String
    Dim ProjectDescriptionLine As String, Introduction As String
            
    'Check Contract Amount for a value
    'If IsNull(ContractAmount) Or ContractAmount = "" Then
    'MsgBox "Please fill in the Contract Value before proceeding", vbOKOnly
    'ContractAmount.SetFocus
    'GoTo Exit_Procedure
    'End If
        
    'Check TM Rate for a value
    If (([Project_Type]) = "1") And (IsNull(TMRate) = True Or TMRate = "") Then
    MsgBox "Please fill in the Time and Materials Rate before proceeding", vbOKOnly
    TMRate.SetFocus
    GoTo Exit_Procedure
    End If
      
    'Start building AddyLineVar, by dealing with blank last name fields.
    If IsNull([sfrmContacts].[Form]![Last]) Then
        AddyLineVar = [Company]
        'Just set salutation to generic.
        SalutationVar = "Sir or Madam"
    Else
        AddyLineVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
        
        'Add Company on after name.
        If Not IsNull([Company]) Then
            AddyLineVar = AddyLineVar & vbCrLf & [Company]
        End If
        
        'Salutation will be customer's last name
        SalutationVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![Last]) & ", "
    End If
    
        'Start building DeliveryAdd, by dealing with blank email fields.
        If IsNull([sfrmContacts].[Form]![Email]) Then
            DeliveryAdd = "Fax:      " & [sfrmContacts].[Form]![BusinessFax]
        Else
            DeliveryAdd = "Email:   " & [sfrmContacts].[Form]![Email]
    End If
        
        'Set introduction line budgetary vs contract
        If ([Project_Type]) = "1" Then
            Introduction = "would like to submit the following budgetary costs."
        Else
            Introduction = "proposes to complete the following scope of work."
    End If
     'Add line break and Address lines.
    AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![Address])
    'Tack on line break then city, state, and zip.
    AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![City]) & ", "
    AddyLineVar = AddyLineVar & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])
    CustCompany = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last]) & vbCrLf & [Company]
    
    'Set PMName to first and last name
    PmName = [sfrmPMTitles].[Form]![FirstName] & " " & [sfrmPMTitles].[Form]![LastName]
    
    'Declare an instance of MS Word.
    Dim Wrd As New Word.Application
    Set Wrd = CreateObject("Word.Application")
    
    'Specify the path and name to the word document.
    Dim MergeDoc As String
    MergeDoc = Application.CurrentProject.Path
    MergeDoc = MergeDoc & "\WordFormLetter.dotx"
    
    'Open the word document template, make it visible.
    Wrd.Documents.Add MergeDoc
    Wrd.Visible = True

 

[azfen]

You are using IsNull function incorrectly. For example instead of:

If Not IsNull([Company])

you should write:

If IsNull([Company]) = False

Go through your code and fix all the InNull expressions. You should assign either = True or =False to this expression.

 

[Frothingslosh]

To clarify what's going on, IF...ELSEIF...ELSE works like this:

[COLOR=black]If <Condition> is TRUE Then[/COLOR]
[COLOR=red]   <code gets executed>[/COLOR]
ElseIf <another condition checked only if previous condition is false> is TRUE then
[COLOR=red]   <different code gets executed>[/COLOR]
<add more ElseIf segments as needed>
Else
[COLOR=red]   <more code runs if nothing evaluated true>[/COLOR]
End If

That might help you in laying these things out down the road. Just keep in mind that once a condition evaluates as true, that condition's code gets run, then VBA exits the If/Then entirely.

 

[Frothingslosh]

"If IsNull([Company]) Then" is perfectly fine in almost every version of Access - it returns a true or false, and true results automatically trigger the then. I use that formatting all the time in Access 2003 and later.

Try running this:

If IsNull(Null) Then
    Debug.Print "NULL"
Else
    Debug.Print "NOT NULL"
End If

Note that 'NULL' will always print.

That means her issue is somewhere else.

 

[Samantha]

"
Try running this:

If IsNull(Null) Then
    Debug.Print "NULL"
Else
    Debug.Print "NOT NULL"
End If

Note that 'NULL' will always print.

That means his issue is somewhere else.

Frothingslosh, Where would I run the code at in the Immediate Window?

 

[Frothingslosh]

In the VB editor, hit 'View', then 'Immediate Window'. YOu'll need to put it into a module as a sub first, though, and then just run it via that window.

I found one problem, by the way: are you using Option Explicit? If not, you should.

Always.

No exceptions.

The problem I spotted is that TMRate is undeclared, and if you don't have Option Explicit set, what's happening is it's getting declared at

If (([Project_Type]) = "1") And (IsNull(TMRate) = True Or TMRate = "") Then

and being populated with a null.

Also, you can replace

IsNull(TMRate) = True Or TMRate = ""

with

Len(TMRate) = 0

for the same result.

 

[Samantha]

"I found one problem, by the way: are you using Option Explicit? If not, you should."

No, I don't think so. Most of this code is borrowed from here or there. I have seen the statement before but have no idea really.

 

[Frothingslosh]

Option Explicit would be at the top of the module for the form. It's one of the declarations that have to be made before everything else, much like Option Compare (Database/Text/Binary) and Option Base 0/1. It's something you should always use, because it forces all variables to be explicitly declared. That keeps you from accidentally creating or mislabeling variables and getting bad data as a result.

It also looks like you're trying to refer to controls on the form this is bound to? When you do that, you need to use either Forms!Formname.Control, or Me.Control. If a subform jumps into the picture, it's Forms!Formname!Subformcontrol.Form.Control or Me!Subform1.Form.Control. Here's a helpful explanation of that:

http://access.mvps.org/access/forms/frm0031.htm

Basically, for each control you're trying to reference by using only the form name, what's happening is that a Variant is being created with that control's name, and as it starts with a NULL value, all your If/Thems are evaluating them as nulls.

 

[Samantha]

Option explicit should be the first line in the code previous to anything else, correct?

Private Sub MergeBttn_Click()
Option Explicit

That is giving me a compile error: invalid inside procedure?

 

[Samantha]

Okay got the Option Explicit at the top of the module it was Option Compare Database

 

[Frothingslosh]

Option explicit should be the first line in the code previous to anything else, correct?

Private Sub MergeBttn_Click()
Option Explicit

That is giving me a compile error: invalid inside procedure?

Not quite. It needs to be at the very top of the module itself. There should already be the line 'Option Compare Database' there unless someone changed that. The options are set before any procedures appear at all.

Edit: You'll want both Option Compare and Option Explicit there. They do two different things.

 

[Samantha]

It also looks like you're trying to refer to controls on the form this is bound to?

Yes

When you do that, you need to use either Forms!Formname.Control, or Me.Control.

Changed Code to:

 If ((Me.Project_Type) = "1") And Len(Me.TMRate) = 0 Then
    MsgBox "Please fill in the Time and Materials Rate before proceeding", vbOKOnly
    Me.TMRate.SetFocus
    GoTo Exit_Procedure
    End If

Still Executing as though the cell has a value?

 

[Frothingslosh]

Okay, is Project_Type text or a number field? If it's a number field, you don't need to put the 1 in quotes. I don't *THINK* that would effect the execution, however.

Do you have the form open? And you're certain there's no value in TMRate? Even a space will count as data.

Edit: Here's a thought: Try this instead:

If Me.Project_Type = "1" And Len(nz(Me.TMRate)) = 0 Then
    MsgBox "Please fill in the Time and Materials Rate before proceeding", vbOKOnly
    Me.TMRate.SetFocus
    GoTo Exit_Procedure
End If

I forgot that Len(Null) returns Null, not 0.

 

[Samantha]

Perhaps I should tell you that Project_Type is a combo box based on the table tblProjectType and I am wrong the source of the form is a query combining several tables. So, you think I should go back and refer to the fields using Forms!Formname.Control?

 

[Frothingslosh]

One other suggestion, by the way. All those If...Then blocks that end with exiting? You can actually chain them like this:

If <condition looking for problem> Then
     <code that runs if condition is met>
ElseIf <another condition looking for problems> Then
    <different code that runs if this condition is met>
ElseIf etc, etc, etc
Else
    <Put remainder of code here, so it only runs no point of failure above is met>
End If

This kind of thing just makes your process flow a little easier to follow, as you now only have one point of exit to deal with.

It's not by any means mandatory, just (usually) helpful.

 

[Frothingslosh]

Perhaps I should tell you that Project_Type is a combo box based on the table tblProjectType and I am wrong the source of the form is a query combining several tables. So, you think I should go back and refer to the fields using Forms!Formname.Control?

Nah, Me is fine. Me in this case is actually shorthand for 'the form whose module contains this procedure'.

I'm assuming the data that's actually bound to Project_Type is a long integer, then, so you don't need the quotes around the number one.

 

[Frothingslosh]

Did you catch my ninja-edit on page 1 about this?

If Me.Project_Type = "1" And Len(nz(Me.TMRate)) = 0 Then
    MsgBox "Please fill in the Time and Materials Rate before proceeding", vbOKOnly
    Me.TMRate.SetFocus
    GoTo Exit_Procedure
End If

You should try using that and see what happens. I'd forgotten that Len(Null) returns Null.

 

[Samantha]

It is a text field and I even refer to the same control further down in the code

'Set introduction line budgetary vs contract
        If ([Project_Type]) = "4" Then
            Introduction = "would like to submit the following budgetary costs."
        Else
            Introduction = "proposes to complete the following scope of work."
    End If

I changed it to the new variation

'Check TM Rate for a value
    If ((Me.Project_Type) = "1") And Len(Nz(Me.TMRate) = 0) Then
    MsgBox "Please fill in the Time and Materials Rate before proceeding", vbOKOnly
    Me.TMRate.SetFocus
    GoTo Exit_Procedure
    End If

The message box appears now weather there is a value or not.