ms-access - 带有查询参数的函数中的 OpenRecordSet
问题描述
我正在尝试创建一个返回查询中的行数(记录)的函数。此函数将在 MS Access 宏中使用。本质上:如果 numrecords([QUERY NAME]) > 0,那么做点什么。如果 [QUERY NAME] 没有任何参数,则以下查询效果很好。
Function numrecords(sRs As String) As Long
Dim dbDb As Database
Dim rsRs As Recordset
Set rsRs = dbDb.OpenRecordset(sRs, dbOpenSnapshot)
numrecords = rsRs.RecordCount
我现在有一个表单,允许用户设置日期以子集输出数据;因此,我将参数引入到组合中。足够的谷歌搜索告诉我我需要使用 QueryDefs。以下代码“应该”工作,因为它在 Stack Overflow 上对类似问题有批准的绿色复选标记,但我不断收到错误 3219“无效操作”。错误发生在:Set rsRs = qdf.OpenRecordset(dbOpenSnapshot)
Function numrecords(sRs As String) As Long
Dim dbDb As Database
Dim rsRs As Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set dbDb = CurrentDb
Set qdf = dbDb.QueryDefs(sRs)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
Set rsRs = qdf.OpenRecordset(dbOpenSnapshot) ' ERROR OCCURS HERE!
numrecords = rsRs.RecordCount
最后,我试图在一个查询上运行这个函数,我们称它为查询 2,它是由另一个查询(查询 1)构建的,该查询从表单中获取参数。查询1的SQL是:
PARAMETERS [Forms]![Frm_EIM_Submisions]![Last_Data_Entry_Date] DATETIME;
SELECT SampleID.Matrix
, SampleID.Location
, SampleID.SampleID
, SampleID.DateSampled AS [DATE Sampled]
, LU_Analytes.CASnum
, LU_Analytes.[Analyte code]
, LU_Analytes.Analyte
, LU_Units.[Units code]
, LU_Units.Units
, LU_Methods.[Method code]
, LU_Methods.Method
FROM Location
INNER JOIN (SampleID
INNER JOIN (LU_Units
INNER JOIN (LU_Methods
INNER JOIN (LU_Analytes
INNER JOIN Data ON LU_Analytes.[Analyte code] = Data.[Analyte code]) ON LU_Methods.[Method code] = Data.[Method code]) ON LU_Units.[Units code] = Data.[Units code]) ON SampleID.SampleID = Data.[Sample ID]) ON Location.Location = SampleID.Location
WHERE ((SampleID.DateSampled)>[Forms]![Frm_EIM_Submisions]![Last_Data_Entry_Date])```
Any input would be much appreciated.
解决方案
首先,找出你提供的价值:
For Each prm In qdf.Parameters
Debug.Print prm.Name, Eval(prm.Name)
' prm.Value = Eval(prm.Name)
Next
接下来,尝试手动输入该值:
For Each prm In qdf.Parameters
' Set realistic date value (mm/dd/yyyy):
prm.Value = #04/01/2021#
Next
这应该给你一个提示,要纠正什么。
推荐阅读
- java - 在android studio上制作的应用程序没有在我的an中打开
- javascript - 使用受控状态更新反应表单
- algorithm - 如何设置hashtable的阈值,避免频繁rehash?
- magento - Magento 2 REST API。列出具有父/子关系的产品
- tcp - erlang VM 占用大约 600 个 os/windows 机器线程
- javascript - 简单赋值帮助:调用函数向其传递参数并获取其返回值
- javascript - 异步方法返回未定义
- python - Extracting a numerical value from .csv files
- wordpress - 将自定义属性添加到联系表单本身(wordpress 插件)
- javascript - How to assign the query responses to the global.arrays, not the value inside the array of global.arrays?