If statement question

[Anthony Viscomi]

Is there a better way of writing the following IF statements?

Private Sub Front_Material_AfterUpdate()
If Me.Front_Style.Value = "Albany" And Me.Front_Material.Value = "Maple"
Then
Me.Front_.Value = -0.15
End If

If Me.Front_Style.Value = "Albany" And Me.Front_Material.Value = "Cherry"
Then
Me.Front_.Value = -0.09
End If

If Me.Front_Style.Value = "Alpha" Then
Me.Front_.Value = -0.6
End If

If Me.Front_Style.Value = "Prairie" And Me.Front_Material.Value = "Maple"
Then
Me.Front_.Value = -0.24
End If

If Me.Front_Style.Value = "Prairie" And Me.Front_Material.Value = "Oak" Then
Me.Front_.Value = -0.27
End If

If Me.Front_Style.Value = "Prairie" Then
Me.Front_.Value = -0.6
End If

End Sub

I have about 15 more of these Front_Styles to add and I was wondering if
there was a different/easier way to write this and still achieve the same
results.

Thanks!
Anthony Viscomi

 

[Douglas J. Steele]

What about storing the values in a table, and doing a lookup (using DLookup,
if necessary)?

 

[Anthony Viscomi]

I initially started to do that but after reviewing the clients Catalog I
notice that method could become just as confusing; example:
Materials
Field Name(s): FrontStyle Maple Cherry Oak Etc.
"Albany" -6% -27% 5%
"Alpha" 10% -23% 2%
"Monarch" -9% 10% -60%
. .

 

[Lynn Trapp]

Field Name(s): FrontStyle Maple Cherry Oak Etc.

"Albany" -6% -27% 5%
"Alpha" 10% -23% 2%
"Monarch" -9% 10% -60%

PMFJI, but I think Doug had in mind you having a table with 3 fields --
FrontStyle, Material, and Percentage -- with a record for each of the
possible combinations. Otherwise you could nest your If statement similar to
this:

If Me.Front_Style = "Albany" Then

If Me.Front_Material = "Maple" Then

Me.Front_ = -0.06

Else If Me.Front_Material = "Cherry" Then

Me.Front_ = -0.27

Else

Me.Front_ = 0.05

End If

Else If Me.Front_Style = "Alpha" Then

If Me.Front_Material = "Maple" Then

Me.Front_ = 0.10

Else If Me.Front_Material = "Cherry" Then

Me.Front_ = -0.23

Else

Me.Front_ = 0.02

End If

Else If Me.Front_Style = "Monarch" Then

If Me.Front_Material = "Maple" Then

Me.Front_ = -0.09

Else If Me.Front_Material = "Cherry" Then

Me.Front_ = 0.10

Else

Me.Front_ = -0.60

End If

End If

 

[Anthony Viscomi]

Thanks! it looks like I'll be going the "If Statement" route.