Changing calculation depending on Combobox

[GraemeG]

Hello,



Is there any way to make the calculation in the following code be different depending on a response made in a combo box.

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

The combo box is called SecondaryHeating.
repsonses:
None
Electric Fire(10)
Gas Fire(15)

The options in there have different life expectancies i.e the number in brackets. Therefore the calculation would need to be triggered to the right one once a selection is made. So Electric fire would be install year +10 and gas fire would be install year +15.

my immediate thinking is by a Select Case strucutre. invoking the different calcs depending on the case.


Thanks

[jzwp11]

If the life expectancy were a separate field/value brought in by the combo box, you could reference that field directly in your calculation.

Me.[SecondaryHeatingRenewYear] <= dateadd("yyyy", me.SecondaryHeating.column(x), yourdatefield)

Of course, you would still have to take care of "none" condition in some way.

[GraemeG]

If the life expectancy were a separate field/value brought in by the combo box, you could reference that field directly in your calculation.

Me.[SecondaryHeatingRenewYear] <= dateadd("yyyy", me.SecondaryHeating.column(x), yourdatefield)

Of course, you would still have to take care of "none" condition in some way.

Thanks I sort of understand. (When an option is slected it gives it a bound column number) however not sure how to utilise this into the code?

[jzwp11]

Generally the bound column of the combo box is a unique value that identifies the item. If the row source of the combo box is a table, then the bound column is usually the primary key field of that table. I would guess that the life expectancy is not the key field of a table, so you would reference it using the column() property.

Can you explain in more detail what you are trying to do and what tables/fields are involved?

[GraemeG]

Please find attached an example of a part of my database.

You will see that the first field on the form is a combo box. In here are selections (but they have a different number in the brackets) This represents their life expectancy.

Now if you enter an INSTALL YEAR you will see that it calculates a renewal year (this is based on the Install Year entered plus its life expectancy)

Therefore depending on what option the user chooses in the combobox this calculation needs to change to represent the right life expectancy.

Hope this explains it better.
Help is much appreciated!

[jzwp11]

Unfortunately, I cannot look at your database since we only have Access 2003 here at work, so I can look at it tonight from home (I have Access 2010 there).

How are you populating the combo box?

Is this data coming from a table or are you typing in these values:
None
Electric Fire(10)
Gas Fire(15)



Is the Install year strictly a number field in the table and not a date field?

Are you trying to save the calculated renewal year in the form's underlying table?

[GraemeG]

Combo box - Just typed them in using valuelist.
Install Year - is just a text field (not number not date)
As for calculated renewal year being in the table. I want all data to appear in the underlying table. Which it currently does.
Thanks for your help.

[jzwp11]

I want all data to appear in the underlying table

Storing a calculated value is generally not done. There are only a few special cases where it is appropriate and based on what you have described, your case is not one of those special cases. Storing a calculated value may impact your data integrity.

In terms of your combo box. I would recommend setting up a table, but if you want to use a value list, I would set up the combo box with 3 columns and separate out the life expectancies into a separate column

RowSource: 1;Electric Fire;10;2;Gas Fire;5;3;None;0

The bound field will be the first column and is unique to each value in the list.

You can set the column widths of the first field and the life expectancy field to zero, so the user does not see them.

On your form, you would need the user to input the Installyear before reaching the combo box.

In the after update event of the combo box, you would need the following code to populate the renewal year based on the selection made in the combo box. The renewal year control should be after the combo box.

Me.RenewalYear = Int(Me.InstallYear) + Me.cboType.Column(2)

The code first converts the installyear value to an integer since you cannot add a number to a text field and then adds the appropriate value based on the combo box selection.

For the "None" selection the installyear=renewal year. You will have to decide what to do with that one based on your business rules.

[GraemeG]

Ok Thanks for this. But seems bit complicated to change everything set up now.
Think I might just take the calculations out and make the user work it out themselves.

[jzwp11]

My recommendation of having a table allows the flexibility to add other types of units with other life expectencies. As you have it now, you would have to rework your combo box every time there is a new type of unit or if the life expectencies change. It might seem more complicated and at first it defintely will be, but setting up everything now & planning for the future will make the database more flexible and easier to manage in the long run. In order to do something like this will require an analysis of your present system, your currrent business processes and needs and build in the flexibility for the future. Of course, you cannot anticipate everything, but following basic design principles will give you a good foundation.

[GraemeG]

My recommendation of having a table allows the flexibility to add other types of units with other life expectencies. As you have it now, you would have to rework your combo box every time there is a new type of unit or if the life expectencies change. It might seem more complicated and at first it defintely will be, but setting up everything now & planning for the future will make the database more flexible and easier to manage in the long run. In order to do something like this will require an analysis of your present system, your currrent business processes and needs and build in the flexibility for the future. Of course, you cannot anticipate everything, but following basic design principles will give you a good foundation.

Thanks I definately agree ther, and might do this i nthe long run. However I am under pressure to get this version out asap as we are working on it without validations at the minute, and manually checking the data.
As for the combobox options having life expectancies. That is just a label. The number in the brackets doe snot do anything towards the caluclation. The calc is done in the VB code.
Is there nothing that can be done to this in the mean time I.e. a select case type?
I.e
Case = Combobox = "Electric"
Case Is ...
Case = Combobox = "Gas"
Case Is ...

The following is an example of the code at the min (ignore the fieldnames)
So you can see how that calc works for an install year entry.
But can a select case be inserted to run the different calcs?

Me.[DomesticRenewYear] = Me.[DomesticInstallYear] + 15
If Me.[DomesticRenewYear] <= Format(Date, "yyyy") Then
            Me.[DomesticRenewYear] = Format(Date, "yyyy")
    End If

[jzwp11]

I'm guessing that this is what you are looking for

Select Case Me.SecondaryHeating
    Case Is = "Electric Fire(10)"
    'your calculation for this option here
    Case Is = "Gas Fire(15)"
    'your calculation for this option here
    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

[GraemeG]

I'm guessing that this is what you are looking for

Select Case Me.SecondaryHeating
    Case Is = "Electric Fire(10)"
    'your calculation for this option here
    Case Is = "Gas Fire(15)"
    'your calculation for this option here
    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

Thanks. However this does not seem to make sense. Do you not have to define the Select Case first?

[jzwp11]

The code does define the Select Case as referring to the combo box with this part of the code

Select Case Me.SecondaryHeating

[GraemeG]

I am still newbie and dont quite understand Select Case.
I have compiled this, but it does not work. Maybe you can see my errors.

Private Sub SecondaryHeatingInstallYear_AfterUpdate()
None = Me.SecondaryHeating = "None"
Elec10 = Me.SecondaryHeating = "Electric Fire(10)"
Gas15 = Me.SecondaryHeating = "Gas Fire(15)"
TenElec = Me.SecondaryHeating = "Tenants Own Electric"
TenGas = Me.SecondaryHeating = "Tenants Own Gas"
Select Case None
Case Is = (Me.[SecondaryHeatingInstallYear] & "") = 0 And (Me.[SecondaryHeatingRenewYear] & "") = 0
Cancel = True
MsgBox "Quant, Install Year and Renew Year Not Required"
Me.WaterHeatingSystem.SetFocus
End Select
Select Case Elec10
If IsNull(SecondaryHeatingRenewYear.Value) Then
    Me.[SecondaryHeatingInstallYear] = ""
Case Is = CLng(Me.[SecondaryHeatingRenewYear]) = CLng(Me.[SecondaryHeatingInstallYear] + 10)
If Me.[SecondaryHeatingRenewYear] <= Format(Date, "yyyy") Then
    Me.[SecondaryHeatingRenewYear] = Format(Date, "yyyy")
End If
End If
End Select
    
Select Case Gas15
If IsNull(SecondaryHeatingRenewYear.Value) Then
    Me.[SecondaryHeatingInstallYear] = ""
Case Is = CLng(Me.[SecondaryHeatingRenewYear]) = CLng(Me.[SecondaryHeatingInstallYear] + 15)
If Me.[SecondaryHeatingRenewYear] <= Format(Date, "yyyy") Then
    Me.[SecondaryHeatingRenewYear] = Format(Date, "yyyy")
End If
End If
End Select
Select Case TenElec
If IsNull(SecondaryHeatingRenewYear.Value) Then
    Me.[SecondaryHeatingInstallYear] = ""
Case Is = CLng(Me.[SecondaryHeatingRenewYear]) = CLng(Me.[SecondaryHeatingInstallYear] + 10)
If Me.[SecondaryHeatingRenewYear] <= Format(Date, "yyyy") Then
    Me.[SecondaryHeatingRenewYear] = Format(Date, "yyyy")
End If
End If
End Select
Select Case TenGas
If IsNull(SecondaryHeatingRenewYear.Value) Then
    Me.[SecondaryHeatingInstallYear] = ""
Case Is = CLng(Me.[SecondaryHeatingRenewYear]) = CLng(Me.[SecondaryHeatingInstallYear] + 15)
If Me.[SecondaryHeatingRenewYear] <= Format(Date, "yyyy") Then
    Me.[SecondaryHeatingRenewYear] = Format(Date, "yyyy")
End If
End If
End Select
End Sub