Solved

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

Posted on 2014-02-10
11
5,073 Views
Last Modified: 2014-02-11
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

0
Comment
Question by:ggodwin
  • 6
  • 3
11 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 150 total points
ID: 39847972
<< #9 Sort >>

Is this the actual name of your field?

If so, try changing it to Sort9 -- or something that does not include spaces or special characters.

The # sign is bound to cause problems.
0
 

Author Comment

by:ggodwin
ID: 39848032
Yes that is the name. Let me try to change it. But why wouldn't the error show on that line?
0
 

Author Comment

by:ggodwin
ID: 39848313
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

0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:ggodwin
ID: 39848345
OK, When I remove that line of code it works.
However, there is one row where the data is populating the wrong fields.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39848448
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39848731
> 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
0
 

Author Comment

by:ggodwin
ID: 39849143
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?
0
 
LVL 61

Expert Comment

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

Author Comment

by:ggodwin
ID: 39850536
The responses did not apply. My database became corrupt and I did not have the same problem later.
0
 

Author Closing Comment

by:ggodwin
ID: 39852135
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.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now