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.

CodePudding user response：

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.

CodePudding user response：

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