Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Combo Box Automation

Discussion in 'Information Technology' started by JenKo, Jul 28, 2006.

  1. JenKo

    JenKo
    Expand Collapse
    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!
     
  2. Loading...


  3. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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" <JenKo@discussions.microsoft.com> wrote in message
    news:F7460AC5-823E-4DFC-9CB2-C3EB354A1857@microsoft.com...
    > 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!
     
  4. JenKo

    JenKo
    Expand Collapse
    Guest

    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" wrote:

    > 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" <JenKo@discussions.microsoft.com> wrote in message
    > news:F7460AC5-823E-4DFC-9CB2-C3EB354A1857@microsoft.com...
    > > 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!

    >
    >
    >
     
  5. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    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" <JenKo@discussions.microsoft.com> wrote in message
    news:4177E291-553C-4B46-8D35-7973E04D3B13@microsoft.com...
    > 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" wrote:
    >
    >> 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" <JenKo@discussions.microsoft.com> wrote in message
    >> news:F7460AC5-823E-4DFC-9CB2-C3EB354A1857@microsoft.com...
    >> > 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!

    >>
    >>
    >>
     

Share This Page