Software

Let Access calculate field values for you

There's no point in manually calculating a field based on the value in another field. With this simple form trick, you can have Access perform the calculation automatically.
When a field needs to be calculated using data from another field, you don't have to perform the calculation yourself. You can create a form that automatically calculates the field for you. For example, after entering a product discount in a form, you want Access to update the discount price field automatically (Figure A).

Figure A

field update

Follow these steps:

  1. Open the form in Design View.

  1. Right-Click the Discount field.

  1. Click on the Event tab and click the Build button of AfterUpdate property box (Figure B).

Figure B

AfterUpdate property

  1. Enter the following code at the prompt (Figure C):

Private Sub Discount_AfterUpdate()
    [Discount Price] = [UnitPrice] * (1 - [Discount])
End Sub

Figure C

update code

  1. Press Alt + Q.

Now, when a discount is entered in the Discount field, the Discount Price field will automatically update with the new price.


Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

4 comments
Just_Learning
Just_Learning

I really don't know a lot about this, but I'm setting something up in Access and it's pretty well finished with the exception of one thing. I'm trying to get the discount field to auto fill with one of two things. I'm looking to have the discount field auto fill with 7.56% if the unit price is $5.95 AND the quantity is greater than 9. I'm also looking to auto fill the discount field with 15.26% if the unit price is $2.95 AND the quantity is greater than 9. Can anyone help me with what I would have to place into the control source to achieve this? You'd be a life saver if you could!

minkz
minkz

This is a great tip but its doing some funny calculations. First, it won't let me store a number less than 1. Tried formats and masks. Also, the logic takes the discount multiplies the price and then subtracts that number for the discounted price (a negative). Anyone else having this problem? Running Office 97. Ancient I know. Thanks in advance. x

Tink!
Tink!

We're running 2003 but the VB language is basically the same still. For our costing of materials we take the Raw material cost (RMC) - add $.05 if it's a material we buy or $0.00 if it's an internal material we make (there is a checkbox to click if it's Internal - that's where the -1 value bewlo come from) - add Loss cost, which is 3% of the RMC - add Handling cost, which is 2% of the RMC Add all of that together and we get an In-formula cost. It's a tiny bit more complicated than the discussion example but this is how I run it (let me know if you need any help deciphering it) :) : [b](assign variables to all the field values you are going to work with) I have this run in the On Exit event for the RMC field.[/b] Private Sub rmc_Exit(Cancel As Integer) On Error GoTo Err_rmc_Exit Dim rmcvalue Dim handlingvalue Dim lossvalue Dim pricevalue Dim freightvalue Dim freightchoosevalue ' Check Freightin Value freightchoosevalue = Me!freightchoose.Value If freightchoose = "-1" Then GoTo Internal 'for Purchased materials Freight in = .05 freightvalue = 0.05 Me!freightin.Value = freightvalue GoTo rmc_Exit_Start Internal: freightvalue = 0 Me!freightin.Value = freightvalue rmc_Exit_Start: ' Change Handling value based on RMC Me!handling.SetFocus rmcvalue = Me!rmc.Value handlingvalue = rmcvalue * 0.02 Me!handling = handlingvalue ' Change Loss value based on RMC Me!Loss.SetFocus lossvalue = rmcvalue * 0.03 Me!Loss = lossvalue ' Add all to make Price Me!cost.SetFocus freightvalue = Me!freightin pricevalue = rmcvalue + freightvalue + handlingvalue + lossvalue Me!cost = pricevalue

Editor's Picks