ERROR 3265... and I don't know why

[grinnZ]

Private Sub cboItem_AfterUpdate()

    If Right(Me.Caption, 4) = "ITEM" Then: Exit Sub
    
    If conErrorTrappingOn = True Then On Error GoTo Err_cboItem_AfterUpdate
    
    Dim db          As Database
    Dim rst         As DAO.Recordset
    Dim SQL         As String
    Dim i           As Integer      'loop counter
    
    'NOTE: tblItems refers to CraftedItem / tblItems_1 refers to reagent
    SQL = "SELECT tblCrafts.CraftID, tblCrafts.CraftedIDLookup, tblCrafts.ReagentIDLookup, tblItems_1.InStock, tblCrafts.Qty, tblCrafts.ProfessionIDLookup, tblItems.Item, tblItems_1.Item, tblProfessions.Profession "
    SQL = SQL & "FROM (tblProfessions INNER JOIN (tblItems INNER JOIN tblCrafts ON tblItems.ItemID = tblCrafts.CraftedIDLookup) ON tblProfessions.ProfessionID = tblCrafts.ProfessionIDLookup) INNER JOIN tblItems AS tblItems_1 ON tblCrafts.ReagentIDLookup = tblItems_1.ItemID "
    SQL = SQL & "WHERE tblCrafts.CraftedIDLookup = " & Me.cboItem.Column(0)
    SQL = SQL & ";"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(SQL)
    
    With rst
        If (.BOF And .EOF) Then
            MsgBox "Something is wrong. No reagents found.", vbCritical, "Search Failure"
            GoTo Exit_cboItem_AfterUpdate
        Else
            .MoveFirst
            Do While Not .EOF
                i = i + 1
                Me.Controls("txtReagent0" & CStr(i)).Visible = True
                Me.Controls("txtPerCraft0" & CStr(i)).Visible = True
                Me.Controls("txtExtended0" & CStr(i)).Visible = True
                Me.Controls("txtInStock0" & CStr(i)).Visible = True
                Me.Controls("txtReagent0" & CStr(i)) = ![tblItems_1.Item]
                Me.Controls("txtPerCraft0" & CStr(i)) = Nz(!Qty)
                'THIS IS THE OFFENDING LINE
                'Generates Error 3265 - Item not found in this collection
                Me.Controls("txtInStock0" & CStr(i)) = Nz(![tblItems_1.InStock])
                .MoveNext
            Loop
        End If
    End With

Above you see the code with a note to the offending line. I have no idea why this error is being generated. The field name is valid in the table and the line just two above it works. I don't know why this one does not. Please... a second pair of eyes.

 

[MarkK]

This line works because your SQL has two fields called 'Item' from two different tables. For the recordset to tell them apart it keeps the tablename prefix, in this case 'tblItems_1' in front of the field name.

Me.Controls("txtReagent0" & CStr(i)) = ![tblItems_1.Item]

This line fails because under normal circumstances the recordset does not retain the tablename as part of the name it uses for a field, and since there is only one field called 'InStock' in the SQL, it names the field 'InStock'

Me.Controls("txtInStock0" & CStr(i)) = Nz(![tblItems_1.InStock])

So to correct this fast and dirty you can just do this ...

Me.Controls("txtInStock0" & CStr(i)) = Nz(!InStock])

... but I would go back to the SQL and make sure my Item fields are properly distinguished from each other using As ...

  "SELECT tblItems.Item As Item, tblItems_1.Item As Item_1, ... "

... and then the recordset doesn't have to deal with the ambiguity.
Mark

 

[grinnZ]

And so then once distinguished in the SQL I can then refer to it using the distinguished identifier?

SQL= "SELECT tblItems.Item as Junk...  "

variable=rst!junk

Like that?

 

[MarkK]

Well, that's a bit informal. I might go with ...

SQL= "SELECT tblItems.Item as WasteMaterial...  "

variable=rst!WasteMaterial

... but apart from that I think you've totally got it!
Cheers,
Mark

 

[grinnZ]

Thank you. Very helpful.

Michael