Results 1 to 3 of 3
  1. #1
    Join Date
    May 2011

    Unanswered: Calculate form field using table

    Hi there,

    I have a form for printing costs. I have fields called PrintSize, Quantity and PrintCost. The PrintSize field is a combo box which looks at the values in another table called Printing. The table called printing has a papersize column and a cost column, e.g.
    A1 - 4.00
    A2 - 3.00 etc.

    I want my PrintCost field to automatically be calculated from the PrintSize & Quantity fields by looking at the Printing table. I tried this:

    Dim SqlStr As String

    SqlStr = "SELECT [Cost] FROM [Printing] WHERE [PaperSize] = '" & PrintSize.Value & "';"*Me.Quantity

    PrintCost.Value = SqlStr

    I dont really understand how to use 'Dim' correctly?

    Any ideas????

  2. #2
    Join Date
    Jan 2009
    Kerala, India

    Calculate form field using table

    Write the following Code in the Printsize ComboBox Click Event Procedure:

    Private Sub cboPrintsize_Click()
    Dim dblCost as Double
    dblCost = Me![Quantity] * Nz(DLookup("PrintCost", "Printing", "Paperize = '" & Me![PrintSize] & "'"), 0)
      Me.PrintCost.Value = dblCost
    End Sub
    cboPrintsize is the name of your Combobox, change it to match the name of your combo box. (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    May 2011
    Hi Thank you for your help!!!

    i tried that and i am getting:

    "Run-time error '2001' you cancelled the previous operation"

    any ideas?


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts