Changing calculation depending on Combobox

**GraemeG** · 02-15-2011, 07:25 AM

I have also taken your example and ammended.

Now this does not flag up any debugs. But does not calculate the renew year once install year entered.
As per below

Code:

Private Sub SecondaryHeatingInstallYear_AfterUpdate()
Select Case Me.SecondaryHeating
    Case Is = "Electric Fire(10)"
        Me.[SecondaryHeatingRenewYear] = Me.[SecondaryHeatingInstallYear] + 10
            If Me.[SecondaryHeatingRenewYear] <= Format(Date, "yyyy") Then
            Me.[SecondaryHeatingRenewYear] = Format(Date, "yyyy")
            End If
    Case Is = "Gas Fire(15)"
     Me.[SecondaryHeatingRenewYear] = Me.[SecondaryHeatingInstallYear] + 15
            If Me.[SecondaryHeatingRenewYear] <= Format(Date, "yyyy") Then
            Me.[SecondaryHeatingRenewYear] = Format(Date, "yyyy")
            End If
            
    Case Is = "Tenants Own Gas"
     Me.[SecondaryHeatingRenewYear] = Me.[SecondaryHeatingInstallYear] + 15
            If Me.[SecondaryHeatingRenewYear] <= Format(Date, "yyyy") Then
            Me.[SecondaryHeatingRenewYear] = Format(Date, "yyyy")
            End If
            
    Case Is = "Tenants Own Electric"
     Me.[SecondaryHeatingRenewYear] = Me.[SecondaryHeatingInstallYear] + 10
            If Me.[SecondaryHeatingRenewYear] <= Format(Date, "yyyy") Then
            Me.[SecondaryHeatingRenewYear] = Format(Date, "yyyy")
            End If
            
    Case Is = "None"
     Me.[SecondaryHeatingRenewYear.Value] = 0 And Me.[SecondaryHeatingInstallYear.Value] = 0
           
End Select
End Sub

**jzwp11** · 02-15-2011, 07:29 AM

Your code does not make sense. I would get rid of all of it and start fresh.
Assuming that you want add some value to the install year to populate the renewal year control depending on what was chosen in the combo box, this is what the code would look like. I do not know what calculations you want to do for the other 3 options in the combo box, so you will have to add the appropriate calculation there.

Code:

Select Case Me.SecondaryHeating
    Case Is = "Electric Fire(10)"
    me.SecondaryHeatingRenewYear= me.SecondaryHeatingInstallYear+10
    Case Is = "Gas Fire(15)"
    me.SecondaryHeatingRenewYear= me.SecondaryHeatingInstallYear+15
    Case Is = "Tenants Own Gas"
    'your calculation for this option here
    Case Is = "Tenants Own Electric"
    'your calculation for this option here
    Case Is = "None"
    'your calculation for this option here
End Select

What else do you want to have happen?

**GraemeG** · 02-15-2011, 07:32 AM

Originally Posted by jzwp11

Your code does not make sense. I would get rid of all of it and start fresh.
Assuming that you want add some value to the install year to populate the renewal year control depending on what was chosen in the combo box, this is what the code would look like. I do not know what calculations you want to do for the other 3 options in the combo box, so you will have to add the appropriate calculation there.

Code:

Select Case Me.SecondaryHeating
    Case Is = "Electric Fire(10)"
    me.SecondaryHeatingRenewYear= me.SecondaryHeatingInstallYear+10
    Case Is = "Gas Fire(15)"
    me.SecondaryHeatingRenewYear= me.SecondaryHeatingInstallYear+15
    Case Is = "Tenants Own Gas"
    'your calculation for this option here
    Case Is = "Tenants Own Electric"
    'your calculation for this option here
    Case Is = "None"
    'your calculation for this option here
End Select

What else do you want to have happen?

Thanks yeah that first code was a mess lol!
The reply above yours is the code I have changed from your example. but I am not having any luck.
What and where would this event procedure take place?

**jzwp11** · 02-15-2011, 07:33 AM

My code would go in the same event, the after update event of the install year.

I see you posted while I was writing my earlier post.

What is the intent of this part of your code that you repeat several times:

Code:

If Me.[SecondaryHeatingRenewYear] <= Format(Date, "yyyy") Then
            Me.[SecondaryHeatingRenewYear] = Format(Date, "yyyy")
            End If

**GraemeG** · 02-15-2011, 07:37 AM

Originally Posted by jzwp11

My code would go in the same event, the after update event of the install year.

I see you posted while I was writing my earlier post.

What is the intent of this part of your code that you repeat several times:

Code:

If Me.[SecondaryHeatingRenewYear] <= Format(Date, "yyyy") Then
            Me.[SecondaryHeatingRenewYear] = Format(Date, "yyyy")
            End If

It is so that if an Install Year is entered so far in the past that the calculations makes the renew year < current year it brings it up to the current year.
I.e. Electric Fire Install Year = 1980
This has a 10 year life expectancy therefore renew year would have been calculated to 1990. But that code makes it go to current year.

**GraemeG** · 02-15-2011, 07:45 AM

Code:

Private Sub SecondaryHeatingInstallYear_AfterUpdate()
Select Case Me.[SecondaryHeating]
    Case Is = "Electric Fire(10)"
        Me.[SecondaryHeatingRenewYear] = Me.[SecondaryHeatingInstallYear] + 10
    Case Is = "Gas Fire(15)"
        Me.[SecondaryHeatingRenewYear] = Me.[SecondaryHeatingInstallYear] + 15
    Case Is = "Tenants Own Gas"
        Me.[SecondaryHeatingRenewYear] = Me.[SecondaryHeatingInstallYear] + 15
    Case Is = "Tenants Own Electric"
        Me.[SecondaryHeatingRenewYear] = Me.[SecondaryHeatingInstallYear] + 10
    Case Is = "None"
        Me.[SecondaryHeatingRenewYear.Value] = 0 And Me.[SecondaryHeatingInstallYear.Value] = 0
End Select
End Sub

Also tried this. It doesnt require any debug but the calculations do not work.
If you enter an install year nothing happens to the renew year.

**jzwp11** · 02-15-2011, 07:46 AM

How about this:

Code:

Select Case Me.SecondaryHeating
    Case Is = "Electric Fire(10)"
    me.SecondaryHeatingRenewYear= IIF(me.SecondaryHeatingInstallYear+10<year(date()),year(date()),me.SecondaryHeatingInstallYear+10)
    Case Is = "Gas Fire(15)"
    me.SecondaryHeatingRenewYear= IIF(me.SecondaryHeatingInstallYear+15<year(date()),year(date()),me.SecondaryHeatingInstallYear+15)
    Case Is = "Tenants Own Gas"
    'your calculation for this option here
    Case Is = "Tenants Own Electric"
    'your calculation for this option here
    Case Is = "None"
    'your calculation for this option here
End Select

**jzwp11** · 02-15-2011, 07:58 AM

I've attached an example database.

**GraemeG** · 02-15-2011, 08:04 AM

Sorry to be a pain.
The following still does not calculate the Renew Year.

Code:

Private Sub SecondaryHeatingInstallYear_AfterUpdate()
Select Case Me.SecondaryHeating
    Case Is = "Electric Fire(10)"
    Me.SecondaryHeatingRenewYear = IIf(Me.SecondaryHeatingInstallYear + 10 < Year(Date), Year(Date), Me.SecondaryHeatingInstallYear + 10)
    Case Is = "Gas Fire(15)"
    Me.SecondaryHeatingRenewYear = IIf(Me.SecondaryHeatingInstallYear + 15 < Year(Date), Year(Date), Me.SecondaryHeatingInstallYear + 15)
    Case Is = "Tenants Own Gas"
    Me.SecondaryHeatingRenewYear = IIf(Me.SecondaryHeatingInstallYear + 15 < Year(Date), Year(Date), Me.SecondaryHeatingInstallYear + 15)
    Case Is = "Tenants Own Electric"
    Me.SecondaryHeatingRenewYear = IIf(Me.SecondaryHeatingInstallYear + 10 < Year(Date), Year(Date), Me.SecondaryHeatingInstallYear + 10)
    Case Is = "None"
    Me.SecondaryHeatingInstallYear.Value = Null And Me.SecondaryHeatingRenewYear.Value = Null
End Select
End Sub

**GraemeG** · 02-15-2011, 08:09 AM

Originally Posted by jzwp11

I've attached an example database.

Thankyou!!!
I have just copied your code into my database and this worked a treat!!
Only issue is I had to turn combobox limit to list to know and bound column 1. Can I still do something to limit to list? --- Sorry ignore I have fixed this!!

Also to be a pain. rather than making a none repsonse = 0 can we make the none response diable the year text boxes?

**jzwp11** · 02-15-2011, 08:28 AM

In the attached, I have hidden the two year controls via the open event of the form. Then in the after update event of the combo box, I unhide the controls if something other than None is selected.

**GraemeG** · 02-15-2011, 08:44 AM

Once again thankyou very much!
Is there a way to make them visible on start up though?

**jzwp11** · 02-15-2011, 08:51 AM

Sorry, the last DB did not do what I intended; please try the attached.

**GraemeG** · 02-15-2011, 10:55 AM

Originally Posted by jzwp11

Sorry, the last DB did not do what I intended; please try the attached.

Thankyou so much for all your help. It was really much appreciated!

Might be few more threads coming soon lol...

**jzwp11** · 02-15-2011, 11:33 AM

You're welcome. Good luck with your database

Changing calculation depending on Combobox

Thread Tools

Similar Threads

change a combobox value depending on another

Filter subForm comboBox based on main form combobox selection - With a TWIST

Open Report or Form depending on condition

Return blank field depending on quantity

Changing the value of filed depending other field value

Posting Permissions