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

Topic

New Reply

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
If Nz(ctl.OldValue) Nz(ctl.Value) Then
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.

Reply | Quote

Replies

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote