Item Not Found in this Collection Error (1 Viewer)

[Sousuke81]

Good day to everyone here. I have a VBA code that is written for my access database to track changes made in the system and record them digitally. In practice with the test database records, it worked perfectly and thus put the code to work in the live version with editing everything for the VBA to work. Now when it tried to be used it gives an Item Not Found in this Collection error that I've never seen before in testing. Below is the code for the VBA and any suggestions or help will be greatly appreciated.

Option Compare Database
Sub AuditChanges(IDField As String)
    On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM Corrections", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    For Each ctl In Screen.ActiveForm.Controls
        If ctl.Tag = "Audit" Then
            If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                With rst
                    .AddNew
                    ![DateTime] = Now()
                    ![LotNumber] = Screen.ActiveForm.Recordset.Fields("Lot Number (date prepared)").Value
                    ![ReagentName] = Screen.ActiveForm.Recordset.Fields("Reagent Name:").Value
                    ![RecordID] = [Forms]![Login Form]![Initials]
                    ![FieldName] = ctl.ControlSource
                    ![OldValue] = ctl.OldValue
                    ![NewValue] = ctl.Value
                    .Update
                End With
            End If
        End If
    Next ctl
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

 

[pbaldy]

Could be a field name spelled wrong, or perhaps a control with the tag that shouldn't. Temporarily comment out the On Error line and run the code, so you can see what line is failing.

 

[Sousuke81]

I found the error. It was not finding something within the Query I had for the form. Once I added what it should have been looking for, it then worked properly

 

[pbaldy]

Glad you found it. FYI, that will slow down if the table gets large, because you're opening the recordset on the whole table. Either open it for append only or like:

"SELECT * FROM Corrections WHERE 1=0"