Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29

    Unanswered: Subform won't update calculated field in main form?

    Hi,

    I've got a Sales Entry Form with a Products Sold subform. I have a calculated field on the main form that sums the prices of the products sold associated with the sale. I would like this field to update every time a product is added, changed, or deleted (via the subform) to reflect the subtotal in real time.

    Any ideas would be appreciated! Thanks!

  2. #2
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    nfw04,

    In you subform I assume you have quantity and sale price so in the query that is the source for your subform you should have something like this;

    ExtPrice: [quantity]*[sellprice]

    This should show in your subform. Now to get the total of the subform to the main form you should put an unbound control in the subform call it txtRecordTotal and in the control source put =Sum([ExtPrice]), set it to visible = false

    Now on your main form put an unbound control box and call it txtsfrmTotal then in the control source put =sfrmSub.Form!txtRecordTotal

    Make sure you change sfrmSub to suit the name of the control that is the subform on you main form.

    If you have any problems post back and I will post you a working sample.
    Regards,



    John A

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding keeping the calculated value updated in the table....

    You could create a function (in a module) to update the totals field in the table. I'll do this often when I need to "break the rules" so to speak and store a totals field in the table for easy querying and other issues (especially accounting type db's).

    You then call the "updateTotals" function from anywhere you need to on your forms.

    Here's a quick ex:

    Function UpdateTotals(RecID as variant)
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTable where RecordID = " & RecID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    if rs.eof and rs.bof then
    rs.close
    set rs = nothing
    else
    rs!TotalsField = rs!FieldOne + rs!FieldTwo.....
    rs.update
    rs.close
    set rs = nothing
    End If
    End Function

    Then I call that function
    Call UpdateTotals(Forms!MyFormName!RecordID)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    Quote Originally Posted by pkstormy
    Regarding keeping the calculated value updated in the table....

    You could create a function (in a module) to update the totals field in the table. I'll do this often when I need to "break the rules" so to speak and store a totals field in the table for easy querying and other issues (especially accounting type db's).

    You then call the "updateTotals" function from anywhere you need to on your forms.

    Here's a quick ex:

    Function UpdateTotals(RecID as variant)
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTable where RecordID = " & RecID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    if rs.eof and rs.bof then
    rs.close
    set rs = nothing
    else
    rs!TotalsField = rs!FieldOne + rs!FieldTwo.....
    rs.update
    rs.close
    set rs = nothing
    End If
    End Function

    Then I call that function
    Call UpdateTotals(Forms!MyFormName!RecordID)
    Great, I think this is what I'm looking for. I'm only a few hours experienced in Visual Basic code, so I'm usually scared of it, but I actually understood that code.

    I think what I may do is create a small table linked to my sale profile table and keep my calculated fields in there for easy querying as you said. I will have to try to get some Visual Basic under my belt.

    When you say "module", could you give a brief explanation as to what that is?

    Thanks!

Posting Permissions

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