Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6296
  • Last Modified:

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

0
ggodwin
Asked:
ggodwin
  • 6
  • 3
1 Solution
 
mbizupCommented:
<< #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
 
ggodwinAuthor Commented:
Yes that is the name. Let me try to change it. But why wouldn't the error show on that line?
0
 
ggodwinAuthor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
ggodwinAuthor Commented:
OK, When I remove that line of code it works.
However, there is one row where the data is populating the wrong fields.
0
 
mbizupCommented:
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
 
Gustav BrockCIOCommented:
> 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
 
ggodwinAuthor Commented:
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
 
mbizupCommented:
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
 
ggodwinAuthor Commented:
The responses did not apply. My database became corrupt and I did not have the same problem later.
0
 
ggodwinAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now