If/Else problem (1 Viewer)

[JPR]

Hello,

I am having problems with the execution of a if/then statement and an exit sub procedure. Just cannot figure where is the mistake. I appreciate your help.
My project is to create a db to manage refunds which clients are due to my company.
Once in the main form, users will select the refund type from a combo. The refund can be total or partial.
My code creates an email and a simple text in the body after clicking on a cmdbutton.

The cmdbutton should first check if a unbound control (RatesNo) is Null. The control sets the number of rates due in the refund it Partial.
I
If is null, then the code should stop running the control RatesNo get focus so that users can add a value to it through a combobox.

If the RatesNo control is Not Null, then the code can be executed creating the email:

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim strBody As String


If Me.DEBTTYPE = "partial" And IsNull("Me.RemNo") Then
MsgBox "You have select the Remittance No.", vbOKOnly, "Remittance Log."
Me.RemNo.BorderColor = vbRed
Me.RemNo.SetFocus
Exit Sub
End If
  
If Me.DEBTTYPE = "total" Then
strBody = "Hello, this is a total refund." & vbCrLf & vbCrLf
End If

If Me.DEBTTYPE = "partial"  and Not is Null (Me.RemNo) Then
strBody = "Hello, this is  a partial refund.” & vbCrLf & vbCrLf
End If
  
 
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg

' Set the Subject, Body, and Importance of the message.
.Subject = "Debt Refund"
.Body = strBody
          
.To = xxxxxxx@google.com
.Display
End With
Set objOutlook = Nothing

 

[oleronesoftwares]

Why don't you use an IF-THEN-ELSE statement, instead of having two if statements?

 

[Minty]

I would always use a select case statement once you get past a single branch of checks.
I just find it a lot easier to read and debug.

Anyway, your problem is caused by this line;

and Not is Null (Me.RemNo) Then

You can't use that syntax in VBA you have to use (As you did in the first check) I'm surprised you don't get an error if you compile it?

and Not IsNull(Me.RemNo) Then

It's also easier to read if you indent the code.

  If Me.DEBTTYPE = "partial" And IsNull("Me.RemNo") Then
        MsgBox "You have select the Remittance No.", vbOKOnly, "Remittance Log."
        Me.RemNo.BorderColor = vbRed
        Me.RemNo.SetFocus
        Exit Sub
    End If
 
    If Me.DEBTTYPE = "total" Then
        strBody = "Hello, this is a total refund." & vbCrLf & vbCrLf
    End If

    If Me.DEBTTYPE = "partial" And Not IsNull(Me.RemNo) Then
        strBody = "Hello, this is  a partial refund.” & vbCrLf & vbCrLf"
    End If
 
 
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg

        ' Set the Subject, Body, and Importance of the message.
        .Subject = "Debt Refund"
        .Body = strBody
          
        .To = xxxxxxx@google.com
        .Display
    End With
    Set objOutlook = Nothing

 

[arnelgp]

If Me.DEBTTYPE = "partial" And IsNull("Me.RemNo") Then

that is your first IF, see where the error is? you should not enclosed Me.RemNo in quotes.

 

[Minty]

I missed that @arnelgp - Well spotted!

 

[MajP]

If Me.DEBTTYPE = "partial"
  If IsNull(Me.RemNo) Then
    MsgBox "You have select the Remittance No.", vbOKOnly, "Remittance Log."
    Me.RemNo.BorderColor = vbRed
    Me.RemNo.SetFocus
  Else
    strBody = "Hello, this is  a partial refund.” & vbCrLf & vbCrLf"
  End If
ElseIf Me.DEBTTYPE = "total" Then
   strBody = "Hello, this is a total refund." & vbCrLf & vbCrLf
End If

 

[JPR]

Sorry about the if is null. It was a typo. I removed the quotes. The code does execute until the msg box but but then it gets stuck, meaning that nothing else can be done and have to close the entire db.

 

[Minty]

What type of control is RemNo? Is it definitely called that as you mention RatesNo not RemNo in your first post?

Have you got Option Explicit at the top of the code module. You should do it everywhere, it will highlight errors and typos automatically.

 

[JPR]

the control's name is correct. Finally got it to work. What I did, is completely remove the code and wrote it again, once sentence at the time so that I could troubleshoot it.
The first part is what probably was causing the problem. Thank you

If Me.DEBTTYPE = "partial" And IsNull(Me.RemNo) Then
        MsgBox "You have to select the Remittance No.", vbOKOnly, "Remittance Log."
        Me.RemNo.BorderColor = vbRed
        Me.RemNo.SetFocus
       Exit Sub

End If