Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    changing value in record (A2k)

    I need to know when a specific record has the value in one of it's fields changed and could use a shove in the right direction about a good way to do it.

    Specifically I'd like to trigger a message box letting me know that the value in strOrgName for record 11448 in tblOrg has changed from "_None" to "whatever the new value is". Inspiration is a little lacking, so some guidance would be more than welcome.

    E

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

    Re: changing value in record (A2k)

    I assume this is meant for a multi-user database, where another user could change the value. Access does not have triggers at the table level. You could use the On Timer event of a form (which may be hidden) to monitor the field value.

    What do you mean by record 11448? Access does not store records in any particular physical order, so "record 11448" is only meaningful if you specify a sort order, for example on the primary key. It's even better to specify the value of a unique key.

    Here is a possible "recipe". Of course, you must adapt it:

    - Create a new form, or open an existing form in design view.
    - Activate the Event tab of the Properties window.
    - Set the Timer Interval property to the interval between checks, in milliseconds.
    - For example, if you want to check once a minute, set it to 60000 (= 60 * 1000).
    - Don't set the timer interval to less than 1000 (one second), it'll cause problems and harm performance.
    - Create an On Timer event procedure:
    <pre>Private Sub Form_Timer()
    ' Store old value
    strOldVal = strNewVal
    ' Get value
    strNewVal = DLookup("strOrgName", "tblOrg", "pkeyOrgID = 11448")
    ' Compare
    If strNewVal <> strOldVal Then
    ' Inform user
    MsgBox "Value of strOrgName changed from '" & strOldVal & "' to '" & _
    strNewVal & "'.", vbInformation
    ' Optional - turn monitoring off
    Me.TimerInterval = 0
    End If
    End Sub
    </pre>

    - Also create an On Load event procedure:
    <pre>Private Sub Form_Load()
    ' Initialize strNewVal
    strNewVal = DLookup("strOrgName", "tblOrg", "pkeyOrgID = 11448")
    End Sub
    </pre>

    - Finally, declare the two variables at the top of the form module, below Option Compare Database and Option Explicit, but before all Subs and Functions:
    <pre>Private strOldVal As String
    Private strNewVal As String
    </pre>

    You can modify this to suit your needs.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: changing value in record (A2k)

    Your assumption was correct. I never would have thought of using the timer. This works perfectly.

    El

Posting Permissions

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