Help w/form coding

[Kelly P]

I have a form that I am wanting to take the cost of an item and add a markup
percentage to. Percentages are: if less than $5.00 you would multiply the
cost times 100%, less than $30 would be 50%, less than $100 would be
30%...ect. When the cost is put in, the cost plus markup is to be stored in
the price column, I haven't been able to get it to correctly calculate, any
suggestions?

 

[Mike Painter]

I have a form that I am wanting to take the cost of an item and add a
markup percentage to. Percentages are: if less than $5.00 you would
multiply the cost times 100%, less than $30 would be 50%, less than
$100 would be 30%...ect. When the cost is put in, the cost plus
markup is to be stored in the price column, I haven't been able to
get it to correctly calculate, any suggestions?

Select Case YourCost
Case < 100
MarkUp = .3
Case < 30
Markup = .5
Case <5
Markup = 1

Case Else
Whatever Markup for prices greater than or equal to 100.
End Select
Note that the highest values must come first.

SomeValue= YourCost + YourCost*Markup

It is rarly a good idea to store a calcluated value in a field but if it has
to be done an afterUpadte event would set
Me!TheirValue= YourCost + YourCost*Markup

I've left off one section of code.
If CustName = Painter" then
YourCost = 0
End If.

( I always wanted to do this for the billing package I wrote for our service
district.)

 

[Rod Plastow]

Kelly,

There is a dilema: you can calculate the price using nested IIf functions
but as this construct needs to be entered into the Control Source property
then the control becomes unbound and your table is not updated.

I think you need to resort to VBA code as an event-handler for the Before
Update event of your cost control. I will give step-by-step instructions
based on A2007. Earlier versions are almost identical for this exercise. If
you know all this then forgive me for being so tedious.

I assume two controls (at least) called txtCost and txtPrice bound to
columns in the form's underlying recordset (table or query). txtPrice may be
locked and disabled as you don't want your user to alter the value.

Open your form in Design View and click on txtCost to select it. If not
already displayed, click on Property Sheet to open it. Click on the Event tab
to display the list of events for txtCost.

Double click alongside Before Update. This should display [Event Procedure].
Now click on the ellipses (three periods/full stops). This should take you to
the VBA coding window and put you in the correct place.

Type a Select Case construct as follows.

------

Private Sub txtCost_BeforeUpdate(Cancel As Integer)

Select Case Me.txtCost
Case Is < 5
Me.txtPrice = Me.txtCost * 2
Case Is < 30
Me.txtPrice = Me.txtCost * 1.5
Case Is < 100
Me.txtPrice = Me.txtCost * 1.3
End Select

End Sub

------

Close the VBA coding window and you're done. Test your form.

Just a footnote about the Select Case. The logic works down from the first
case to the last until it finds the first true case. The instructions for
that case are executed and then the construct is exited. So the sequence in
which you specify the cases is important.

Rod

 

[Rod Plastow]

Mike,

I believe your Select Case construct is upside down. If the cost is say $2
then the first case (< 100) is true and the cost gets marked up by .3 and not
by 1 as required.

Regards,

Rod

 

[Kelly P]

Perfect! Thank you soooo very much!

 

[Mike Painter]

Mike,

I believe your Select Case construct is upside down. If the cost is
say $2 then the first case (< 100) is true and the cost gets marked
up by .3 and not by 1 as required.

I was standing on my head when I wrote it?
I dictated the reply to my dog and he messes it up?
I was home that night?

The last is one of the funniest things I've ever herd a student blurt out
when the teacher started to ask a question that might have had something to
do with an event that happened in the community.