Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    56

    Unanswered: Best way to do a Calculation/calculated field

    Ok, New scenario, - My Database is attached as example


    Again ill only discuss the relevant stuff for the problem,

    Ok, i have my Tables:

    StockTrackerTBL
    FurnitureTBL

    and my Form:
    ProgramTestFRM

    Now, it gets the Items, in the lookup box, from FurnitureTBL however the records are stored in StockTrackerTBL,

    I need to do a calculation, the Weight of the item (stored in FurnitureTBL) Multiplied by the Quanity (stored in StockTrackerTBL) to present me with a TotalWeight

    now, i don't know whether its easier and more efficient to use a query, and use SQL to tell the form where its getting the info from, OR whether to build a script in VBA for the form like

    Code:
    Private Sub Qty AfterUpdate()
    
    Me.TotalWeight = Qty * Weight_FurnitureTBL
    
    End Sub
    
    Private Sub Totalweight AfterUpdate()
    
    Call Qty_AfterUpdate 
    
    End Sub
    Which is better? and also, is that code even right cause it didn't seem to work for me, then again i didn't try to manipulate this, im still quite new..

    if so, can you provide me with a small tutorial on whats best, why, and how..

    Thanks alot People, Much appreiacted

    Retarded Jack,
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the form is used to enter or change one or both values (Quantity and Weight), keep the computation at the form level:
    Code:
    Private Sub Quantity_AfterUpdate()
    
        ComputeTotalWeight
    
    End Sub
    
    Private Sub Weight_AfterUpdate()
    
        ComputeTotalWeight
    
    End Sub
    
    Private Sub ComputeTotalWeight()
    
       Me.TotalWeight.Value = Me.Quantity.Value * Me.Weight.Value
    
    End Sub
    Normally TotalWeight should not be stored in any table. It's a derived value that can be computed from two other values.
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    56

    Another Postcode problem (combobox cant handle all of them)

    Ok, so i have about 135,000 postcodes in my database now, in the PostcodesTBL table,

    the reason its in the table and not on a datalink from excel is because when trying the link to excel it instantly made access so slow, and it crashed.. but with the postcodes in the table in access, it seems to handle it fine.. however,

    on my form, i have the combobox that gets the data from postcodesTBL, and stores it in StockTrackerTBL when the record is complete and saved, however, when you click the dropdown box, it can only handle a certain amount in the list, when i type in "SE18" and look for it in the list, it isnt there, and when i enter a full postcode like SE18 7NN, it says its not in the list and can't store that value..

    is there a workaround for this that i'm not thinking about? like having a text box that will relate to the PostcodesTBL and just typing the postcode in the Text box will recognise that SE18 is in the Massive list?

Posting Permissions

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