Hi

I have a table with three columns which contain banded funding rates
eg

Band - Day Rate - Residential Rate
F - 22495 - 42303
G - 32967 - 49069

On the input form there are two combo boxes where the user selects the
band (ie F or G) and whether the day or residential rate applies
(these are coded D or R in the combo box.)

There is a text box on the form for entering the actual funding
amount. Can this be generated automatically based on the input from
the two combo boxes (eg, Band F, Residential, would be 42303.)

If so, how?

Many thanks
Steven Thompson

Steve <(E-Mail Removed)> wrote in
news:86cceb65-214f-44ce-80d5-(E-Mail Removed)
m:

> Hi
>
> I have a table with three columns which contain banded funding
> rates eg
>
> Band - Day Rate - Residential Rate
> F - 22495 - 42303
> G - 32967 - 49069
>
> On the input form there are two combo boxes where the user selects
> the band (ie F or G) and whether the day or residential rate
> applies (these are coded D or R in the combo box.)
>
> There is a text box on the form for entering the actual funding
> amount. Can this be generated automatically based on the input
> from the two combo boxes (eg, Band F, Residential, would be
> 42303.)
>
> If so, how?
>
> Many thanks
> Steven Thompson
>

in the after_update event for each combobox,select the event procedure
option and add the following Visual Basic code:
IF Band = "F" then
if Rate = "D" then
Me.ActualFundingAmt = 22495
else ' rate must be R
Me.ActualFundingAmt = 42303
end if
Else ' Band is G
if Rate = "D" then
Me.ActualFundingAmt = 32967
else
Me.ActualFundingAmt = 49069
End if
End if

Note you may want to provide some preliminary tests to prevent the
calculation if either combobox is null or empty.
--
Bob Q.
PA is y I've altered my address.

That's very helpful Bob, thanks. What if I also have a band H - can I
include an extra ELSE statement to accommodate this within the first
IF? I tried to do this but when I compiled it the error message said
it expected another IF.

Cheers
Steven

Steve <(E-Mail Removed)> wrote in
news:2bc47704-7392-466d-9669-(E-Mail Removed)
m:

> That's very helpful Bob, thanks. What if I also have a band H -
> can I include an extra ELSE statement to accommodate this within
> the first IF? I tried to do this but when I compiled it the error
> message said it expected another IF.
>
> Cheers
> Steven
>
>

To add an extra condition you can use the ElseIF keyword for all
except the last condition.

IF Band = "F" then
if Rate = "D" then
Me.ActualFundingAmt = 22495
else ' rate must be R
Me.ActualFundingAmt = 42303
end if
ElseIf Band = "G"
if Rate = "D" then
Me.ActualFundingAmt = 32967
else
Me.ActualFundingAmt = 49069
End if
ElseIf Band = "H"
if Rate = "D" then
Me.ActualFundingAmt = 32967
else
Me.ActualFundingAmt = 49069
End if
Else ' Band = 'Z'
End if

There is also the Select Case structure, read the help on that. It's
useful if you have many possibilitiesto check in a single field

Select Case band
case 'A'
' do something,
Case 'B'
' do something different
Case else
' handle the unforseen
End Select



--
Bob Q.
PA is y I've altered my address.

The ElseIf/Else option worked fine Bob. I got in a bit of a muddle
matching the Ifs with the End Ifs but once I dealt with that it was
OK.

Thanks again for your help.

Steven