CODEWITHSUNDEEP
sqlvbams-access
Elen Glimois
Elen Glimois

Reputation: 47

Vba Access error 91

I try to run this code 

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

This code work but give me error 91.

Object variable or With block variable not set

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 ?

Thanks

Upvotes: 0

Views: 873

Answers (2)

Parfait
Parfait

Reputation: 107567

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.

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

Upvotes: 3

Bruce Huang
Bruce Huang

Reputation: 143

Try to remove the oRst related code lines. This variable is not initialized and not used.

Upvotes: 1

Related Questions