Item not found in collection error

[grgimpy]

This code is supposed to take the last three records entered into the query "Ni-Au-CooperSubformQuery" and place them in the table MyTable. After placing the three records in the table it should analyze them.

For some reason I am getting the "item not found in collection" error when I execute this code. It pertains to my line:

If rstSPC.[Ni_Au_Nickel_Thickness_Avg] > SPCAve + 2 * SPCSigma Then TwoOfThreeP = TwoOfThreeP + 1
 

For some reason the field [Ni_Au_Nickel_Thickness_Avg] is not being recognized as part of the recordset I am creating. Any help would be greatly appreciated!

    Dim dbSPC As Object
    Dim rstSPC As Object
    Dim strSQL As String
    Dim DQC As String
    Dim LoopCnt As String
 
If Me.[cboPartNumber] = "156882-001" Then
    SPCAve = DLookup("[Ni-Au-CooperNiAve]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
    SPCSigma = DLookup("[Ni-Au-CooperNiSigma]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
    Warning = "Nickel-Gold Cooper Nickel Thickness Out of Control.  SPC rule: 2 out of 3 consecutive points all appear above two standard deviations from the center line."
    WarningTitle = "Ni/Au Line Cooper Nickel Thickness Out of Control"
    PartQuery = "Ni-Au-CooperSubformQuery"
End If
 
 
'Set up for quote characters & comma to be used in String
DQC = """, """
'run a delete query to clear table
DoCmd.RunSQL "delete mytable.* from MyTable"
''''SET dbSPC EQUAL TO CURRENT DATABASE
    Set dbSPC = CurrentDb
''''OPEN THE QUERY FOR THE SPECIFIC PART NUMBER
    Set rstSPC = dbSPC.OpenRecordset(PartQuery)
'Set your initial value for loop counter
LoopCnt = 1
'''''2 of 3 CONSECUTIVE POINTS ABOVE 2 SIGMA FROM CENTER LINE
    rstSPC.MoveLast
For i = 1 To 3
    If rstSPC.[Ni_Au_Nickel_Thickness_Avg] > SPCAve + 2 * SPCSigma Then TwoOfThreeP = TwoOfThreeP + 1
    strSQL = "insert into MyTable(LoopCount,NickelThicknessAve,Ave,Sigma,TwoofThreeP) select """ & _
           LoopCnt & DQC & rstSPC.[Ni_Au_Nickel_Thickness_Avg] & _
            DQC & SPCAve & DQC & SPCSigma & DQC & TwoOfThreeP & """"
'Last """" is for your last closing quote
 
'execute your SQL insert query constructed in strSQL
DoCmd.RunSQL strSQL
'Increment your loop counter
LoopCnt = LoopCnt + 1
    rstSPC.MovePrevious
Next i
If TwoOfThreeP > 1 Then MsgBox Warning, vbExclamation, WarningTitle
    Me.Point1.Caption = TwoOfThreeP
    DoCmd.GoToRecord , , acLast
 

[sashi]

This code is supposed to take the last three records entered into the query "Ni-Au-CooperSubformQuery" and place them in the table MyTable. After placing the three records in the table it should analyze them.

For some reason I am getting the "item not found in collection" error when I execute this code. It pertains to my line:

If rstSPC.[Ni_Au_Nickel_Thickness_Avg] > SPCAve + 2 * SPCSigma Then TwoOfThreeP = TwoOfThreeP + 1
 

For some reason the field [Ni_Au_Nickel_Thickness_Avg] is not being recognized as part of the recordset I am creating. Any help would be greatly appreciated!

    Dim dbSPC As Object
    Dim rstSPC As Object
    Dim strSQL As String
    Dim DQC As String
    Dim LoopCnt As String
 
If Me.[cboPartNumber] = "156882-001" Then
    SPCAve = DLookup("[Ni-Au-CooperNiAve]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
    SPCSigma = DLookup("[Ni-Au-CooperNiSigma]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
    Warning = "Nickel-Gold Cooper Nickel Thickness Out of Control.  SPC rule: 2 out of 3 consecutive points all appear above two standard deviations from the center line."
    WarningTitle = "Ni/Au Line Cooper Nickel Thickness Out of Control"
    PartQuery = "Ni-Au-CooperSubformQuery"
End If
 
 
'Set up for quote characters & comma to be used in String
DQC = """, """
'run a delete query to clear table
DoCmd.RunSQL "delete mytable.* from MyTable"
''''SET dbSPC EQUAL TO CURRENT DATABASE
    Set dbSPC = CurrentDb
''''OPEN THE QUERY FOR THE SPECIFIC PART NUMBER
    Set rstSPC = dbSPC.OpenRecordset(PartQuery)
'Set your initial value for loop counter
LoopCnt = 1
'''''2 of 3 CONSECUTIVE POINTS ABOVE 2 SIGMA FROM CENTER LINE
    rstSPC.MoveLast
For i = 1 To 3
    If rstSPC.[Ni_Au_Nickel_Thickness_Avg] > SPCAve + 2 * SPCSigma Then TwoOfThreeP = TwoOfThreeP + 1
    strSQL = "insert into MyTable(LoopCount,NickelThicknessAve,Ave,Sigma,TwoofThreeP) select """ & _
           LoopCnt & DQC & rstSPC.[Ni_Au_Nickel_Thickness_Avg] & _
            DQC & SPCAve & DQC & SPCSigma & DQC & TwoOfThreeP & """"
'Last """" is for your last closing quote
 
'execute your SQL insert query constructed in strSQL
DoCmd.RunSQL strSQL
'Increment your loop counter
LoopCnt = LoopCnt + 1
    rstSPC.MovePrevious
Next i
If TwoOfThreeP > 1 Then MsgBox Warning, vbExclamation, WarningTitle
    Me.Point1.Caption = TwoOfThreeP
    DoCmd.GoToRecord , , acLast
 

Hi there,

Error 3265. "Item not found in this collection" generally means the the field name you are referencing in you SQL statement doesn't exist in the table. Check to make sure you spelled the field name correctly.Good luck & Take care.