ACCESS 2000 - "Set rst" error message

On the last line  shown here I encounter an error message:
   Runtime error 91
   Object variable or With block variable not set
(I don't understand the message in this context.)

Public Sub CreateAndFormatDifFile()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String, strWriteFile As String, NewLine As String

    Set fs = CreateObject("Scripting.FileSystemObject")
   
    strWriteFile = "C:\Starbldr\AP_" & Format(Now(), "dd-mm-yy") & " " & Format(Now(), "h-mm-ss") & ".txt"
    Set ExpFile = fs.CreateTextFile(strWriteFile, True)

'Print the Header line
    DoCmd.OpenQuery "qryDeleteDIFHeader"
    DoCmd.OpenQuery "qryCreateDIFHeader"
   
    strSQL = "SELECT * FROM DIFHeader"
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
Here is the entire procedure:

Public Sub CreateAndFormatDifFile()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String, strWriteFile As String, NewLine As String

    Set fs = CreateObject("Scripting.FileSystemObject")
   
    strWriteFile = "C:\Starbldr\AP_" & Format(Now(), "dd-mm-yy") & " " & Format(Now(), "h-mm-ss") & ".txt"
    Set ExpFile = fs.CreateTextFile(strWriteFile, True)

'Print the Header line
    DoCmd.OpenQuery "qryDeleteDIFHeader"
    DoCmd.OpenQuery "qryCreateDIFHeader"
   
    strSQL = "SELECT * FROM DIFHeader"
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

    NewLine = Recty = rst![REC-TY]
    NewLine = NewLine & rst![CUST-VEND] & Space(6 - Len(rst![CUST-VEND]))
    NewLine = NewLine & rst![INVOICE] & Space(10 - Len(rst![INVOICE]))
    NewLine = NewLine & Space(15)
    NewLine = NewLine & rst![INV-DESC] & Space(24 - Len(rst![INV-DESC]))
    NewLine = NewLine & Space(4)
    NewLine = NewLine & rst![POST-CODE] & Space(4 - Len(rst![POST-CODE]))
    NewLine = NewLine & Space(8)
    NewLine = NewLine & rst![INV-DATE]
    NewLine = NewLine & rst![DUE-DATE]
   
    ExpFile.WriteLine NewLine
   
'Print the Detail lines
    DoCmd.OpenQuery "qryDeleteDIFDetail"
    DoCmd.OpenQuery "qryCreateDIFDetail"
   
    strSQL = "SELECT * FROM DIFDetail"
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
   
    If rst.RecordsetCount > 0 Then
        rst.MoveFirst
        Do Until rst.EOF
            NewLine = Recty = rst![REC-TY]
            NewLine = NewLine & Space(5)
            NewLine = NewLine & rst![J-E-I-TYPE]
            NewLine = NewLine & rst![Job] & Space(10 - Len(rst![Job]))
            NewLine = NewLine & rst![PHASE] & Space(5 - Len(rst![PHASE]))
            NewLine = NewLine & rst![COST] & Space(5 - Len(rst![COST]))
            NewLine = NewLine & rst![COST-TYPE] & Space(2 - Len(rst![COST-TYPE]))
            NewLine = NewLine & Space(11)
            NewLine = NewLine & rst![ACCOUNT] & Space(8 - Len(rst![ACCOUNT]))
            NewLine = NewLine & Space(12 - Len(rst![DIST-AMT])) & rst![DIST-AMT]
            NewLine = NewLine & Space(42)
            NewLine = NewLine & rst![DIST-DESC] & Space(24 - Len(rst![DIST-DESC]))
                       
            ExpFile.WriteLine NewLine
            rst.MoveNext
        Loop
    End If
   
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing

    ExpFile.Close
End Sub
ljcorAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Geeze ... what I meant to say above was "I'm going to be doing a backup for an hour or so"

Anyway ... I see the problem ... db  is not defined:


    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fs As Object, ExpFile As Object
    Dim strSQL As String, strWriteFile As String, NewLine As String

    Set db = CurrentDb  '************** ADD THIS
    strSQL = "SELECT * FROM DIFHeader"
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try

Set ExpFile = Nothing

instead.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Also  fyi  ... this is not reliable ... test for zero record count instead

  If rst.RecordsetCount > 0 Then

Instead:

  If rst.RecordsetCount = 0 then
      ' ** no records - outta here
  Else
   ' *** what you are doing now
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ljcorAuthor Commented:
MX,

Set ExpFile = Nothing

I did that but got the same error.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
The message means you are trying to Close something that is not open, however I don't see why that would be.

Wait ... where have you DImmed ExpFile ??  All I see is a Set

Does this code compile ...?

mx

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Does the code work up to this point - for sure ?

mx
0
 
ljcorAuthor Commented:
The code does compile and the runtime works "for sure" down to the statement:
   Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

I don't know what I would say to dimension ExpFile.

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Jack ... a quick fix for the moment is:

On Error Resume Next
 ExpFile.Close
Err.Clear

.... or ... just get rid of that statement.

mx
0
 
ljcorAuthor Commented:
When I say "it works", that is because it does write an actual header item to the Starbldr directory.

    strWriteFile = "C:\Starbldr\AP_" & Format(Now(), "dd-mm-yy") & " " & Format(Now(), "h-mm-ss") & ".txt"
    Set ExpFile = fs.CreateTextFile(strWriteFile, True)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
OOOOH ... sorry ... I thought the problem was at the very LAST Line
 ExpFile.Close

Crap ... ignore everything I've said.

0
 
ljcorAuthor Commented:
I don't know if I can get rid of it since it is necessary in order to write each of the line items to the Starbldr directory.

How will I write the data out to Starbldr?

jc
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Jack ... put this in the declaration section of where ever this code is (form, module):

Option Compare Database
Option Explicit

Then compile

The first compile error will be on

Set fs = CreateObject("Scripting.FileSystemObject")

because  

fs

has not been defined ... eg

Dim fs as Object  ' (or whatever it needs to be)

Then go from there.

mx
0
 
ljcorAuthor Commented:
No problem.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Hey ... I going to be doing a back for an hour or so ... thus, offline for a while.

good luck ...  get rid of all the compile errors first.

mx
0
 
ljcorAuthor Commented:
It does not show an;y compile errors.

Have a good hour - or so.

I'm pretty sure this will still be here.

jack
0
 
ljcorAuthor Commented:
I did Dim  FS and Expfile as object.

That compiled and ran down to the "Set rst ..." statement.

Then I copied these two lines and put them immediately below the four lines of DIM statements.

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fs As Object, ExpFile As Object
    Dim strSQL As String, strWriteFile As String, NewLine As String
   
    strSQL = "SELECT * FROM DIFHeader"
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

Got the same error "91" message.

The system certainly does not like the  "Set rst..."  line, yet it looks very good.

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
As a side note .... here:

    rst.Close
    Set rst = Nothing
    db.Close                     ' *** Remove this statement
    Set db = Nothing

get rid of

db.Close

You need to close the 'currentDB'

Just keep the
Set db  = Nothing

mx
0
 
ljcorAuthor Commented:


OK - got by that tiny hurdle that caused me and Experts bigtime hours.

As I was running those fixes I found that I was encountering lots of null items in the data to be printed.  I NZ'd them - and I hope that doesn't screw things.


A 1 hour backup - you need a helper!


0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
nah .... 60 GB on my notebook ...

using True Image

www.acronis.com

the best

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Are we done here, lol ?

mx
0
 
ljcorAuthor Commented:
Absolutely done -  and done in!

You are a very, very good friend to me.  And to many others, too.

Thanks again, JA.  Many thanks.

jack
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Always a pleasure Jack ... sorry I didn't set the DB thing from the git go.  I was distracted knowing I was about the watch the (recorded) season opener of Boston (ill)Legal ...

oh ... don't forget to close this Q :-)

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.