Function to return a Commision Percent based on input from form

[Guest]

I am new to VBA and I am trying to write a function for a Commission form.

The form has 9 controls. 8 are a combination of combo boxes and dollars
amounts. The user input determines what Commission % should be used to
calculate the commission. I am trying to build a function that will
generate the commission rate based on the user input. Do I have to have a
commission rate table or can I do a series of If..Then...Else statements in a
function using say a variable dCommRate. and give dCommRate a value based on
the other parameters chosen in the If then... I tryed a Select ...Case but
that seems to only work in a sub procedure? and it is my understanding that
sub procedures cannot return values. After the dCommRate variable is
determined I then need to multiply the dCommRate by the Sale and populate the
information in the underlying data table.

Is this doable?


Thanks

 

[Carl Rapson]

I am new to VBA and I am trying to write a function for a Commission form.

The form has 9 controls. 8 are a combination of combo boxes and dollars
amounts. The user input determines what Commission % should be used to
calculate the commission. I am trying to build a function that will
generate the commission rate based on the user input. Do I have to have a
commission rate table or can I do a series of If..Then...Else statements
in a
function using say a variable dCommRate. and give dCommRate a value based
on
the other parameters chosen in the If then... I tryed a Select ...Case but
that seems to only work in a sub procedure? and it is my understanding
that
sub procedures cannot return values. After the dCommRate variable is
determined I then need to multiply the dCommRate by the Sale and populate
the
information in the underlying data table.

Is this doable?


Thanks

Select Case should work anywhere you can write VBA code.

Sub procedures can't return values, but Function procedures can. If you
define your procedure like this:

Function MyProcedure(parameters) As Currency

then you can return a value from this function like this:

MyProcedure = somevalue

right before the End Function statement.

Carl Rapson

 

[Graham Mandeno]

There is no difference between a Sub and a Function, except that a Function
can (it doesn't have to) return a result and a Sub cannot. (Functions can
also be called from event properties and Subs cannot).

With regard to your question about having a commission rate table versus
hard-coding the rates in VBA, I would definitely go for a table unless the
rates are fixed and unlikely ever to change. It's much easier to change
data that to change code.

 

[Guest]

Here is what I have so far. Doesn't seem to work? All of the dims are the
controls that the end user enters and therefore must be included to filter to
the specifc rate.

Public Function GetComm() As Currency

Dim currComm As Currency
Dim strProdType As String
Dim strProdPre As String
Dim strAge As String
Dim strPayType As String
Dim intCDSC As Integer
Dim strOption As String

strProdType = Me![txtProdType]
strProdPre = Me![CmboProdPrefix]
strAge = Me![cmbAge]
strPayType = Me![cmbPayType]
intCDSC = Me![cmbCDSC]
strOption = Me![cmbOption]
currComm = Me![txtCommissionPercent]


Select Case strProdPre
'Commission Rate for Product
Case Is = "Pro" Or "POD" Or "POT"
currComm = 0.00.51
End Select
GetComm = currComm
End Function

Any help would be appreciated. thanks

There is no difference between a Sub and a Function, except that a Function
can (it doesn't have to) return a result and a Sub cannot. (Functions can
also be called from event properties and Subs cannot).

With regard to your question about having a commission rate table versus
hard-coding the rates in VBA, I would definitely go for a table unless the
rates are fixed and unlikely ever to change. It's much easier to change
data that to change code.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am new to VBA and I am trying to write a function for a Commission form.

The form has 9 controls. 8 are a combination of combo boxes and dollars
amounts. The user input determines what Commission % should be used to
calculate the commission. I am trying to build a function that will
generate the commission rate based on the user input. Do I have to have a
commission rate table or can I do a series of If..Then...Else statements
in a
function using say a variable dCommRate. and give dCommRate a value based
on
the other parameters chosen in the If then... I tryed a Select ...Case but
that seems to only work in a sub procedure? and it is my understanding
that
sub procedures cannot return values. After the dCommRate variable is
determined I then need to multiply the dCommRate by the Sale and populate
the
information in the underlying data table.

Is this doable?


Thanks

 

[LillianH via AccessMonster.com]

There is no difference between a Sub and a Function, except that a Function
can (it doesn't have to) return a result and a Sub cannot. (Functions can
also be called from event properties and Subs cannot).

With regard to your question about having a commission rate table versus
hard-coding the rates in VBA, I would definitely go for a table unless the
rates are fixed and unlikely ever to change. It's much easier to change
data that to change code.

I am new to VBA and I am trying to write a function for a Commission form.

[quoted text clipped - 17 lines]

Thanks

Here is what I have so far. Doesn't seem to work? All of the dims are the
controls that the end user enters and therefore must be included to filter to

the specifc rate.

Public Function GetComm() As Currency

Dim currComm As Currency
Dim strProdType As String
Dim strProdPre As String
Dim strAge As String
Dim strPayType As String
Dim intCDSC As Integer
Dim strOption As String

strProdType = Me![txtProdType]
strProdPre = Me![CmboProdPrefix]
strAge = Me![cmbAge]
strPayType = Me![cmbPayType]
intCDSC = Me![cmbCDSC]
strOption = Me![cmbOption]
currComm = Me![txtCommissionPercent]


Select Case strProdPre
'Commission Rate for Product
Case Is = "Pro" Or "POD" Or "POT"
currComm = 0.00.51
End Select
GetComm = currComm
End Function

Any help would be appreciated. thanks

 

[Graham Mandeno]

Hi Lillian (?)

What do you mean: "Doesn't seem to work"?

The line:
currComm = 0.00.51
is not even valid syntax, because 0.00.51 is not a valid number, so the code
editor should turn that line red and not allow you to move off it without
displaying an error message.

Also,
Case Is = "Pro" Or "POD" Or "POT"
is not valid.

It should be:
Case "Pro", "POD", "POT"

You don't actually need to Dim local variables to refer to controls on your
form. It is perfectly acceptable to say:
Select Case Me!cmbProdPrefix

And, as I said in my last post, are you SURE you want to hard-code the rates
in your VBA code? I think you will be making problems that will come back
to bite you later when someone wants to change a rate or add a new category.
I suggest you put the rates in a reference table and have your function look
up the appropriate rate from the table.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here is what I have so far. Doesn't seem to work? All of the dims are
the
controls that the end user enters and therefore must be included to filter
to
the specifc rate.

Public Function GetComm() As Currency

Dim currComm As Currency
Dim strProdType As String
Dim strProdPre As String
Dim strAge As String
Dim strPayType As String
Dim intCDSC As Integer
Dim strOption As String

strProdType = Me![txtProdType]
strProdPre = Me![CmboProdPrefix]
strAge = Me![cmbAge]
strPayType = Me![cmbPayType]
intCDSC = Me![cmbCDSC]
strOption = Me![cmbOption]
currComm = Me![txtCommissionPercent]


Select Case strProdPre
'Commission Rate for Product
Case Is = "Pro" Or "POD" Or "POT"
currComm = 0.00.51
End Select
GetComm = currComm
End Function

Any help would be appreciated. thanks

There is no difference between a Sub and a Function, except that a
Function
can (it doesn't have to) return a result and a Sub cannot. (Functions
can
also be called from event properties and Subs cannot).

With regard to your question about having a commission rate table versus
hard-coding the rates in VBA, I would definitely go for a table unless
the
rates are fixed and unlikely ever to change. It's much easier to change
data that to change code.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am new to VBA and I am trying to write a function for a Commission
form.

The form has 9 controls. 8 are a combination of combo boxes and
dollars
amounts. The user input determines what Commission % should be used to
calculate the commission. I am trying to build a function that will
generate the commission rate based on the user input. Do I have to
have a
commission rate table or can I do a series of If..Then...Else
statements
in a
function using say a variable dCommRate. and give dCommRate a value
based
on
the other parameters chosen in the If then... I tryed a Select ...Case
but
that seems to only work in a sub procedure? and it is my understanding
that
sub procedures cannot return values. After the dCommRate variable is
determined I then need to multiply the dCommRate by the Sale and
populate
the
information in the underlying data table.

Is this doable?


Thanks

 

[LillianH via AccessMonster.com]

Hi Lillian (?)

What do you mean: "Doesn't seem to work"?

The line:
currComm = 0.00.51
is not even valid syntax, because 0.00.51 is not a valid number, so the code
editor should turn that line red and not allow you to move off it without
displaying an error message.

Also,
Case Is = "Pro" Or "POD" Or "POT"
is not valid.

It should be:
Case "Pro", "POD", "POT"

You don't actually need to Dim local variables to refer to controls on your
form. It is perfectly acceptable to say:
Select Case Me!cmbProdPrefix

And, as I said in my last post, are you SURE you want to hard-code the rates
in your VBA code? I think you will be making problems that will come back
to bite you later when someone wants to change a rate or add a new category.
I suggest you put the rates in a reference table and have your function look
up the appropriate rate from the table.

Here is what I have so far. Doesn't seem to work? All of the dims are
the

[quoted text clipped - 69 lines]

I made the changes as you suggest. Still no go. But I am soooooo close I
can taste it....

The function now reads

Public Function GetComm() As Currency

Dim currComm As Currency
Dim strProdType As String
Dim strProdPre As String
Dim strAge As String
Dim strPayType As String
Dim intCDSC As Integer
Dim strOption As String

Select Case strProdPre
'Commission Rate for Product
Case Is = "PRO" Or "POD" Or "POT"
currComm = 0.051
End Select
GetComm = currComm
End Function



I call the function by placing GetComm() in the Default value of the property
sheet in the control - [txtCommPercent]. That is where I want the currComm
to appear.

Please note: Everytime I type in -
Case "Pro", "POD", "POT"

it automatically changes to:
Case Is = "PRO" Or "POD" Or "POT"


Thanks for all your help. Even though it doesn't quite work yet... I now I
am on the right track.

Thanks AGAIN

 

[Graham Mandeno]

Answers inline ...

I made the changes as you suggest. Still no go. But I am soooooo close I
can taste it....

The function now reads

Public Function GetComm() As Currency

Dim currComm As Currency
Dim strProdType As String
Dim strProdPre As String
Dim strAge As String
Dim strPayType As String
Dim intCDSC As Integer
Dim strOption As String

Select Case strProdPre

You have not assigned any value to strProdPre, so it will be an empty
string.
I think it should be:
Select Case Me!cmbProdPrefix

'Commission Rate for Product
Case Is = "PRO" Or "POD" Or "POT"

This is wrong! (see below)

currComm = 0.051
End Select
GetComm = currComm
End Function



I call the function by placing GetComm() in the Default value of the
property
sheet in the control - [txtCommPercent]. That is where I want the
currComm
to appear.

I don't think this will work. DefaultValue is used only at the moment a new
record is created, and at this stage the other controlling variables (e.g.
your product prefix combo) have not been filled in.

I think you should change this line above:
GetComm = currComm
to
txtCommPercent = CurrComm

Then, for each of the textboxes/comboboxes that can affect the commission,
set the AfterUpdate property to:
=GetComm()

Please note: Everytime I type in -
Case "Pro", "POD", "POT"

it automatically changes to:
Case Is = "PRO" Or "POD" Or "POT"

This certainly does not happen for me, and when I do try it in the second
form I get:
Run-time error 13: Type mismatch

This is to be expected, because you are performing a boolean operation (Or)
on a bunch of strings.