Results 1 to 4 of 4
  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

    Error in tracking changes (run-time error 2424 (2000)

    I have code that I use to track the changes on one of my forms....Now when I make changes, I receive an error:

    "The expression you entered has a field, control, or property name that Microsoft Access can't find."

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim db As DAO.Database
    Dim ctl As Control
    Dim rst As DAO.Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblChanges")
    On Error GoTo Form_BeforeUpdate_Error

    For Each ctl In Me.Controls
    If TypeOf ctl Is Label Then
    'Do nothing
    Else
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">If Nz(ctl.OldValue) <> Nz(ctl.Value) Then</span hi>
    With rst
    rst.AddNew
    rst!Fields("Rank") = RANK
    rst!Fields("LNAME") = LNAME
    rst!Fields("FNAME") = FNAME
    rst!Fields("MI") = MI
    rst!Fields("SSN") = SSN
    rst!Fields("MOS") = MOS
    rst!Fields("COMPANY") = COMPANY
    rst!Fields("OBJECT_CHANGED") = Me.Name
    rst!Fields("PRIOR_VALUE") = ctl.OldValue
    rst!Fields("CURRENT_VALUE") = ctl.Value
    rst!Fields("CHANGED_BY") = GetNetUser()
    rst.Update
    End With
    End If

    Next ctl 'Compile Error: Next without For
    On Error GoTo 0
    rst.Close
    End If
    Exit Sub

    Form_BeforeUpdate_Error:
    If Err.Number = 2427 Then 'this is the error number for something which does not have any data, and therefore does not have an "oldvalue".
    Resume Next
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate"
    End If

    End Sub


    I'm hoping this is fixable in methods other than redesigning/importing to a new database.
    ____________________________
    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
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Error in tracking changes (run-time error 2424 (2000)

    Do you have any other controls (in addition to labels) that do not have values.
    e.g. command buttons, lines, rectangles etc

    You handle labels with the "If TypeOf ctl Is Label Then' line.

    It looks like you handle others with the error handler. But the error handler uses resume next.

    So if you have a control with no value, I think the code will continue with trying to add the recordset.

    You can tell the code to resume somewhere else by labelling a line with an identifier in the first column, then resume at that identifier.


    aa: Next ctl 'Compile Error: Next without For

    resume aa
    Regards
    John



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

    Re: Error in tracking changes (run-time error 2424 (2000)

    Temporarily turn off error handling by commenting out the line On Error GoTo Form_BeforeUpdate_Error.
    When the error occurs, select Debug.
    Activate the Immediate window and type ? ctl.Name then press Enter.
    You wil see the name of the offending control. Does this give you a clue as to why Value or OldValue isn't valid?

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

    Re: Error in tracking changes (run-time error 2424 (2000)

    Hans - worked beautifully - I designed the form based on a table - when I made changes to the table's design, I forgot to change the objects on the form. Thank you.
    ____________________________
    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
  •