Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    28

    Unhappy Unanswered: Urgent please, Always get record before updated ?

    Help please, Very urgent.

    I use Access XP + ADO with OLEDB for Jet. I have a form with a subform. Both are bound form. Relation is 1 : M. When each record in subform is updated, I write program in Form_AfterUpdate event to sum amount from all records in this subform. I found that I always get amount value but that value is before record is updated. This is part of my program.

    Private Sub Form_AfterUpdate()
    Dim RS_D as ADODB.Recordset
    Dim mSQL as String
    Dim wkTotalAmt as Variant ' For keep decimal data type.

    mSQL = "select * from OrderDetail where OrderNo = '" & parent!OrderNo & "' "
    RS_D.Open mSQL, mCNN, adOpenKeyset, adReadOnly
    If RS_D.BOF And RS_D.EOF Then GoTo Form_AfterUpdate_Exit
    RS_D.MoveFirst
    Do While Not RS_D.EOF
    wkTotalAmt = wkTotalAmt + CDec(RS_D!Amt)
    RS_D.MoveNext
    Loop

    I think my code is simple. So, Is it possible that something else may cause this problem ?
    Any help so much appreciated.
    Suntisuk

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Here is what I do:

    I have a textbox on the parent form that sums the book values of items listed in the subform ... I set the ControlSource of said text box to:

    TotCostBookTxt.ControlSource = "=DSum('[Cost Book]','" & ItemsTbl & "')"

    Then I merely have to perform a Requery call on the textbox ...

  3. #3
    Join Date
    Nov 2003
    Posts
    28
    Hi, Thanks for your reply.

    I have to explain a little more that after update each record I will use the sum value to calculate tax and write tax value to other table also. Question is By your method, Can Access garantee that sum value in main form will be generated before I execute my Form_AfterUpdate event ?
    and
    Could you please, if you know, why my code doesn't work ?

    Thanks again

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Actually your event does work correctly. What may be happening is that the record in the subform is not getting updated ( moving off the record or clicking on the "pencil" in the row selector) before you perform the after update event. Question: Why are you doing the After Update anyways? That event only fires when the form in question gets the focus back ... Focus of a subform won't activate this event.

  5. #5
    Join Date
    Nov 2003
    Posts
    28
    I not sure your question but that code is of sub-form, not for main-form.

  6. #6
    Join Date
    Nov 2003
    Posts
    28
    Could it about hardware or Windows 2000 Server ? Have any parameters can I set ?

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    One thing I noticed is that your variable wkTotalAmt is local to the event method. Is this value to be used elsewhere?

Posting Permissions

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