Access VBA中的参数太少，但可在“查询"构建器中使用 [英] Too Few Parameters in Access VBA but works in Query builder

问题描述

我正在使用下面的SQL，如果我从查询生成器中运行它，则可以正常工作，但是一旦将其放在VBA中，它就会抛出错误:

代码:

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

错误:

参数太少.预期为1.(运行时3061)

任何帮助您理解为什么它对一个人有用但对另一个人不起作用的帮助

解决方案

基于表单和报表的参数仅在GUI上下文中可用(使用GUI，表单，报表，宏和DoCmd.RunSQL运行查询).您可能正在通过CurrentDb执行此操作，而需要使用querydef.

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

您可以在此答案中了解有关不同类型的参数以及何时使用哪个参数的更多信息. >

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

解决方案

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

这篇关于Access VBA中的参数太少，但可在“查询"构建器中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！