none
How can a user edit a constant in a calculation field? RRS feed

  • Question

  • At the moment I have a calculated field in a query such as-     Total cost: [Cost]*3

    The multiplier (3) is constant in all records. This is displayed in the text box on the form . However, I would like to give the user the ability to change that multiplier or constant via an edit button. In doing so this will also affect all records which is what I want.

    I created a table with a field Name “Multiplier” and Data type “Number”. I added this table to the query and replaced the number 3 with-  Total Cost: [Cost]*[Multiplier]    which works except now when a new record is opened to enter data it contains a previous record instead of opening as New Record.

    Is there a way to force the form to open as new record? I have “Data Entry” as yes for the form in the Property sheet.

    Any recommendations would be appreciated.

    Thanks, Dave

    Friday, November 3, 2017 7:58 PM

Answers


  • A query on both tables which applies the multiplier to each row would not be updatable, as it would return the Cartesian product of the two tables, i.e. the single row in the multiplier table would be joined to every row in the costs table.  A Cartesian product of two tables is achieved by adding both tables to the query, but without explicitly joining the tables. e.g.

    SELECT Costs.*, Multiplier,
    Cost*Multiplier As GrossCost
    FROM Costs, Multipliers;

    An updatable recordset can be returned by omitting the Multipliers table:

    SELECT *,
    FROM Costs;

    However, to return the gross cost and to update the value of the Multiplier column via the form it would necessary to include an unbound text box txtMultiplier in the form, and set its value in the form's Open event procedure with:

    Me. txtMultiplier = DLookup("Multiplier", "Multipliers")

    Then in the AfterUpdate event procedure of the txtMultiplier control execute an UPDATE statement:

    Dim strSQL As String

    strSQL = "UPDATE Multipliers " & _
        "SET Multiplier = " & Nz(Me. txtMultiplier,0)

    CurrentDb.Execute strSQL, dbFailOnError
    Me. txtMultiplier = DLookup("Multiplier", "Multipliers")
    Me.Recalc

    The gross cost can then be shown in a further unbound control with a ControlSource property of:

    =[Cost]*[txtMultiplier]




    Ken Sheridan, Stafford, England

    Friday, November 3, 2017 9:36 PM
  • Hi Dave,

    Pardon me for jumping back in; but if you *must* do the calculation in the query, moving the DLookup() there might help, but it will probably slow down your query. For example:

    SELECT TableName.*, TableName.Cost * Nz(DLookup("Multiplier", "tblMultiplier"),1) As TotalCost

    FROM TableName

    Just my 2 cents...

    Monday, November 6, 2017 7:51 PM

All replies

  • Hi Dave,

    It's hard to imagine what you're seeing. Did you add the Multiplier field to the query, so it can be changed? How did you join the new table with the original ones?

    Friday, November 3, 2017 8:15 PM
  • Hi .theDBguy,

    Yes, I added the field to the query and the calculated field will display the correct value. But now when I open the data form as a new record it doesn't open that way. When I remove the multiplier field and remove it from the calculation field and just use a number for the multiplier then I can open the form as a new record.

    The table relationship is "intermediate". I actually tried it 1st without creating the relationship but the results were the same.

    Hope this helps.

    Friday, November 3, 2017 8:24 PM
  • Hi,

    Thanks for trying to explain but I'm afraid I still can picture what you did. Can you post some screen shots, maybe?

    Friday, November 3, 2017 9:31 PM

  • A query on both tables which applies the multiplier to each row would not be updatable, as it would return the Cartesian product of the two tables, i.e. the single row in the multiplier table would be joined to every row in the costs table.  A Cartesian product of two tables is achieved by adding both tables to the query, but without explicitly joining the tables. e.g.

    SELECT Costs.*, Multiplier,
    Cost*Multiplier As GrossCost
    FROM Costs, Multipliers;

    An updatable recordset can be returned by omitting the Multipliers table:

    SELECT *,
    FROM Costs;

    However, to return the gross cost and to update the value of the Multiplier column via the form it would necessary to include an unbound text box txtMultiplier in the form, and set its value in the form's Open event procedure with:

    Me. txtMultiplier = DLookup("Multiplier", "Multipliers")

    Then in the AfterUpdate event procedure of the txtMultiplier control execute an UPDATE statement:

    Dim strSQL As String

    strSQL = "UPDATE Multipliers " & _
        "SET Multiplier = " & Nz(Me. txtMultiplier,0)

    CurrentDb.Execute strSQL, dbFailOnError
    Me. txtMultiplier = DLookup("Multiplier", "Multipliers")
    Me.Recalc

    The gross cost can then be shown in a further unbound control with a ControlSource property of:

    =[Cost]*[txtMultiplier]




    Ken Sheridan, Stafford, England

    Friday, November 3, 2017 9:36 PM
  • Hi Ken,

    Thank you, I did try this out over the weekend and it did work for the form. However, I need to have this work in the current query that my form looks at since this value is also used in other calculations and also reports. I'm just not sure how to populate that value into the other query's or table. I'm starting to think that maybe this multiplier value will just have to remain as 'set' without the option of changing it by the user. However, I do appreciate the time you have taken to answer my question. Thank you.

    Dave

    Monday, November 6, 2017 4:36 PM
  • Users should never interface with data in a table or query's raw datasheet, but only via forms.  A form set up in the way I described will update the table, so any queries which draw upon the table will reflect the current data as entered via the form, as will any objects such as reports whose RecordSource is a query which draws upon the table.

    Ken Sheridan, Stafford, England

    Monday, November 6, 2017 6:08 PM
  • Hi Dave,

    Pardon me for jumping back in; but if you *must* do the calculation in the query, moving the DLookup() there might help, but it will probably slow down your query. For example:

    SELECT TableName.*, TableName.Cost * Nz(DLookup("Multiplier", "tblMultiplier"),1) As TotalCost

    FROM TableName

    Just my 2 cents...

    Monday, November 6, 2017 7:51 PM
  • Hi .theDBGuy,

    Yes, I did put the DLookup in the query as:      Multiplier: DLookUp("Multiplier","Multiplier_TB")

    The for the calculated field in the same query:     UnPlanned work costs: [Planned Repaired Cost W/O]*[Multiplier]

    Works like a charm. Now my form opens up as a new record. My thanks to the both of you Ken and DBguy! :-)

    Monday, November 6, 2017 9:03 PM
  • Hi Dave,

    Congratulations! Glad to hear you got it sorted out. Ken and I were happy to assist.

    Good luck with your project.

    Monday, November 6, 2017 9:04 PM