Vba访问错误91 [英] Vba Access error 91
问题描述
我尝试运行此代码
Public Sub Production_UpdateStatus(ByVal lngProductionId As Long, _
ByVal NewProductionStatus As eProductionStatus)
Dim oDb As DAO.Database
Dim oRst As DAO.Recordset
Dim StrSql As String
Dim strProductionStatus As String
On Error GoTo Err_Infos
GetCurrentProductionStatusString NewProductionStatus, strProductionStatus
Set oDb = CurrentDb
'Mise a jour du staut de production
StrSql = "UPDATE tProduction SET tProduction.Statut= '" & strProductionStatus & "'" _
& " WHERE (tProduction.IdProduction=" & lngProductionId & ");"
oDb.Execute StrSql
'Fermeture des connexions
oRst.Close
oDb.Close
Set oDb = Nothing
Set oRst = Nothing
Exit_currentSub:
Exit Sub
Err_Infos:
MsgBox "Erreur #" & Err.Number & " : " & Err.Description
Resume Exit_currentSub
End Sub
此代码有效,但给我错误91.
This code work but give me error 91.
对象变量或未设置带块变量
Object variable or With block variable not set
它将生成以下SQL查询:
It generate the following SQL query :
UPDATE tProduction SET tProduction.Statut= 'Nouvelle' WHERE (tProduction.IdProduction=2);
当我测试直接查询时,我没有任何错误.您能帮我消除这个错误吗?
When I test direct query, I do not have any error. Could you help me to eliminate this error ?
谢谢
推荐答案
您正在关闭从未用Set
初始化的记录集对象oRst
.因为您运行操作查询,所以不需要记录集,并且它可能早于以前的代码版本就存在.
You are closing a recordset object, oRst
, that was never initialized with Set
. Because you run an action query you do not need a recordset and it may have lingered from prior code versions.
同样,由于您要将文字值传递给SQL查询,因此请考虑使用避免链接和引号括起来的DAO QueryDef参数:
On that same note, because you are passing literal values to an SQL query, consider parameterizing with DAO QueryDef parameters that avoids concatenation and quote enclosures:
Dim oDb As DAO.Database, qdef As DAO.QueryDef
Dim StrSql As String, strProductionStatus As String
GetCurrentProductionStatusString NewProductionStatus, strProductionStatus
Set oDb = CurrentDb
StrSql = "PARAMETERS strProductionStatusParam Text(255), lngProductionIdParam Long;" _
& " UPDATE tProduction SET tProduction.Statut = [strProductionStatusParam]" _
& " WHERE (tProduction.IdProduction = [lngProductionIdParam]);"
Set qdef = oDb.CreateQueryDef("", StrSql)
qdef!strProductionStatusParam = strProductionStatus
qdef!lngProductionIdParam = lngProductionId
qdef.Execute dbFailOnError
Set qdef = Nothing
Set oDb = Nothing
这篇关于Vba访问错误91的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!