Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

How write value of calculated field on a form to the bound table field

I have a form with a calculated field.  When I close the form I want the value of that field to be written to a table field.  The form uses the table as its record source.

The calculated field name is "txtTotOPcost".  The field name in the table I want to write the value to is "TotOPcost".  The table name is "tblMyEstimates"

I realize that it is not advised to capture calculated values but in this case I have to do so,

--Steve
0
SteveL13
Asked:
SteveL13
  • 10
  • 10
  • 2
1 Solution
 
SteveL13Author Commented:
One clarification... I actually have 8 field values to write to the table.  The above is just one of them.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try this -- and it assumes there is only one record in this other table:

Private Sub Form_Close()
    With CurrentDb.OpenRecordset("tblMyEstimates", dbOpenDynaset)
        .Edit
        ![TotOPcost] = Me.txtTotOPcost
        .Update
    End With
End Sub


mx

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Private Sub Form_Close()
    With CurrentDb.OpenRecordset("tblMyEstimates", dbOpenDynaset)
        .Edit
        ![TotOPcost] = Me.txtTotOPcost
       ' repeat for other fields
        .Update
    End With
End Sub
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I *assume* this is NOT the table bound to this Form ?

mx
0
 
GRayLCommented:
Is the record to which you want to write the calculated value already selected by the values in other controls on the form - ie. the current record?  
0
 
SteveL13Author Commented:
This did not work.  Here is what I have with all 8 fields in place.  There is only one record per key field where "txtRealEstN" on the form is equal to "EstN" in the table.

Private Sub Form_Close()

    With CurrentDb.OpenRecordset("tblMyEstimates", dbOpenDynaset)
        .Edit
        ![TotMatlcost] = Me.txtTotStockCost
        ![TotLaborcost] = Me.txtTotalLaborCostAIC2
        ![TotOPcost] = Me.txtTotOPcost
        ![TotEstcost] = Me.txtTotEstCostAIC
        ![VApercent] = Me.txtVAPercent
        ![TotSellPrice] = Me.txtTotSellPrice
        ![TotMinSellPrice] = Me.txtTotMinSellPrice
        ![TotQuotedSellPrice] = Me.txtQuotedSellPrice
         .Update
    End With

End Sub
0
 
SteveL13Author Commented:
Regarding... I *assume* this is NOT the table bound to this Form ?

No, this form IS bound to tblMyEstimates

--Steve

0
 
SteveL13Author Commented:
Regarding:  Is the record to which you want to write the calculated value already selected by the values in other controls on the form - ie. the current record?  

Yes, the record in the table is already selected as the current record.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Private Sub Form_BeforeUpdate (Cancel As Integer)

With Me
        ![TotMatlcost] = .txtTotStockCost
        ![TotLaborcost] = .txtTotalLaborCostAIC2
        ![TotOPcost] = .txtTotOPcost
        ![TotEstcost] = .txtTotEstCostAIC
        ![VApercent] = .txtVAPercent
        ![TotSellPrice] = .txtTotSellPrice
        ![TotMinSellPrice] = .txtTotMinSellPrice
        ![TotQuotedSellPrice] = .txtQuotedSellPrice
End With

End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And ... how are you closing the Form ?

mx
0
 
SteveL13Author Commented:
With a command button.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well ... that code in the BU event should do it

But ... for good measure ... lets say you have this button:

Private Sub btnClose_Click()
    Me.Dirty = False '  force a Save, trigger BU event
    DoCmd.Close acForm, Me.Name
End Sub

mx
0
 
SteveL13Author Commented:
I put...
    With Me
        ![TotMatlcost] = .txtTotStockCost
        ![TotLaborcost] = .txtTotalLaborCostAIC2
        ![TotOPcost] = .txtTotOPcost
        ![TotEstcost] = .txtTotEstCostAIC
        ![VApercent] = .txtVAPercent
        ![TotSellPrice] = .txtTotSellPrice
        ![TotMinSellPrice] = .txtTotMinSellPrice
        ![TotQuotedSellPrice] = .txtQuotedSellPrice
    End With
In the before update event of the form and the form won't even close with the command button.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What was the code you had in this button initially?

mx
0
 
GRayLCommented:
I believe you missed the step:

SET rs  = CurrentDb.OpenRecordset("tblMyEstimates", dbOpenDynaset)

then:

    With Me
        rs![TotMatlcost] = .txtTotStockCost
        rs![TotLaborcost] = .txtTotalLaborCostAIC2
        rs![TotOPcost] = .txtTotOPcost
        rs![TotEstcost] = .txtTotEstCostAIC
        rs![VApercent] = .txtVAPercent
        rs![TotSellPrice] = .txtTotSellPrice
        rs![TotMinSellPrice] = .txtTotMinSellPrice
        rs![TotQuotedSellPrice] = .txtQuotedSellPrice
    End With
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Steve ... Is this Form bound to the actual Table or a query based on the table ... and if a query ... are those fields you want to write to in the query ?

mx
0
 
SteveL13Author Commented:
This form is bound to a table... tblMyEstimates.

Also, adding...
SET rs  = CurrentDb.OpenRecordset("tblMyEstimates", dbOpenDynaset)
did not work.
0
 
SteveL13Author Commented:
I've even tried adding a command button to the form and in the onclick event of the button put...

SET rs  = CurrentDb.OpenRecordset("tblMyEstimates", dbOpenDynaset)
    With Me
        rs![TotMatlcost] = .txtTotStockCost
    End With

(just trying to write one calculated field value to a table field and it doesn't do anything)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Steve ... what I posted @ http:#a35741566 (button) and @ http:#a35741491 (BU code) is all you should need.  So ... as is the case often ... something else is going on here.  There is no need to open a recordset on the same table to do this.

Did you add the Save button or at least the code ,,,  Me.Dirty = False  ... to force a Save and trigger the BU code ?

mx
0
 
SteveL13Author Commented:
I have the button onclick code in place.  But when I add the before update code to the form and click the button, the form won't even close.
0
 
SteveL13Author Commented:
I had a typo in my original code.  All is well.  Thank you!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
cool ...
mx
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 10
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now