Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    13

    Unanswered: Multiplying two fields in a form from different tables

    Im new to access so I would appreciate if somene can explain me in detail.
    I have two tables 'LabIssue' and 'Store'.
    I created a form for 'LabIssue' in which data will be entered. In the form I want to calculate the 'ChemicalAmount' by multiplying 'Store.Costperunit' with the 'QuantityIssued' which will be entered by the user. ChemicalAmount and QuantityIssued will be stored in 'LabIssue' table. I tried it through the expression builder but im getting #Name? error, did alot of things but couldnt figured out whats the problem.
    Thankyou

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If Store.Costperunit is part of the rowset of the form (i.e. if the RecordSource property of the form is based on a query that includes both tables LabIssue and Store, you can use:
    Code:
    Me!ChemicalAmount = Me!Costperunit * Me.QuantityIssued.Value
    If Costperunit is not available among the fields in the RecordSet of the form, you have to retrieve it first, using the DLookup function for instance. Something like:
    Code:
    strCriteria = "ChemicalID=" & Me.ChemicalID.Value
    Me!ChemicalAmount = DLookup("Costperunit", "Store", strCriteria) * Me.QuantityIssued.Value
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Posts
    13
    i tried to run the second case but failed i think it is because 'Costperunit' is a column of store table and the form in which transaction will take place is based on 'LabIssue' table...so do i have to link store.costperunit with the LabIssue table.
    please can you tell me in detail how to do this? I am stuck in this...i dont know how to use Dlookup. Help!
    Thankyou

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In this statement:
    Code:
    Me!ChemicalAmount = DLookup("Costperunit", "Store", strCriteria) * Me.QuantityIssued.Value
    the DLookUp expression can be read as "retrieve the value of the column Costperunit from the table Store for one row matching the criteria".

    If Costperunit is the name of a column of the table Store (i.e. if the expression Store.Costperunit is valid) and the whole statement "failed" (you do not specify what happened, which would be the most useful piece of information), it probably means that there is something wrong with the criteria.
    Have a nice day!

  5. #5
    Join Date
    Jun 2011
    Posts
    13
    Here is the initial database, i will be highly grateful if you can have a look and figure out what is the problem?
    Thankyou
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No code can work as there is no code at all in your database.

    Anyway, you do not need to use a DLookUp function to retrieve the value of Store.Costperunit as it is already present in the fourth column of the combobox named Combo297.

    Open the module of the form LabIssue and paste this code into it then compile and save:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Combo297_AfterUpdate()
    
        Me!ChemicalAmount = Me.Combo297.Column(3) * Me.QuantityIssued.Value
        
    End Sub
    Also, I don't understand what you try to achieve when placing:
    Code:
    =[Costperunit]*[QuantityIssued]
    in the AfterUpdate property of the textbox Text303.

    Generally speaking, it would be advisable to rename the objects of the form and give meaningful names to them : It's hard to debug and maintain a database where the objects have names such as Combo297 (could be: Combo_Store) or Text303 (could be: Text_Qty_Issued).
    Have a nice day!

Posting Permissions

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