Access 2010 VBA Run-time error '3265' Item not found in this collection

Question

This is a strange one as this code worked fine (some thirty or more runs) Saturday and yesterday but kicks out with "Item not found in this collection." invariably today:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("quyPartRpt")
qdf.Sql = Replace(qdf.Sql, "plugtable", txtVigilTable)
If optGp1 = 1 Then
    qdf.Sql = Replace(qdf.Sql, "WHERE v.[fldUserName] = v.[fld.UserName]", "WHERE v.[fldChurch/Parish] = Forms!frmGpReport1!cmbChurch")
    qdf.Parameters("Forms!frmGpReport1![cmbChurch]") = Forms!frmGpReport1![cmbChurch]
Else
    If IsNull(Forms!frmGpReport1![cmbCouncil]) Then
        qdf.Sql = Replace(qdf.Sql, "WHERE v.[fldUserName] = v.[fld.UserName]", "WHERE v.[fldGroup] = Forms!frmGpReport1![cmbGroup]")
        qdf.Parameters("Forms!frmGpReport1![cmbGroup]") = Forms!frmGpReport1![cmbGroup]
    Else
        qdf.Sql = Replace(qdf.Sql, "WHERE v.[fldUserName] = v.[fld.UserName]", "WHERE v.[fldGroup] = Forms!frmGpReport1![cmbGroup] AND v.[fldCouncil/Court] = Forms!frmGpReport1![cmbCouncil]")
        qdf.Parameters("Forms!frmGpReport1![cmbGroup]") = Forms!frmGpReport1![cmbGroup]
        MsgBox Forms!frmGpReport1![cmbCouncil]
        qdf.Parameters("Forms!frmGpReport1![cmbCouncil]") = Forms!frmGpReport1![cmbCouncil]
    End If
End If

MsgBox qdf.Sql
Set rs = qdf.OpenRecordset

(Truncated as rest is irrelevant and works.)

The line "qdf.Parameters("Forms!frmGpReport1![cmbCouncil]") = Forms!frmGpReport1![cmbCouncil]" is the one that always kicks out the error.

The line immediately above it ("MsgBox Forms!frmGpReport1![cmbCouncil]") gives me the correct value of the contents of cmbCouncil so I cannot for the life of me understand why VBA can't find it when setting up the query.

Any ideas at all would be most appreciated.

(The If,Else is to deal with the fact that some of our organizations -- Knights of Columbus, for example -- are organized into local councils or courts and most are not. I want to be able to report accordingly.)

Thanks.

Answer 1

I didn't quite understand Gustav's answer (still don't) but it put me on the right track.

I didn't want to dim cmbChurch etc. as Text since they are already comboboxes formatted as text; but it did occur to me that it might be more convenient to create variables, set them equal to the comboboxes, and then feed them into the query, thus:

    Dim vChurch As String
    Dim vGroup As String
    Dim vCouncil As String
   . . . . . 
vChurch = IIf((Not IsNull(Me.cmbChurch)),   Me.cmbChurch, "")
vGroup = IIf((Not IsNull(Me.cmbGroup)), Me.cmbGroup, "")
vCouncil = IIf((Not IsNull(Me.cmbCouncil)), Me.cmbCouncil, "")
MsgBox "vChurch = " & vChurch & vbCrLf & "vGroup = " & vGroup & vbCrLf & "vCouncil = " & vCouncil
If optGp1 = 1 Then
    qdf.Sql = Replace(qdf.Sql, "WHERE v.[fldUserName] = v.[fld.UserName]", "WHERE v.[fldChurch/Parish] = Forms!frmGpReport1![cmbChurch]")
    MsgBox qdf.Sql
    qdf.Parameters("Forms!frmGpReport1![cmbChurch]") = vChurch
Else
    If (vCouncil = "") Then
        qdf.Sql = Replace(qdf.Sql, "WHERE v.[fldUserName] = v.[fld.UserName]", "WHERE v.[fldGroup] = Forms!frmGpReport1![cmbGroup]")
        MsgBox qdf.Sql
        qdf.Parameters("Forms!frmGpReport1![cmbGroup]") = vGroup
    Else
        qdf.Sql = Replace(qdf.Sql, "WHERE v.[fldUserName] = v.[fld.UserName]", "WHERE v.[fldGroup] = Forms!frmGpReport1![cmbGroup] AND v.[fldCouncil/Court] = Forms!frmGpReport1![cmbCouncil]")
        MsgBox qdf.Sql
        qdf.Parameters("Forms!frmGpReport1![cmbGroup]") = vGroup
        qdf.Parameters("Forms!frmGpReport1![cmbCouncil]") = vCouncil
    End If
End If

That pretty much did it.

Call me happy camper!

Answer 2

Quite often Access SQL doesn't like anonymous parameters and gets confused. So, you may have to be more explicit, building SQL like this, where you declare the parameters and their data type:

Parameters
    cmbChurch Text,
    cmbGroup Text,
    cmbCouncil Text;
Select 
    ...
From
    PlugTable
Where
    v.[fldChurch/Parish] = cmbChurch
    And
    v.[fldGroup] = cmbGroup
    And
    v.[fldCouncil/Court] = cmbCouncil

And then:

qdf.Parameters("cmbChurch").Value = Forms!frmGpReport1![cmbChurch].Value
qdf.Parameters("cmbGroup").Value = Forms!frmGpReport1![cmbGroup].Value
qdf.Parameters("cmbCouncil").Value = Forms!frmGpReport1![cmbCouncil].Value

Answer 3

1. Show the text of msgbox qdf.sql

2. Since (you believe) the data is correct

   MsgBox Forms!frmGpReport1![cmbCouncil]
   

... it follows that the problem is in the other part of the statement

qdf.Parameters("Forms!frmGpReport1![cmbCouncil]")

Normally, this is because you've used the wrong qdf or parameter.

3. Since (you believe) the qdf hasn't changed since last week ... it follows that the compiled form of the qdf has become corrupted. Delete and re-create the qdf. If that doesn't help, copy the database into a new database.