Error Message Block if without End If 2

[Lavenderchan]

Hi I was writing a code to depending on a task I add a few items to it and it worked fine now all I'm getting is the Error Message Block If without End If. some suggestions I've seen on the web said to count the if statements but I'm not sure if there is to be an equal number of end if's. Any help would be apprecatied.

Here is the code I have so far:
Private Sub cboTask_BeforeUpdate(Cancel As Integer)

If Me.cboTask = ("Annual Operating Budget & Rent Increase Request") Then
Me.Due_Date = Date + 30
If Me.cboTask = ("Annual Operating Budget & Rent Increase Request") Then
Me.Status = ("Active")
End If

If Me.cboTask = ("Annual Financial Statement Response") Then
Me.Due_Date = Date + 30
If Me.cboTask = ("Annual Financial Statement Response") Then
Me.Status = ("Active")
End If

If Me.cboTask = ("2530 New Role for Property") Then
Me.Due_Date = Date + 30
If Me.cboTask = ("2530 New Role for Property") Then
Me.Status = ("Active")
End If

If Me.cboTask = ("2530 Submission") Then
Me.Due_Date = Date + 30
If Me.cboTask = ("2530 Submission") Then
Me.Status = ("Active")
End If

If Me.cboTask = ("Addendum B MOR Form 9834") Then
Me.Due_Date = Date
If Me.cboTask = ("Addendum B MOR Form 9834") Then
Me.Status = ("No Response Required")
End If

If Me.cboTask = ("Affirmative Fair housing Marketing Plan") Then
Me.Due_Date = Date
If Me.cboTask = ("Affirmative Fair housing Marketing Plan") Then
Me.Status = ("No Response Required")
Me.Comments = ("Mail to Cincinnati")
End If
End If

Thanks,
Keri

 

[PWise]

try

If Me.cboTask = ("Annual Operating Budget & Rent Increase Request") Then
    Me.Due_Date = Date + 30
End If
  If Me.cboTask = ("Annual Operating Budget & Rent Increase Request") Then
    Me.Status = ("Active")
End If
 
If Me.cboTask = ("Annual Financial Statement Response") Then
    Me.Due_Date = Date + 30
End If
    If Me.cboTask = ("Annual Financial Statement Response") Then
    Me.Status = ("Active")
End If
 
If Me.cboTask = ("2530 New Role for Property") Then
    Me.Due_Date = Date + 30
End If
    If Me.cboTask = ("2530 New Role for Property") Then
    Me.Status = ("Active")
End If
    
If Me.cboTask = ("2530 Submission") Then
    Me.Due_Date = Date + 30
End If
If Me.cboTask = ("2530 Submission") Then
    Me.Status = ("Active")
End If
    
If Me.cboTask = ("Addendum B MOR Form 9834") Then
    Me.Due_Date = Date
End If
    If Me.cboTask = ("Addendum B MOR Form 9834") Then
    Me.Status = ("No Response Required")
    End If
    
If Me.cboTask = ("Affirmative Fair housing Marketing Plan") Then
    Me.Due_Date = Date
End If
If Me.cboTask = ("Affirmative Fair housing Marketing Plan") Then
    Me.Status = ("No Response Required")
    Me.Comments = ("Mail to Cincinnati")
    End If

 

[Lavenderchan]

Thank you very Much.
Keri

 

[dhookom]

Wouldn't this work ok and be a bit simpler

SELECT CASE Me.cboTask 
    Case "Annual Operating Budget & Rent Increase Request"
        Me.Due_Date = Date + 30
        Me.Status = "Active"
    Case "Annual Financial Statement Response"
        Me.Due_Date = Date + 30
        Me.Status = "Active"
    Case "2530 New Role for Property"
        Me.Due_Date = Date + 30
        Me.Status = "Active"
    Case "2530 Submission"
        Me.Due_Date = Date + 30
        Me.Status = "Active"
    Case "Addendum B MOR Form 9834"
        Me.Due_Date = Date
        Me.Status = "No Response Required"
    Case "Affirmative Fair housing Marketing Plan"
        Me.Due_Date = Date
        Me.Status = "No Response Required"
        Me.Comments = "Mail to Cincinnati"
END SELECT

Duane
Hook'D on Access
MS Access MVP

 

[PWise]

Even Simpler

Select Case Me.cboTask
    Case "Annual Operating Budget & Rent Increase Request", _
          "Annual Financial Statement Response", _
          "2530 New Role for Property", _
          "2530 Submission"
             Me.Due_Date = Date + 30
             Me.Status = "Active"
    Case "Addendum B MOR Form 9834", "Affirmative Fair housing Marketing Plan"
        Me.Due_Date = Date
        Me.Status = "No Response Required"
        If Me.cboTask = "Affirmative Fair housing Marketing Plan" Then Me.Comments = "Mail to Cincinnati"
End Select

 

[Lavenderchan]

Oh I didn't know there were others ways to write it. I've just starting learning about vba.
thank you.
Keri

 

[dhookom]

Actually I would probably attempt to model this all in tables rather than hard-coding values into modules. This type of code should either be in tables or functions in a standard module of business calculations.

There are a couple very good resources on the web. Here is one Data belongs in your tables -- not in your code

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

Hi I have taken the advice and put my data in a table so I can use the case method and have it stored in the table instead of the code? I still a little confused?
Thanks,

Keri

 

[dhookom]

How did you set up your table? I assume it has a Task field as the primary key and then a couple columns/fields to store DatePlus, Status, and Comments.

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

Yes I did set the table up that way but I'm not sure how to do the code.

 

[dhookom]

It would have made sense to reply back with your table and field names. However, try something like:

Dim strComments As String
Me.Due_Date = Date + DLookup("DatePlus","Yourtable","Task=""" & Me.cboTask & """")
Me.Status = DLookup("Status","Yourtable","Task=""" & Me.cboTask & """")
strComments = DLookup("Comments","Yourtable","Task=""" & Me.cboTask & """") & ""
If Len(strComments) > 0 Then 
   Me.Comments = strComments
End If

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

Hi If my due date is returning a null value do I use the len function or nz?
thanks,
Keri

 

[dhookom]

I would make sure the table has values so you wouldn't return nulls. You could use:

Date + Val(Nz(DLookup("DatePlus","Yourtable","Task=""" & Me.cboTask & """"),0))

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

Hi duane,

Would I get a return null if the value types of my field are different. My table fields are Task,DatePlus,Status and Comments. I have the Date Plus set as a number and my due date field on my form is set as date. I have change both the text and back but I'm still returning a null. for Me.Due_Date? Is it because I did the event before update. It worked fine when I did the values in the code with the select case. I'm confused why I keep getting the 2001 runtime error.
Thanks for your help.
Keri

 

[dhookom]

I would use the After Update event of the combo box. Doesn't cboTask have a Row Source of a small lookup table? Is this lookup table the one you used for "My table fields are Task,DatePlus,Status and Comments"?


Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

Yes, I figured it out. I was missing my dateplus field in the row source. Thank you very much for your help.
keri