Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Log Changes not working (Access 2000)

    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 <font color=red>new</font color=red> 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?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Log Changes not working (Access 2000)

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

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Log Changes not working (Access 2000)

    Sorry <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>, 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
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Log Changes not working (Access 2000)

    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 [img]/forums/images/smilies/cool.gif[/img] 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.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Log Changes not working (Access 2000)

    Hm..I wonder why it worked in my old database <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>. Thanks <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26>. 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.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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