Automatically generating text entries from combo box

[Steve]

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

 

[Bob Quintal]

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.

 

[Steve]

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

 

[Bob Quintal]

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

 

[Steve]

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