asked on

Run Time Error 3265 "Item not found in this collection"

I am trying to pass some data from one field to the next in the same table. I have this code that I am using. It has been great for four years. However, today I added a new field.
The new field is listed as #9 Sort (Yes/No)

When I run the code I get a "Run Time Error 3265" Item not found in this collection".
However, when I hit the debugger it goes to "qdTMMK.Parameters(10) = Me.DateCode".

Which has never given me a problem before

Sub CopyMultipleQrevalueTMMK_SelectByTagNumber()
Dim rsTMMK As DAO.Recordset
Dim qdTMMK As QueryDef
Dim reccountTMMK As Long
Dim copycountTMMK As Long
Dim msgStringTMMK As String
 
Set rsTMMK = Me.qQreValueTMMKVEHSubform.Form.RecordsetClone
If Not ((rsTMMK.BOF) And (rsTMMK.EOF)) Then
    rsTMMK.MoveLast
    reccountTMMK = rsTMMK.RecordCount
    Debug.Print reccountTMMK & " records in subform"
    rsTMMK.MoveFirst
     
    For ctr = 1 To rsTMMK.RecordCount
        If rsTMMK.Fields("Select") = True And rsTMMK("tagNumber") <> Me.TagNumber Then
            copycountTMMK = copycountTMMK + 1
            Debug.Print "*** Copying details to SKPI with Tagnumber " & rsTMMK.Fields("tagNumber")
            Set qdTMMK = CurrentDb.QueryDefs("qryUpdateQreValue_TagNumber")
            qdTMMK.Parameters("strTagNumber") = rsTMMK.Fields("tagNumber")
            'Fill in the form-based params
            qdTMMK.Parameters(1) = Me.ProblemDescription
            qdTMMK.Parameters(2) = Me.HowFound
            qdTMMK.Parameters(3) = Me.QREConfirmation
            qdTMMK.Parameters(4) = Me.InterimAction
            qdTMMK.Parameters(5) = Me.QualitAlertNumber
            qdTMMK.Parameters(6) = Me.SortedQuantity
            qdTMMK.Parameters(7) = Me.SortRejects
            qdTMMK.Parameters(8) = Me.[SortCompletion Date]
            qdTMMK.Parameters(9) = Me.[Sort (Yes/No)]
            qdTMMK.Parameters(10) = Me.DateCode
'            For x = 0 To qdTMMK.Parameters.Count - 1
'                Debug.Print x; qdTMMK.Parameters(x).Name & vbTab & vbTab & qdTMMK.Parameters(x).Value
'            Next
            qdTMMK.Execute
            Set qdTMMK = Nothing
        ElseIf rsTMMK("tagNumber") = Me.TagNumber Then
            Debug.Print "Skipping TagNumber" & rsTMMK("tagNumber") & " because it is the record being edited in the main form."
        End If
        rsTMMK.MoveNext
    Next
    msgStringTMMK = "Details were copied to " & copycountTMMK & " records."
Else
    msgStringTMMK = "No records in subform!"
End If
    MsgBox msgStringTMMK, vbOKOnly + vbInformation, "Copy to multiple SKPIs"
Set qdTMMK = Nothing
Set rsTMMK = Nothing
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ggodwin

ASKER

Yes that is the name. Let me try to change it. But why wouldn't the error show on that line?

ggodwin

ASKER

I've done this...
Nothing different.
However, when I removed the #10 line "qdTMMK.Parameters(10) = Me.DateCode" the code seemed to work. However, the data did not populate in the new SortYesNo field.

When the (10) line is left then the original error code returns.

Here is my new code.

Sub CopyMultipleQrevalueTMMK_SelectByTagNumber()
Dim rsTMMK As DAO.Recordset
Dim qdTMMK As QueryDef
Dim reccountTMMK As Long
Dim copycountTMMK As Long
Dim msgStringTMMK As String
 
Set rsTMMK = Me.qQreValueTMMKVEHSubform.Form.RecordsetClone
If Not ((rsTMMK.BOF) And (rsTMMK.EOF)) Then
    rsTMMK.MoveLast
    reccountTMMK = rsTMMK.RecordCount
    Debug.Print reccountTMMK & " records in subform"
    rsTMMK.MoveFirst
     
    For ctr = 1 To rsTMMK.RecordCount
        If rsTMMK.Fields("Select") = True And rsTMMK("tagNumber") <> Me.TagNumber Then
            copycountTMMK = copycountTMMK + 1
            Debug.Print "*** Copying details to SKPI with Tagnumber " & rsTMMK.Fields("tagNumber")
            Set qdTMMK = CurrentDb.QueryDefs("qryUpdateQreValue_TagNumber")
            qdTMMK.Parameters("strTagNumber") = rsTMMK.Fields("tagNumber")
            'Fill in the form-based params
            qdTMMK.Parameters(1) = Me.ProblemDescription
            qdTMMK.Parameters(2) = Me.HowFound
            qdTMMK.Parameters(3) = Me.QREConfirmation
            qdTMMK.Parameters(4) = Me.InterimAction
            qdTMMK.Parameters(5) = Me.QualitAlertNumber
            qdTMMK.Parameters(6) = Me.SortedQuantity
            qdTMMK.Parameters(7) = Me.SortRejects
            qdTMMK.Parameters(8) = Me.[SortCompletion Date]
            qdTMMK.Parameters(9) = Me.SortYesNo
            qdTMMK.Parameters(10) = Me.DateCode
'            For x = 0 To qdTMMK.Parameters.Count - 1
'                Debug.Print x; qdTMMK.Parameters(x).Name & vbTab & vbTab & qdTMMK.Parameters(x).Value
'            Next
            qdTMMK.Execute
            Set qdTMMK = Nothing
        ElseIf rsTMMK("tagNumber") = Me.TagNumber Then
            Debug.Print "Skipping TagNumber" & rsTMMK("tagNumber") & " because it is the record being edited in the main form."
        End If
        rsTMMK.MoveNext
    Next
    msgStringTMMK = "Details were copied to " & copycountTMMK & " records."
Else
    msgStringTMMK = "No records in subform!"
End If
    MsgBox msgStringTMMK, vbOKOnly + vbInformation, "Copy to multiple SKPIs"
Set qdTMMK = Nothing
Set rsTMMK = Nothing
End Sub

Open in new window

ggodwin

ASKER

OK, When I remove that line of code it works.
However, there is one row where the data is populating the wrong fields.

mbizup

I'd have to see your update query to give you any specific help.

however, most collections in Access are zero based, meaning that the first element is numbered ZERO, not 1. There are exceptions to this rule, but if the parameter collection does start at zero (I can't remember offhand), that could explain unexpected fields if you are starting the count at 1 in your code.

Gustav Brock

> However, when I removed the #10 line "qdTMMK.Parameters(10) = Me.DateCode"
> the code seemed to work.

Well, then that field is not present in your recordsource.

/gustav

ggodwin

ASKER

OK...
These errors seem to have resolved them selves.

Now everything is working except the data is not populating in one of the fields.

Should I start and entirely new question? or add it here?

mbizup

Start a new question so that email are sent and the question is seen by others, and delete this one if nothing here helped.

ggodwin

ASKER

The responses did not apply. My database became corrupt and I did not have the same problem later.

ggodwin

ASKER

This was not the exact fix to the problem. But, As I was checking this I came across and error in the update query. Once I made this recommended change in both the code and query the problem was resolved.

Run Time Error 3265 &quot;Item not found in this collection&quot;

Run Time Error 3265 "Item not found in this collection"