未设置对象变量或With块变量Access vba [英] Object variable or With block variable not set Access vba

问题描述

我正在使用Access中的几个表 CTOL 和 CTOL_Asbuilt .我正在尝试运行一个查询，以使用VBA代码将这两个表连接在一起.我在Access中运行查询，它可以工作.我将DAO用于数据库库以从本地Access数据库中检索数据(代码与数据库位于同一数据库项目中)，并且我是VBA Access脚本的新手.

I'm working with a couple tables, CTOL and CTOL_Asbuilt in Access. I'm trying to run a query to join these two tables together using VBA code. I ran the query in Access and it works. I'm using DAO for the database library to retrieve data from the local Access database (code is in the same database project as the database), and I'm new to VBA Access scripting.

SELECT CTOL.ID, CTOL.BOM_PART_NAME, CTOL.CII, CTOL.[PART FIND NO], CTOL.CSN,
       CTOL.AFS, CTOL.EQP_POS_CD, CTOL.LCN, CTOL.POS_CT, CTOL.SERIAL_NO, 
       CTOL.PART_NO_LLP, [CTOL_Asbuilt].[PART-SN], [CTOL_Asbuilt].[PART-ATA-NO], 
       [CTOL_PW-E750207_Asbuilt].[PW-PART-NO]
FROM CTOL LEFT JOIN [CTOL_Asbuilt] ON CTOL.[PART FIND NO] = [CTOL_Asbuilt].[PART-ATA-NO];

这是下面的代码:

Option Compare Database
Option Explicit

'Const adOpenStatic = 3
'Const adLockOptimistic = 3

Function queryDatabase()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsQuery As DAO.Recordset


Dim part_find_no() As String
Dim eqp_pos() As Integer
'Dim strSQL As String
Dim i As Integer
Dim j As Integer
'Set objConnection = CurrentDb.OpenRecordset("CTOL")

Set db = CurrentDb

Set rsQuery = db.OpenRecordset("SicrProcess", dbOpenDynaset)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function

当我使用调用该函数的宏运行此代码时，出现以下错误:

I'm getting the following error when I run this code with a macro that calls the function:

运行时错误"91":

对象变量或未设置带块变量

Object variable or With block variable not set

我正在尝试将代码与查询一起使用以遍历两个字段，并且当 PART FIND NO 条目与最后一个匹配时，增加 EQP_POS_CD 字段的值(否则，它仅移至下一条记录，直到到达结果集的末尾为止).我想测试运行此查询，以确保代码通过在Access中手动运行查询来检索输出的结果.

I'm trying to use the code with the query to loop through two fields and increment the value of the EQP_POS_CD field when the PART FIND NO entry matches the last (else, it just moves to the next record until it reaches the end of the result set). I want to test-run this query to make sure that the code retrieves the result that is output by running the query manually in Access.

您能帮我解决此错误，以便我运行我的代码来检索数据吗?谢谢！

Can you help me in fixing this error so I can run my code to retrieve the data? Thanks!

推荐答案

rs.Close

您无法关闭未打开的东西.也许您是说它是rsQuery.Close?

You cannot close something that is not open. Perhaps you meant it to be rsQuery.Close?

这篇关于未设置对象变量或With块变量Access vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！