Could someone help with this code please?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am new to using VBA. I am trying to set the value of a field based on the
answers to three other fields. Here is what I've got so far....

Private Sub PI_Limit_of_Indemnity_AfterUpdate()
Select Case Me.PI_Limit_of_Indemnity.Value
Case PI_Limit_of_Indemnity = "£250,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£125.00"
End If
Case PI_Limit_of_Indemnity = "£500,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£135.00"
End If
Case PI_Limit_of_Indemnity = "£1,000,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£150.00"
End If
End Select

End Sub

There would be lots of additional code to write also based on different
answers in the Type of member and Fees fields as well but if I've got the
basic code right, I can carry on from there.

Many thanks in advance.

Also, this discussion group has been so useful - thanks everyone.
 
You've already declared what your checking in the first line of the Select
Case statement, so you don't put that on each Case line. See below.

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£125.00"
End If
Case "£500,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£135.00"
End If
Case "£1,000,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£150.00"
End If
End Select


I noticed you have "If" but no "Else" - are you sure you don't have an
alternate value to enter for each case? Also, you can populate a value for
an instance where NONE of the defined cases match by adding Case Else (and
value) after your last defined Case but before End Select:

Case "£1,000,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£150.00"
End If
Case Else
Me.PI_Premium.Value = "£1.00"
End Select
 
Thanks for this, I'm getting somewhere now... Just another question, how do
you use the between operator. Here is my next chunk of code but it is not
recognising it properly:-

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If Type_of_Member = "I" And Fees >= "50001" AND <= "75000" Then
Me.PI_Premium.Value = "£145.00"

A pop up box appears after carriage return which says Expected: expression
and highlighting the <=

Sorry, this is probably so simple to access wizards.

Thanks
 
A variable can't be compared to two things in one conditional. You have to
identify it each time:
If Type_of_Member = "I" And Fees >= "50001" AND <= "75000" Then
If Type_of_Member = "I" And (Fees >= "50001" AND Fees <= "75000") Then
Also, the parenthises are not necessarily required, but it makes certain the
order of comparison. It is also easier for a human to read.

I took the liberty of rewriting your code. This version will be easier to
read and will execute faster because it is doing comparisons once rather than
for each case. Notice that for all cases, you want only Type_of_Member = "I"
and Fees < "50000". If you check for both of those conditions up front, you
don't have to evaluate them again. Also notice I removed the references to
the .Value property. It is not necessary to specify this property.

Private Sub PI_Limit_of_Indemnity_AfterUpdate()
If Type_of_Member = "I" And Fees < "50000" Then
Select Case Me.PI_Limit_of_Indemnity
Case Is "£250,000"
Me.PI_Premium = "£125.00"
Case Is "£500,000"
Me.PI_Premium = "£135.00"
Case Is "£1,000,000"
Me.PI_Premium = "£150.00"
End Select
End If

End Sub
 
Glad to help! The issue here is unless you actually use "between" you have
to define the comparator both times. Also, you didn't define your
comparator - you need Me.[yourcontrol] rather than just [yourcontrol] - I
should have caught that the first time.

Try either:

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If Me.Type_of_Member = "I" And Me.Fees >= "50001" AND Me.Fees <= "75000"
Then
Me.PI_Premium.Value = "£145.00"


Or:

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If (Me.Type_of_Member = "I") And (Me.Fees BETWEEN "50001" AND <=
"75000") Then
Me.PI_Premium.Value = "£145.00"

Personally I prefer BETWEEN - easier to find and modify if need be.

SusanV
 
OK guys, here goes. This is my code. When Type_of_Member is "I" everything
seems to work ok. I thought I could follow this when Type_of_Member is "C" as
well (there are different Fee values too for "C") but it's not working. When
I change Type_of_Member to "C" it just follows the "I" code. I changed the
event to GotFocus too as this works better for me........

Private Sub Quote_Accepted_GotFocus()
If Type_of_Member = "I" And Fees <= "£50,000" Then
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£125.00"
Case "£500,000"
Me.PI_Premium = "£135.00"
Case "£1,000,000"
Me.PI_Premium = "£150.00"
End Select
End If

If Type_of_Member = "I" And (Fees >= "£50,001" And Fees <= "£75,000") Then
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£145.00"
Case "£500,000"
Me.PI_Premium = "£160.00"
Case "£1,000,000"
Me.PI_Premium = "£175.00"
End Select
End If

If Type_of_Member = "I" And Fees >= "£75,001" Then
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
x = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
Case "£500,000"
x = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
Case "£1,000,000"
x = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
End Select
End If

If Type_of_Member = "C" And Fees <= "£50,000" Then
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£125.00"
Case "£500,000"
Me.PI_Premium = "£135.00"
Case "£1,000,000"
Me.PI_Premium = "£150.00"
End Select
End If

If Type_of_Member = "C" And (Fees >= "£50,001" And Fees <= "£100,000")
Then
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£165.00"
Case "£500,000"
Me.PI_Premium = "£180.00"
Case "£1,000,000"
Me.PI_Premium = "£200.00"
End Select
End If

If Type_of_Member = "C" And (Fees >= "£100,001" And Fees <= "£250,000")
Then
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£250.00"
Case "£500,000"
Me.PI_Premium = "£270.00"
Case "£1,000,000"
Me.PI_Premium = "£300.00"
End Select
End If

If Type_of_Member = "C" And Fees >= "£250,001" Then
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
x = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
Case "£500,000"
x = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
Case "£1,000,000"
x = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
End Select
End If

End Sub

i'm sorry about this guys but now that I'm trying to do this, I'd really
love your help to solve it!!!

xx
 
Personally, I'd break this out differently, checking first the member type,
as that seems to have the fewest options.

Air code:

If Member = "I" then
'check Fee rates, assign premiums etc for Member type I using if's and
cases
ElseIf Member = "C" then
'same as I but for Member type C
Else 'to cover other types, or check for invalid entry in Member control
'Do other stuff
End If
 
Hi!

I changed the Type of member and Fees controls to show Me. in front of them
but the code I just posted still doesn't work when it gets down to
Type_of_Member "C"

Hope you can help.

Thanks.

SusanV said:
Glad to help! The issue here is unless you actually use "between" you have
to define the comparator both times. Also, you didn't define your
comparator - you need Me.[yourcontrol] rather than just [yourcontrol] - I
should have caught that the first time.

Try either:

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If Me.Type_of_Member = "I" And Me.Fees >= "50001" AND Me.Fees <= "75000"
Then
Me.PI_Premium.Value = "£145.00"


Or:

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If (Me.Type_of_Member = "I") And (Me.Fees BETWEEN "50001" AND <=
"75000") Then
Me.PI_Premium.Value = "£145.00"

Personally I prefer BETWEEN - easier to find and modify if need be.

SusanV


Gillian said:
Thanks for this, I'm getting somewhere now... Just another question, how
do
you use the between operator. Here is my next chunk of code but it is not
recognising it properly:-

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If Type_of_Member = "I" And Fees >= "50001" AND <= "75000" Then
Me.PI_Premium.Value = "£145.00"

A pop up box appears after carriage return which says Expected: expression
and highlighting the <=

Sorry, this is probably so simple to access wizards.

Thanks
Click to expand...
Click to expand...
 
I don't see how the Got Focus will even work. At that point, the control may
not have the value in it you expect.

Private Sub Quote_Accepted_GotFocus()

If Type_of_Member = "I" Then
Select Case fees
Case Is <= "£50,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£125.00"
Case "£500,000"
Me.PI_Premium = "£135.00"
Case "£1,000,000"
Me.PI_Premium = "£150.00"
End Select
Case "£50,001" To "£75,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£145.00"
Case "£500,000"
Me.PI_Premium = "£160.00"
Case "£1,000,000"
Me.PI_Premium = "£175.00"
End Select
Case Is > "£75,000"
X = MsgBox("This will need to be referred to Saturn")
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "0.00"
Case "£500,000"
Me.PI_Premium = "0.00"
Case "£1,000,000"
Me.PI_Premium = "0.00"
End Select
End Select
End If

If Type_of_Member = "C" Then
Select Case fees
Case Is <= "£50,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£125.00"
Case "£500,000"
Me.PI_Premium = "£135.00"
Case "£1,000,000"
Me.PI_Premium = "£150.00"
End Select
Case "£50,001" To "£100,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£165.00"
Case "£500,000"
Me.PI_Premium = "£180.00"
Case "£1,000,000"
Me.PI_Premium = "£200.00"
End Select
Case "£100,001" To "£250,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£250.00"
Case "£500,000"
Me.PI_Premium = "£270.00"
Case "£1,000,000"
Me.PI_Premium = "£300.00"
End Select
Case Is > "£250,00"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
X = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
Case "£500,000"
X = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
Case "£1,000,000"
X = MsgBox("This will need to be referred to Saturn")
Me.PI_Premium.Value = "0.00"
End Select
End Select
End If

End Sub
 
Very nice Klatuu!

Klatuu said:
I don't see how the Got Focus will even work. At that point, the control
may
not have the value in it you expect.

Private Sub Quote_Accepted_GotFocus()

If Type_of_Member = "I" Then
Select Case fees
Case Is <= "£50,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£125.00"
Case "£500,000"
Me.PI_Premium = "£135.00"
Case "£1,000,000"
Me.PI_Premium = "£150.00"
End Select
Case "£50,001" To "£75,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£145.00"
Case "£500,000"
Me.PI_Premium = "£160.00"
Case "£1,000,000"
Me.PI_Premium = "£175.00"
End Select
Case Is > "£75,000"
X = MsgBox("This will need to be referred to Saturn")
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "0.00"
Case "£500,000"
Me.PI_Premium = "0.00"
Case "£1,000,000"
Me.PI_Premium = "0.00"
End Select
End Select
End If

If Type_of_Member = "C" Then
Select Case fees
Case Is <= "£50,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£125.00"
Case "£500,000"
Me.PI_Premium = "£135.00"
Case "£1,000,000"
Me.PI_Premium = "£150.00"
End Select
Case "£50,001" To "£100,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£165.00"
Case "£500,000"
Me.PI_Premium = "£180.00"
Case "£1,000,000"
Me.PI_Premium = "£200.00"
End Select
Case "£100,001" To "£250,000"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
Me.PI_Premium = "£250.00"
Case "£500,000"
Me.PI_Premium = "£270.00"
Case "£1,000,000"
Me.PI_Premium = "£300.00"
End Select
Case Is > "£250,00"
Select Case Me.PI_Limit_of_Indemnity
Case "£250,000"
X = MsgBox("This will need to be referred to
Saturn")
Me.PI_Premium.Value = "0.00"
Case "£500,000"
X = MsgBox("This will need to be referred to
Saturn")
Me.PI_Premium.Value = "0.00"
Case "£1,000,000"
X = MsgBox("This will need to be referred to
Saturn")
Me.PI_Premium.Value = "0.00"
End Select
End Select
End If

End Sub
Click to expand...
 
Did you see my most recent post? Either break out member type first or put
member type C as your else statement for each if in each Case.


Gillian said:
Hi!

I changed the Type of member and Fees controls to show Me. in front of
them
but the code I just posted still doesn't work when it gets down to
Type_of_Member "C"

Hope you can help.

Thanks.

SusanV said:
Glad to help! The issue here is unless you actually use "between" you
have
to define the comparator both times. Also, you didn't define your
comparator - you need Me.[yourcontrol] rather than just [yourcontrol] - I
should have caught that the first time.

Try either:

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If Me.Type_of_Member = "I" And Me.Fees >= "50001" AND Me.Fees <=
"75000"
Then
Me.PI_Premium.Value = "£145.00"


Or:

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If (Me.Type_of_Member = "I") And (Me.Fees BETWEEN "50001" AND <=
"75000") Then
Me.PI_Premium.Value = "£145.00"

Personally I prefer BETWEEN - easier to find and modify if need be.

SusanV


Gillian said:
Thanks for this, I'm getting somewhere now... Just another question,
how
do
you use the between operator. Here is my next chunk of code but it is
not
recognising it properly:-

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If Type_of_Member = "I" And Fees >= "50001" AND <= "75000" Then
Me.PI_Premium.Value = "£145.00"

A pop up box appears after carriage return which says Expected:
expression
and highlighting the <=

Sorry, this is probably so simple to access wizards.

Thanks

:

You've already declared what your checking in the first line of the
Select
Case statement, so you don't put that on each Case line. See below.

Select Case Me.PI_Limit_of_Indemnity.Value
Case "£250,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£125.00"
End If
Case "£500,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£135.00"
End If
Case "£1,000,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£150.00"
End If
End Select


I noticed you have "If" but no "Else" - are you sure you don't have an
alternate value to enter for each case? Also, you can populate a value
for
an instance where NONE of the defined cases match by adding Case Else
(and
value) after your last defined Case but before End Select:

Case "£1,000,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£150.00"
End If
Case Else
Me.PI_Premium.Value = "£1.00"
End Select
--
hth,
SusanV


Hi,
I am new to using VBA. I am trying to set the value of a field based
on
the
answers to three other fields. Here is what I've got so far....

Private Sub PI_Limit_of_Indemnity_AfterUpdate()
Select Case Me.PI_Limit_of_Indemnity.Value
Case PI_Limit_of_Indemnity = "£250,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£125.00"
End If
Case PI_Limit_of_Indemnity = "£500,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£135.00"
End If
Case PI_Limit_of_Indemnity = "£1,000,000"
If Type_of_Member = "I" And Fees <= "50000" Then
Me.PI_Premium.Value = "£150.00"
End If
End Select

End Sub

There would be lots of additional code to write also based on
different
answers in the Type of member and Fees fields as well but if I've
got
the
basic code right, I can carry on from there.

Many thanks in advance.

Also, this discussion group has been so useful - thanks everyone.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Values not being recognised??? 3
update 1 field from 2 others 3
code wild card value for form variable 4
Obtain the same value of the form in a subform 2
VLOOKUP, a table with text and numerics 7
Fee Grid Calculation 5
Code Help. 1
Help please - Lookup required - not sure! 1

Back
Top