Combo Box Automation

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

Guest

Hello,

I am designing a database in which customers can place orders, and each
order has the potential of generating a discount percentage.

The discount percentages reset every year. A customer's first order is 0%
off, their second, third, fourth and fifth are 5% off, thereafter 10% off
until the end of the year is reached, then we start over again on 1/1 with 0%
for the first order of the year.

I'd like this percentage to be stored as a value in a field for each order.

I could provide a drop down, and have someone manually choose the percentage
based on the above, but I would much prefer to automate this.

Thanks in advance for your help!
 
Hi JenKo

This really has nothing to do with a combo box, except that you are probably
using a combo box to select the customer, right?

After selecting the customer (your combo's AfterUpdate event) you can
calculate the required discount something like this:

Private Sub cboCustomer_AfterUpdate()
Dim lPrevOrders as Long
lPrevOrders = DCount( "*", "[your orders table]", _
"Year([OrderDate])=Year(Date()) And [CustomerID]=" _
& cboCustomer & " and [OrderID]<>" & Me.OrderID)
Select Case lPrevOrders
Case Is >= 5
Me.Discount = 0.10
Case Is >= 1
Me.Discount = 0.05
Case Else
Me.Discount = 0
End Select
End Sub
 
Thank you!

I will most likely show orders as a subform on the customer form, and there
ould be no combo box to select the customer. I was intending to provide a
drop down of discount percentages to choose from when an order record is
entered, but automate the selection. Is it possible with the module below to
allow someone to later select a percent discount that deviates from the rule?
Or is it better to provide either a combo box or an automatically populated
text box?

Graham Mandeno said:
Hi JenKo

This really has nothing to do with a combo box, except that you are probably
using a combo box to select the customer, right?

After selecting the customer (your combo's AfterUpdate event) you can
calculate the required discount something like this:

Private Sub cboCustomer_AfterUpdate()
Dim lPrevOrders as Long
lPrevOrders = DCount( "*", "[your orders table]", _
"Year([OrderDate])=Year(Date()) And [CustomerID]=" _
& cboCustomer & " and [OrderID]<>" & Me.OrderID)
Select Case lPrevOrders
Case Is >= 5
Me.Discount = 0.10
Case Is >= 1
Me.Discount = 0.05
Case Else
Me.Discount = 0
End Select
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JenKo said:
Hello,

I am designing a database in which customers can place orders, and each
order has the potential of generating a discount percentage.

The discount percentages reset every year. A customer's first order is 0%
off, their second, third, fourth and fifth are 5% off, thereafter 10% off
until the end of the year is reached, then we start over again on 1/1 with
0%
for the first order of the year.

I'd like this percentage to be stored as a value in a field for each
order.

I could provide a drop down, and have someone manually choose the
percentage
based on the above, but I would much prefer to automate this.

Thanks in advance for your help!
Click to expand...
Click to expand...
 
In that case, you could put this code in the Form_BeforeInsert event of the
subform, so it is calculated as soon as the user begins to enter a new
record.

It would simply provide an initial value (you could think of it as a
variable default value). The user could then overwrite it with whatever
they like.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JenKo said:
Thank you!

I will most likely show orders as a subform on the customer form, and
there
ould be no combo box to select the customer. I was intending to provide a
drop down of discount percentages to choose from when an order record is
entered, but automate the selection. Is it possible with the module below
to
allow someone to later select a percent discount that deviates from the
rule?
Or is it better to provide either a combo box or an automatically
populated
text box?

Graham Mandeno said:
Hi JenKo

This really has nothing to do with a combo box, except that you are
probably
using a combo box to select the customer, right?

After selecting the customer (your combo's AfterUpdate event) you can
calculate the required discount something like this:

Private Sub cboCustomer_AfterUpdate()
Dim lPrevOrders as Long
lPrevOrders = DCount( "*", "[your orders table]", _
"Year([OrderDate])=Year(Date()) And [CustomerID]=" _
& cboCustomer & " and [OrderID]<>" & Me.OrderID)
Select Case lPrevOrders
Case Is >= 5
Me.Discount = 0.10
Case Is >= 1
Me.Discount = 0.05
Case Else
Me.Discount = 0
End Select
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

JenKo said:
Hello,

I am designing a database in which customers can place orders, and each
order has the potential of generating a discount percentage.

The discount percentages reset every year. A customer's first order is
0%
off, their second, third, fourth and fifth are 5% off, thereafter 10%
off
until the end of the year is reached, then we start over again on 1/1
with
0%
for the first order of the year.

I'd like this percentage to be stored as a value in a field for each
order.

I could provide a drop down, and have someone manually choose the
percentage
based on the above, but I would much prefer to automate this.

Thanks in advance for your help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top