Calculate values on the fly?

[Guest]

Looking for the best way to do something. Best can be easiest or best
practice - I'm up for either. Don't need details (not yet - just a
general direction to start.

I have a table that tracks photography orders - one record per order. I
have a related table that has the list of pictures on each order (line item
detail) - one record per picture and size ordered. Pictures are priced based
on size and quantity ordered - order more of a certain size, they are
cheaper. For example - order up to five 4x6 pictures and you pay $5 each.
Order 6 or more and it's down to $4 each.

The question is how to get Access to generate the prices (at the line item
detail level) and an order total (the total should be easy once the line
items are done). My order form is based on the main order table with a
subform based on the photo list table, linked by order ID. I enter the
individual picture orders in the subform. Right now I am manually selecting
the price from a dropdown I created. But I have to know before hand how many
of each size were ordered so I can pick the correct price. I'd like to get
Access to do that work for me.

What's the best way to have the prices automatically calculated? Is there a
way to have Access calculate the price on the fly - updating the records
already entered if I add more? I've thought of a button on the form that
would calculate the prices and totals after all the records are entered,
using a query to get the total count of each size photo ordered and go from
there. Any other ideas?

Thanks!

 

[Jeff L]

I would have a lookup table that has PictureTypeID, MinPics, MaxPics,
Cost. Then when you select your PictureType and Quantity, you would
lookup the cost. I would do the lookup in the AfterUpdate event of the
quantity. You would have something like this:

Me.CostOfPics = Dlookup("Cost", "YourLookupTable", "PictureTypeID = " &
Me.PictureType & " And " & Me.Quantity & " Between MinPics and MaxPics)
* Me.Quantity

Then to get an order total you would have a textbox with
=Sum(Me.CostOfPics) in it.

Your PictureTypeID should refer to another table with the various types
of pics( 5x7, 8x10, Wallets, etc). There would only be 2 fields in
this table, PictureTypeId and PictureType. Use that table as a
dropdown for your orders when selecting PictureType.

Hope that helps!