Run-time error 91: Object variable or With block variable not set

Question

I'm using Access 2010 VBA that is returning a recordset from an IBM iSeries. I have the following loop to append the recordset to a local table:

'Loop through recordset and place values
    Do While rsti401.EOF = False
Set rst401 = CurrentDb.OpenRecordset("tblLocal_SL401WK", dbOpenDynaset, dbSeeChanges)
With rst401
    .AddNew
    .Fields("PC") = rsti401.Fields("PC")
    .Fields("TIME") = rsti401.Fields("TIME")
    .Fields("CONO") = rsti401.Fields("CONO")
    .Fields("STYCOL") = rsti401.Fields("STYCOL")
    .Fields("WHSE") = rsti401.Fields("WHSE")
    .Fields("CUNO") = rsti401.Fields("CUNO")
    .Fields("SIZE01") = rsti401.Fields("SIZE01")
    .Fields("SIZE02") = rsti401.Fields("SIZE02")
    .Fields("SIZE03") = rsti401.Fields("SIZE03")
    .Fields("SIZE04") = rsti401.Fields("SIZE04")
    .Fields("SIZE05") = rsti401.Fields("SIZE05")
    .Fields("SIZE06") = rsti401.Fields("SIZE06")
    .Fields("SIZE07") = rsti401.Fields("SIZE07")
    .Fields("SIZE08") = rsti401.Fields("SIZE08")
    .Fields("SIZE09") = rsti401.Fields("SIZE09")
    .Fields("SIZE10") = rsti401.Fields("SIZE10")
    .Fields("SIZE11") = rsti401.Fields("SIZE11")
    .Fields("SIZE12") = rsti401.Fields("SIZE12")
    .Fields("SIZE13") = rsti401.Fields("SIZE13")
    .Fields("SIZE14") = rsti401.Fields("SIZE14")
    .Fields("SIZE15") = rsti401.Fields("SIZE15")
    .Fields("BQTY01") = rsti401.Fields("BQTY01")
    .Fields("BQTY02") = rsti401.Fields("BQTY02")
    .Fields("BQTY03") = rsti401.Fields("BQTY03")
    .Fields("BQTY04") = rsti401.Fields("BQTY04")
    .Fields("BQTY05") = rsti401.Fields("BQTY05")
    .Fields("BQTY06") = rsti401.Fields("BQTY06")
    .Fields("BQTY07") = rsti401.Fields("BQTY07")
    .Fields("BQTY08") = rsti401.Fields("BQTY08")
    .Fields("BQTY09") = rsti401.Fields("BQTY09")
    .Fields("BQTY10") = rsti401.Fields("BQTY10")
    .Fields("BQTY11") = rsti401.Fields("BQTY11")
    .Fields("BQTY12") = rsti401.Fields("BQTY12")
    .Fields("BQTY13") = rsti401.Fields("BQTY13")
    .Fields("BQTY14") = rsti401.Fields("BQTY14")
    .Fields("BQTY15") = rsti401.Fields("BQTY15")
.Update
End With
rsti401.MoveNext
Loop


'close connections
rsti401.Close
rst401.Close
IBM.Close

Set IBM = Nothing
Set rst401 = Nothing
Set rsti401 = Nothing
Set CMD = Nothing

However, each time I run it I is stopping at the following line:

rst401.Close

With error 'Run-time error 91'. I can't work it out. I've set rst401 at the beginning, so why am I still getting the error.

Any pointers would be a great help.

Thanks,

Michael

Did you state Dim Rst1401 As New RecordSet before setting it? — Amen Jlili
– Amen Jlili, Commented Mar 13, 2015 at 11:19
Try stepping through your code in debug mode and watching how your rst401 variable evolves (instructions). If that doesn't help, then I suggest you see this for guidance: How to create a Minimal, Complete, and Verifiable example. — Jean-François Corbett
– Jean-François Corbett, Commented Mar 13, 2015 at 11:20
I would try using a database variable instead of using CurrentDb: Dim dB as Recordset Set dB = CurrentDb. Or move the offending line into the With block. — AVG
– AVG, Commented Mar 13, 2015 at 11:25
One additional note, though not related to your problem. rst401 is opening an entire table, returning records, when all you are trying to do is add a new record. It would be much more efficient to use a where clause that returns not records, or use an insert statement instead of a recordset. — AVG
– AVG, Commented Mar 13, 2015 at 12:26

PaulFrancis · Accepted Answer · 2015-03-13 11:30:21Z

The problem you have is because the rst401 is set inside the Do While Loop, and you are trying to close an object that has lost its scope outside the Loop. Suggest you make the following changes.

'Loop through recordset and place values
Set rst401 = CurrentDb.OpenRecordset("tblLocal_SL401WK", dbOpenDynaset, dbSeeChanges)

Do While rsti401.EOF = False        
    With rst401
        .AddNew
        .Fields("PC") = rsti401.Fields("PC")
        .Fields("TIME") = rsti401.Fields("TIME")
        .Fields("CONO") = rsti401.Fields("CONO")
        .Fields("STYCOL") = rsti401.Fields("STYCOL")
        .Fields("WHSE") = rsti401.Fields("WHSE")
        .Fields("CUNO") = rsti401.Fields("CUNO")
        .Fields("SIZE01") = rsti401.Fields("SIZE01")
        .Fields("SIZE02") = rsti401.Fields("SIZE02")
        .Fields("SIZE03") = rsti401.Fields("SIZE03")
        .Fields("SIZE04") = rsti401.Fields("SIZE04")
        .Fields("SIZE05") = rsti401.Fields("SIZE05")
        .Fields("SIZE06") = rsti401.Fields("SIZE06")
        .Fields("SIZE07") = rsti401.Fields("SIZE07")
        .Fields("SIZE08") = rsti401.Fields("SIZE08")
        .Fields("SIZE09") = rsti401.Fields("SIZE09")
        .Fields("SIZE10") = rsti401.Fields("SIZE10")
        .Fields("SIZE11") = rsti401.Fields("SIZE11")
        .Fields("SIZE12") = rsti401.Fields("SIZE12")
        .Fields("SIZE13") = rsti401.Fields("SIZE13")
        .Fields("SIZE14") = rsti401.Fields("SIZE14")
        .Fields("SIZE15") = rsti401.Fields("SIZE15")
        .Fields("BQTY01") = rsti401.Fields("BQTY01")
        .Fields("BQTY02") = rsti401.Fields("BQTY02")
        .Fields("BQTY03") = rsti401.Fields("BQTY03")
        .Fields("BQTY04") = rsti401.Fields("BQTY04")
        .Fields("BQTY05") = rsti401.Fields("BQTY05")
        .Fields("BQTY06") = rsti401.Fields("BQTY06")
        .Fields("BQTY07") = rsti401.Fields("BQTY07")
        .Fields("BQTY08") = rsti401.Fields("BQTY08")
        .Fields("BQTY09") = rsti401.Fields("BQTY09")
        .Fields("BQTY10") = rsti401.Fields("BQTY10")
        .Fields("BQTY11") = rsti401.Fields("BQTY11")
        .Fields("BQTY12") = rsti401.Fields("BQTY12")
        .Fields("BQTY13") = rsti401.Fields("BQTY13")
        .Fields("BQTY14") = rsti401.Fields("BQTY14")
        .Fields("BQTY15") = rsti401.Fields("BQTY15")
        .Update
    End With
    rsti401.MoveNext
Loop

'close connections
rsti401.Close
rst401.Close
IBM.Close

Set IBM = Nothing
Set rst401 = Nothing
Set rsti401 = Nothing
Set CMD = Nothing

Collectives™ on Stack Overflow

Run-time error 91: Object variable or With block variable not set

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related