Log Changes not working (Access 2000)

Topic

New Reply

I had some VBA code in a previous database that would track changes of specific fields (i.e. an employee moves to another city, etc…). My previous log would record the employee’s new location, which is what I wanted it to do. Now, when I change the field (it is a combo box), it logs the previous location. What gives?

Reply | Quote

Replies

Check your VBA code. Or post it here – it’s impossible for us to know what’s happening from the information you have given.

Reply | Quote

Sorry , here it is:

Private Sub LOCATION_CHANGE()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset

On Error GoTo ErrHandler

Set cnn = CurrentProject.Connection
rst.Open “tblChanges”, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
rst.AddNew
rst!Object = “Location”
rst!Individual = [LNAME] & “-” & [FNAME] & “-” & [SSN]
rst!COMPANY = [COMPANY]
rst!Change = [LOCATION]
rst!UserName = GetNetUser()
rst.Update

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

Your code should be in the After Update event, not in the On Change event. The On Change event occurs while the user is still typing, so a) you’d get a log record for each character typed, and while the user types, the new value hasn’t been stored in the Location field yet, so rst!Change = [LOCATION] logs the old (stored) value. If you use After Update, rst!Change = [LOCATION] will log the new value.

Reply | Quote

Hm..I wonder why it worked in my old database . Thanks . I started with After_Update, and that’s when I kept getting multiple records for the same change (being typed, etc). If I set the properties of the combo box to limit to list, I guess that would also alleviate some of the problem too, huh.

Reply | Quote