If then code in a combo box 2

[aliceapraham]

I have a Table that has a Product field & 2 Price fields. Price1 & Price2. In the form I have Qty field, a combobox which is the 'Product' field & 'Price' field, I want to code, either the product filed or the price field, that when i put a Qty from 1 to 10000 & when i choose the product, in the price field pops up the Price1, & if the Qty is <10001 and or >25000 the Price2 field pops up. How do i do that?
Thank you
Alice

 

[Remou]

Have a look at this:

Calculate correct cost from sliding scale of prices
thread702-1362575

 

[TheAceMan1]

How are ya aliceapraham . . .

Although as a programmer I can't help but agree with [blue]Remou[/blue], I'm betting managers & users will find your current interface more frieldly.

So if in your table you indeed have [blue]Price1, Price2, Price[/blue] . . . then on the form you can remove [blue]Price1 & Price2[/blue]. Just make sure they exist in the [blue]forms field list[/blue] for access.

In the code module of the form, copy/paste the following routine:

[blue]Public Sub PriceGrabber()

   If Trim(Me!Product & "") <> "" And Trim(Me!Qty & "") <> "" Then
      If Me!Qty > 0 And Me!Qty < 10001 Then
         Me!Price = Me!Price1
      ElseIf Me!Qty > 10000 And Me!Qty < 25001 Then
         Me!Price = Me!Price2
      Else
         [green][b]'Price setup for quantity > 25000[/b][/green]
      End If
      
      [green][b]'Me!SubTotal = Me!Qty * Me!Price[/b][/green]
   End If
       
End Sub[/blue]

Then in the [blue]AfterUpdate[/blue] event of both [blue]Product[/blue] and [blue]Qty[/blue]:

[blue]Call PriceGrabber[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Remou]

What happens when the cost ranges change in response to market pressure?

 

[TheAceMan1]

Howdy [blue]Remou[/blue] . . .

Its my experience that quantity ranges are pretty much set. Its the pricing I've seen change in abundance (enerybody goin up!).

Again . . . [blue]I fully perfer your method[/blue] and have used it several times. I'm just going on experience with user contact. Its like users of excel trying to make a db the same thing! I've had a hard time in the past trying to get management of certain firms to accept the schema you've posted and even those that accepted, they've never liked it. I can only hope they've at least gotton use to it . . . by now.

Of course if they add ranges they'll have to add fields and extend the IF-Then-Else statement. But I've yet had to do so.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[aliceapraham]

I'm so gratefull to u guys, thank you so much.that code worked, but now the problem is that i need the user to have couple of lines to pick in every line a product. so when I'm copy & pasting the Qty, Product & price & total inthe form it's not doing it, why?, I'm sure it has to have aother names, not the same name in the property box, & I tried that, but something in the code is not working. I don't know anything about VB, so pls help.
Thank you again for your help.
Alice

 

[aliceapraham]

Maybe my question was not that clear (I know).
The user have to select couple of products (different products)& each according to thier Unit price, & each has their total, then at the end there is a Grand total for all of them. how can i make couple o flines with the same names. It's like they are buying 2 shirts & 2 shoes....etc.
Pls Help again. thanks a lot
Alice

 

[TheAceMan1]

aliceapraham . . .

I'm here! . . . be more specific about:

[blue] . . . but now the problem is that i need the user to have couple of lines to pick in every line a product. so when I'm copy & pasting the Qty, Product & price & total inthe form it's not doing it, why? . . .[/blue]

[blue]I await! . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[aliceapraham]

thank u for beeing here, ok, i had couple of field
Qty, Product, Unit Price & Total, which is working perfectly. Now I need to make couple of rows, so that the user selects multiple products, for each row a product, & as the first row I want the secnd row to work the same. tell me if u didn't get my question. thanks alot.
Qty Product Unit Price Total
couple of rows like that.

 

[TheAceMan1]

aliceapraham . . .

Sorry still not getting what you mean. Start a new post so others in this fora can have a peak . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[aliceapraham]

Ok thanks, I posted in a new one called Multiple line of products. check it. thanks again
Alice