Good Ole nested IIF 1

[Hulm1]

I am stuck on something here and losing hair. I have an unbound text box called "packingfee". I have another text box with a total value in it, let's call it TotalSale.

I have 4 PackingFee prices which are based on values in TotalSale. If the value is <1 PackingFee = 0, if it is between 1 and 1000 then PackingFee is 45, between 1000 and 5000 it is 100 etc.

I have tried Case select, Switch and IIf in the control source of PackingFee. I have also tried it in the afterchange event of TotalSale (where I made PackingFee a bound txtBox to a field in the database). I would prefer to keep it unbound though.

I am not doing very well!

Thanks for any help I might receive!

 

[alvechurchdata]

There shouldn't be a need to make PackingFee into a bound textbox. Something like this in the LostFocus of TotalSale should do it:

Private Sub txtTotalSale_LostFocus()
  Select Case txtTotalSale.Value
    Case Is = 0
      Me.txtPackingFee.Value = 0
    Case Is < 1000
      Me.txtPackingFee.Value = 45
    Case Else
    Me.txtPackingFee.Value = 100
  End Select
End Sub

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[MajP]

Easiest to update would be a simple table.

tblPackingFee
minValue
maxValue
PackingFee

example
-999999 1 0
1 1000 45

in your unbound textbox
=dlookup("PackingFee","tblPackingFee", "minValue < " & txtBxName & " And MaxValue >= " & txtBxName)

 

[alvechurchdata]

Easiest to update would be a simple table.

Good point. I should have stopped to think about the problem behind the question before answering.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[dhookom]

I support MajP's suggestion to use a table. Data belongs in your tables, not in your code.

Duane
Hook'D on Access
MS Access MVP

 

[Hulm1]

Thank you all for your help. I will have a go with tblPakingFee. Meantime can I confirm that LostFocus is the right event? It is also an unbound txt box (a calculated one)

 

[MajP]

If you are going to use a dlookup on the table then, you just make the controlsource of the unbound textbox the dlookup function. You would not need to use an event procedure.

If this is a continous form then that is the method you would want to use. If it is a single form then you could do it in code. You would have to make a subroutine and call that routine from both the form's current event and also the after update of one or more of the controls. That way when you make a change in the record the procedure fires and when you move to a different record it fires.

 

[Hulm1]

Ok brilliant thanks. I'll give it a go