Using a module

[rpbsr]

Someone here was kind enough to suggest a module for an application I'm
working on involving the calculation of a discount based on household income
and the number of people. I want to develop a form such that the discount
field is filled in when they enter the household income and number in the
household. How do I use the module with the form?

Thanks for your help.

 

[Ken Snell \(MVP\)]

If all you want is to fill in a value, you probably don't need a module.
Just use a textbox on the form to hold the "discount" value, and use an
expression as the Control Source of that textbox, where the expression
returns the discount amount.

You'll need to give us many more details before we can provide more specific
recommendations.

 

[rpbsr]

Hi Ken,

I need to assign discounts based on the number of people in the household and
the income. For example:
80% if 8 people and <$78K
80% if 7 people and <$73K
....80% if 1 person and <$41K

50% if 8 people and <$49K
50% if 7 people and <$46K
....

30% if 8 people and <$29K
....

The VBA module suggested was:

For sake of discussion I assume your table
is tblHouseholds, with fields HouseholdID, NumberOfPeople, Income, and
all three fields required.

select CalcDiscount(NumberOfPeople, Income)
from tblHouseholds

Then in a standard module write:
Public Function CalcDiscount(ByVal NumberOfPeople As Integer, ByVal
Income As Currency) As Single
Dim sngPercent As Single
sngPercent = 0
Select Case NumberOfPeople
Case 1
Select Case Income
Case 0 To 10000
sngPercent = 0.1 '0.1 = 10%
Case 10000 To 25000
sngPercent = 0.2
'etc.
End Select
Case 2
Select Case Income
Case 0 To 11000
sngPercent = 0.11
Case 11000 To 26000
sngPercent = 0.22
'etc.
End Select
'etc.
End Select
CalcDiscount = sngPercent
End Function

 

[Ken Snell \(MVP\)]

May I recommend that you create a table (name it tblDiscounts) that you can
use to store the various scenarios? This would be its structure:

DiscountID (primary key -- autonumber)
DiscountAmt
PeopleNumber
MaxIncome


Populate this table with your data.

Then, create a query based on this table. Name it qryDiscounts:

SELECT DiscountAmt, PeopleNumber, MaxIncome
FROM tblDiscounts
ORDER BY PeopleNumber, MaxIncome;


Then, you can use a DLookup expression for the ControlSource of a textbox on
the form (no module needed):

=DLookup("DiscountAmt", "qryDiscounts", "PeopleNumber = " &
[NumberofPeopleControlOnForm] & " And " & [IncomeControlOnForm] &
"<[MaxIncome]")

 

[rpbsr]

Ken,
Thanks, I'm glad a module won't be necessary for this. One question: for
[NumberOfPeopleControlOnForm] and [IncomeControlOnForm], I'd like these to
come to the form from a tblMembers. Will this work as you recommend? If not,
how would I modify the DLookup expression?

Thanks.

May I recommend that you create a table (name it tblDiscounts) that you can
use to store the various scenarios? This would be its structure:

DiscountID (primary key -- autonumber)
DiscountAmt
PeopleNumber
MaxIncome


Populate this table with your data.

Then, create a query based on this table. Name it qryDiscounts:

SELECT DiscountAmt, PeopleNumber, MaxIncome
FROM tblDiscounts
ORDER BY PeopleNumber, MaxIncome;


Then, you can use a DLookup expression for the ControlSource of a textbox on
the form (no module needed):

=DLookup("DiscountAmt", "qryDiscounts", "PeopleNumber = " &
[NumberofPeopleControlOnForm] & " And " & [IncomeControlOnForm] &
"<[MaxIncome]")

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I need to assign discounts based on the number of people in the household
and
the income. For example:
80% if 8 people and <$78K
80% if 7 people and <$73K
...80% if 1 person and <$41K

50% if 8 people and <$49K
50% if 7 people and <$46K
...

30% if 8 people and <$29K
...

The VBA module suggested was:

For sake of discussion I assume your table
is tblHouseholds, with fields HouseholdID, NumberOfPeople, Income, and
all three fields required.

select CalcDiscount(NumberOfPeople, Income)
from tblHouseholds

Then in a standard module write:
Public Function CalcDiscount(ByVal NumberOfPeople As Integer, ByVal
Income As Currency) As Single
Dim sngPercent As Single
sngPercent = 0
Select Case NumberOfPeople
Case 1
Select Case Income
Case 0 To 10000
sngPercent = 0.1 '0.1 = 10%
Case 10000 To 25000
sngPercent = 0.2
'etc.
End Select
Case 2
Select Case Income
Case 0 To 11000
sngPercent = 0.11
Case 11000 To 26000
sngPercent = 0.22
'etc.
End Select
'etc.
End Select
CalcDiscount = sngPercent
End Function

 

[Ken Snell \(MVP\)]

Are you saying that your form would be based on the table "tblMembers" (or
on a query that is based on that table)? So long as the form's two controls
contain the appropriate values for those controls, the DLookup expression I
provided should be fine.

Note that my NumberofPeopleControlOnForm and IncomeControlOnForm names are
generic names; replace them in the DLookup expression with the actual names
of the corresponding controls on the form,

--

Ken Snell
<MS ACCESS MVP>

Ken,
Thanks, I'm glad a module won't be necessary for this. One question: for
[NumberOfPeopleControlOnForm] and [IncomeControlOnForm], I'd like these to
come to the form from a tblMembers. Will this work as you recommend? If
not,
how would I modify the DLookup expression?

Thanks.

May I recommend that you create a table (name it tblDiscounts) that you
can
use to store the various scenarios? This would be its structure:

DiscountID (primary key -- autonumber)
DiscountAmt
PeopleNumber
MaxIncome


Populate this table with your data.

Then, create a query based on this table. Name it qryDiscounts:

SELECT DiscountAmt, PeopleNumber, MaxIncome
FROM tblDiscounts
ORDER BY PeopleNumber, MaxIncome;


Then, you can use a DLookup expression for the ControlSource of a textbox
on
the form (no module needed):

=DLookup("DiscountAmt", "qryDiscounts", "PeopleNumber = " &
[NumberofPeopleControlOnForm] & " And " & [IncomeControlOnForm] &
"<[MaxIncome]")

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I need to assign discounts based on the number of people in the
household
and
the income. For example:
80% if 8 people and <$78K
80% if 7 people and <$73K
...80% if 1 person and <$41K

50% if 8 people and <$49K
50% if 7 people and <$46K
...

30% if 8 people and <$29K
...

The VBA module suggested was:

For sake of discussion I assume your table
is tblHouseholds, with fields HouseholdID, NumberOfPeople, Income, and
all three fields required.

select CalcDiscount(NumberOfPeople, Income)
from tblHouseholds

Then in a standard module write:
Public Function CalcDiscount(ByVal NumberOfPeople As Integer, ByVal
Income As Currency) As Single
Dim sngPercent As Single
sngPercent = 0
Select Case NumberOfPeople
Case 1
Select Case Income
Case 0 To 10000
sngPercent = 0.1 '0.1 = 10%
Case 10000 To 25000
sngPercent = 0.2
'etc.
End Select
Case 2
Select Case Income
Case 0 To 11000
sngPercent = 0.11
Case 11000 To 26000
sngPercent = 0.22
'etc.
End Select
'etc.
End Select
CalcDiscount = sngPercent
End Function


:

If all you want is to fill in a value, you probably don't need a
module.
Just use a textbox on the form to hold the "discount" value, and use
an
expression as the Control Source of that textbox, where the expression
returns the discount amount.

You'll need to give us many more details before we can provide more
specific
recommendations.

--

Ken Snell
<MS ACCESS MVP>


Someone here was kind enough to suggest a module for an application
I'm
working on involving the calculation of a discount based on
household
income
and the number of people. I want to develop a form such that the
discount
field is filled in when they enter the household income and number
in
the
household. How do I use the module with the form?

Thanks for your help.

 

[rpbsr]

Ken,
This worked out just as I'd hoped. Thanks very much for your help and the
learning experience!

Are you saying that your form would be based on the table "tblMembers" (or
on a query that is based on that table)? So long as the form's two controls
contain the appropriate values for those controls, the DLookup expression I
provided should be fine.

Note that my NumberofPeopleControlOnForm and IncomeControlOnForm names are
generic names; replace them in the DLookup expression with the actual names
of the corresponding controls on the form,

--

Ken Snell
<MS ACCESS MVP>

Ken,
Thanks, I'm glad a module won't be necessary for this. One question: for
[NumberOfPeopleControlOnForm] and [IncomeControlOnForm], I'd like these to
come to the form from a tblMembers. Will this work as you recommend? If
not,
how would I modify the DLookup expression?

Thanks.

May I recommend that you create a table (name it tblDiscounts) that you
can
use to store the various scenarios? This would be its structure:

DiscountID (primary key -- autonumber)
DiscountAmt
PeopleNumber
MaxIncome


Populate this table with your data.

Then, create a query based on this table. Name it qryDiscounts:

SELECT DiscountAmt, PeopleNumber, MaxIncome
FROM tblDiscounts
ORDER BY PeopleNumber, MaxIncome;


Then, you can use a DLookup expression for the ControlSource of a textbox
on
the form (no module needed):

=DLookup("DiscountAmt", "qryDiscounts", "PeopleNumber = " &
[NumberofPeopleControlOnForm] & " And " & [IncomeControlOnForm] &
"<[MaxIncome]")

--

Ken Snell
<MS ACCESS MVP>



Hi Ken,

I need to assign discounts based on the number of people in the
household
and
the income. For example:
80% if 8 people and <$78K
80% if 7 people and <$73K
...80% if 1 person and <$41K

50% if 8 people and <$49K
50% if 7 people and <$46K
...

30% if 8 people and <$29K
...

The VBA module suggested was:

For sake of discussion I assume your table
is tblHouseholds, with fields HouseholdID, NumberOfPeople, Income, and
all three fields required.

select CalcDiscount(NumberOfPeople, Income)
from tblHouseholds

Then in a standard module write:
Public Function CalcDiscount(ByVal NumberOfPeople As Integer, ByVal
Income As Currency) As Single
Dim sngPercent As Single
sngPercent = 0
Select Case NumberOfPeople
Case 1
Select Case Income
Case 0 To 10000
sngPercent = 0.1 '0.1 = 10%
Case 10000 To 25000
sngPercent = 0.2
'etc.
End Select
Case 2
Select Case Income
Case 0 To 11000
sngPercent = 0.11
Case 11000 To 26000
sngPercent = 0.22
'etc.
End Select
'etc.
End Select
CalcDiscount = sngPercent
End Function


:

If all you want is to fill in a value, you probably don't need a
module.
Just use a textbox on the form to hold the "discount" value, and use
an
expression as the Control Source of that textbox, where the expression
returns the discount amount.

You'll need to give us many more details before we can provide more
specific
recommendations.

--

Ken Snell
<MS ACCESS MVP>


Someone here was kind enough to suggest a module for an application
I'm
working on involving the calculation of a discount based on
household
income
and the number of people. I want to develop a form such that the
discount
field is filled in when they enter the household income and number
in
the
household. How do I use the module with the form?

Thanks for your help.

 

[Ken Snell \(MVP\)]

You're welcome. Good luck!