Too Few Parameters in Access VBA but works in Quer

问题:

I am using the below SQL, it works fine if I run it from query builder but once I have put it in VBA it throws out an error:

Code:

With CurrentDb.CreateQueryDef("", "SELECT [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field32, [_tbl_Structure].[Supervisor Emp Num], [_tbl_Structure].Supervisor FROM _tbl_Structure RIGHT JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5 WHERE ((([_tbl_Structure].Supervisor)=?));")
            .Parameters(0) = [Forms]![frm_Manager_Stats_NEW]![Text279]  
            Set lvxObj = AvailabilityCap.Object
                Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        End With

Error:

Too few parameters. Expected 1. (Runtime 3061)

Any help on understanding why this works for one but not another is appreciated

回答1:

Form- and report-based parameters are only available in the GUI context (queries run using the GUI, forms, reports, macros and DoCmd.RunSQL). You're probably executing this through CurrentDb, and need to use a querydef instead.

With CurrentDb.CreateQueryDef("", "SELECT [_tbl_Structure].[User Name], tbl_Genesys_Daily.Field32, [_tbl_Structure].[Supervisor Emp Num], [_tbl_Structure].Supervisor FROM _tbl_Structure RIGHT JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5 WHERE ((([_tbl_Structure].Supervisor)=?));")
    .Parameters(0) = [Forms]![frm_Manager_Stats_NEW]![Text279]
    Set rs = .OpenRecordset
End With

You can learn more about the different types of parameters, and when to use which one, in this answer