Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-02-10
11
Medium Priority
?
5,758 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 450 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 52

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

926 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