changing value in record (A2k)

Topic

New Reply

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

Reply | Quote

Replies

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:

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

– Also create an On Load event procedure:

Private Sub Form_Load()
  ' Initialize strNewVal
  strNewVal = DLookup("strOrgName", "tblOrg", "pkeyOrgID = 11448")
End Sub

– 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:

Private strOldVal As String
Private strNewVal As String

You can modify this to suit your needs.

Reply | Quote

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

El

Reply | Quote

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

El

Reply | Quote