Run-time error 3265: Item not found in this collection

Question

I keep getting this "Run-time error '3265': Item not found in this collection." error message.

The line it occurs on is: If rs!Borings.[Custom Sampling Method] = False Then which is the first instance of a field being referenced, so that makes me wonder if the SQL was added to the string correctly. But I printed the string to the Immediate window, and it says:

SELECT Borings.ProjectID, Borings.BoringID, Borings.HoleDepth, Samples.BoringID,
     Samples.Number, Samples.Depth, Samples.Length, Borings.[Continuous To], 
     Borings.[Every Other], Borings.[Sample Length], 
     Borings.[Custom Sampling Method] 
FROM Borings LEFT JOIN Samples ON Borings.BoringID = Samples.BoringID 
   WHERE Borings.ProjectID = 462

It looks correct to me.. is it wrong? I wonder if the value at the end supposed to be in quotes?

If I instead try to use Set rs = db.OpenRecordset("qryAddSamples"), that method doesn't work. I end up getting a different run-time error '3061' Too few parameters. Expected 1. I don't know what that means.

To give an understanding of what the code is supposed to do, it's supposed to generate soil samples for every soil boring for the current project loaded, based on the sampling info in the Borings Table.

Option Compare Database

Dim db As DAO.Database
Dim rs As DAO.Recordset


Private Sub Update_Samples_Click()

    DoCmd.RunCommand acCmdSaveRecord

    Dim strSQL As String
    Dim sampleDepth As Integer
    Dim sampleNumber As Integer
    
    strSQL = "SELECT Borings.ProjectID, Borings.BoringID, Borings.HoleDepth, Samples.BoringID, " & _
             "Samples.Number, Samples.Depth, Samples.Length, Borings.[Continuous To], " & _
             "Borings.[Every Other], Borings.[Sample Length], Borings.[Custom Sampling Method] " & _
             "FROM Borings LEFT JOIN Samples ON Borings.BoringID = Samples.BoringID " & _
             "WHERE Borings.ProjectID = " & [TempVars]![tmpProjectID]
        
    Debug.Print strSQL
        
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    sampleDepth = 0
    sampleNumber = 1
    
    Do While Not rs.EOF
        
        If rs!Borings.[Custom Sampling Method] = False Then

        Do While sampleDepth + rs!Borings.[Sample Length] <= rs!Borings.[Continuous To]

        rs.AddNew
        AddSamples
        
        sampleNumber = sampleNumber + 1
        sampleDepth = sampleDepth + rs!Samples.[Length]
       
        Loop
        
        Do While sampleDepth + rs!Borings.[Sample Length] <= rs!Borings.HoleDepth
        
        rs.AddNew
        AddSamples
        
        sampleNumber = sampleNumber + 1
        sampleDepth = sampleDepth + rs!Borings.[Every Other]

        Loop
        
        End If

        rs.MoveNext

    Loop
    
    rs.Close
    
    Set rs = Nothing
    Set db = Nothing
    
    DoCmd.Close
    DoCmd.OpenForm "Main"

End Sub

Private Sub AddSamples()
        rs!Samples.[BoringID] = rs!Borings.BoringID
        rs!Samples.[Sample Number] = sampleNumber
        rs!Samples.[Depth] = sampleDepth
        rs!Samples.[Length] = rs!Borings.[Sample Length]
        rs.Update
End Sub

Answer 1

Nevermind, after some more research, I fixed it. Since the bBoringID is an Autonumber in the Borings table, I don't need to enter it into the Samples table. Access will automatically enter the same key value in the Samples table. I removed the rs!sBoringID.Value = rs!bBoringID.Value and I finally see all the values in the other rs!fields.

Answer 2

I'm having trouble assigning the value of bBoringID to the sBoringID field in a recordset. As it's adding new samples to the Samples table, it needs to the take the current bBoringID from the Borings table and assign it to sBoringID in the Samples table. Whenever it gets to the rs!sBoringID.Value = rs!bBoringID.Value line, I get the "Run-time error 3162: You tried to assign the Null value to a variable that is not a Variant data type" error.

However, the values are being printed to the immediate window correctly, except there is a space in front and at the end of the values. But the caption when I mouse over the variables and the watches for the variables all say Null.

Dim db As DAO.Database
Dim rs As DAO.Recordset


Private Sub Update_Samples_Click()

    DoCmd.RunCommand acCmdSaveRecord
    
    Dim strSQL As String
    Dim sampleDepth As Long
    Dim sampleNumber As Long

    strSQL = "SELECT Borings.ProjectID, Borings.BoringID as bBoringID, Borings.HoleDepth, " & _
             "Samples.BoringID as sBoringID, Samples.Number, Samples.Depth, Samples.Length, " & _
             "Borings.[Continuous To], Borings.[Every Other], Borings.[Sample Length], " & _
             "Borings.[Custom Sampling Method] " & _
             "FROM Borings LEFT JOIN Samples ON Borings.BoringID = Samples.BoringID " & _
             "WHERE Borings.ProjectID = " & [TempVars]![tmpProjectID]
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    rs.MoveFirst
    
    Debug.Print strSQL
    Debug.Print rs!ProjectID.Value                  'Borings Table
    Debug.Print rs!bBoringID.Value                  'Borings Table
    Debug.Print rs!HoleDepth.Value                  'Borings Table
    Debug.Print rs![Continuous To].Value            'Borings Table
    Debug.Print rs![Every Other].Value              'Borings Table
    Debug.Print rs![Sample Length].Value            'Borings Table
    Debug.Print rs![Custom Sampling Method].Value   'Borings Table
    Debug.Print rs!sBoringID.Value                  'Samples Table
    Debug.Print rs!Number.Value                     'Samples Table
    Debug.Print rs!Depth.Value                      'Samples Table
    Debug.Print rs!Length.Value                     'Samples Table
    Debug.Print [TempVars]![tmpProjectID]

    sampleDepth = 0
    sampleNumber = 1
    
    Do While Not rs.EOF
        
        If rs![Custom Sampling Method].Value = False Then

        Do While sampleDepth + rs![Sample Length].Value <= rs![Continuous To].Value

                  rs.AddNew
>>this line >>    rs!sBoringID.Value = rs!bBoringID.Value
                  rs![Sample Number].Value = sampleNumber
                  rs!Depth.Value = sampleDepth
                  rs!Length.Value = rs![Sample Length].Value
                  rs.Update

Immediate window output:
SELECT Borings.ProjectID, Borings.BoringID as bBoringID, Borings.HoleDepth, Samples.BoringID as sBoringID, Samples.Number, Samples.Depth, Samples.Length, Borings.[Continuous To], Borings.[Every Other], Borings.[Sample Length], Borings.[Custom Sampling Method] FROM Borings LEFT JOIN Samples ON Borings.BoringID = Samples.BoringID WHERE Borings.ProjectID = 462
 462    'Why is there a space before and after all these integer values?
 848    'Why is there a space before and after all these integer values? 
 60     'Why is there a space before and after all these integer values?
 10     'Why is there a space before and after all these integer values?
 5      'Why is there a space before and after all these integer values?
 2      'Why is there a space before and after all these integer values?
False
Null
Null
Null
Null
462

Answer 3

Try to be more specific by using property Value as rs!SomeField is a field object. Also, leave out the table names where not needed:

    Do While Not rs.EOF
        
        If rs![Custom Sampling Method].Value = False Then

            Do While sampleDepth + rs![Sample Length].Value <= rs![Continuous To].Value

                rs.AddNew
                AddSamples
                
                sampleNumber = sampleNumber + 1
                sampleDepth = sampleDepth + rs!Length.Value
        
            Loop
            
            Do While sampleDepth + rs![Sample Length].Value <= rs!HoleDepth.Value
            
                rs.AddNew
                AddSamples
                
                sampleNumber = sampleNumber + 1
                sampleDepth = sampleDepth + rs![Every Other].Value

            Loop
        
        End If

        rs.MoveNext

    Loop