Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AfterUpdate when moving between form and subform (ACCESS 2003)

    Hi,

    I have a form that AfterUpdate, calculates a status and writes the status away to a history table.

    It worked fine until I added a subform now everytime that I toggle between the form and subform, it writes a new entry to my history table. Is there an event/combination of events that can be recommended to enable me NOT TO WRITE to my history table if only toggling between form and subform?

    Thanking any response in advance
    WILLS

    Private Sub Form_AfterUpdate()

    Dim CalcStatus As String
    Dim CalcStatusValue As Single

    'Calc Temporary Status
    Dim rstA As DAO.Recordset
    Set rstA = CurrentDb().OpenRecordset("select * from stqryExtract where Id = " & Me!ID)
    Call CalculateStatus(rstA, CalcStatus, CalcStatusValue)
    rstA.Close

    'Check and update current status where different on tblInvoice
    Dim rstB As DAO.Recordset
    Set rstB = CurrentDb().OpenRecordset("select Id, status, statusvalue, statuslastchangedt, statuslastchangeuser from tblInvoice where Id = " & Me!ID)
    'Check and add current status to tblInvoiceHistory
    Dim Ident As String ' A pseudo identifier to add to InvoiceHistory
    Ident = Me!Identifier & "-" & Me.Cc & "-" & Me.TypeOfCost
    Dim rstC As DAO.Recordset
    Set rstC = CurrentDb().OpenRecordset("tblInvoiceHistory")
    Call UpdateInvStatus(rstB, rstC, CalcStatus, CalcStatusValue, Ident)
    rstC.Close
    rstB.Close

    ErrorHandlerExit: Exit Sub

    ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AfterUpdate when moving between form and subform (ACCESS 2003)

    When the user moves the focus from the main form to the subform, Access will save the record in the main form if it has been modified. Normally, the record should remain unchanged after that if the user merely moves back and forth between the main form and subform, so you'd get only one record in the history table, unless you have code that modifies the record again.

  3. #3
    New Lounger
    Join Date
    Aug 2004
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterUpdate when moving between form and subform (ACCESS 2003)

    Thanks Hans.

    I am mindful of this. I should have stated in my original post that I use the subform to record low-level detail and act as a pseudo calculator whilst negotiating with a third party. This process means I toggle between form and subform updating both sides as part of a negotiation. I want to write to history when exiting the record containing that negotiation.

    Is there an event/combination of events that can be recommended to enable me NOT TO WRITE to my history table if only toggling between form and subform?

    Thanking any response in advance
    WILLS

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: AfterUpdate when moving between form and subform (ACCESS 2003)

    I don't think so. When the user clicks in the subform, the only form-level events that occur on the main form are the Before Update and After Update events, but these don't "know" that the focus moves. The Deactivate and Lost Focus events of the main form do *not* occur.

    You could hide the navigation buttons on the main form, and use command buttons to move to the previous/next/new record instead, and put the code to write the history record in the code behind these buttons.

Posting Permissions

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