Link to home
Start Free TrialLog in
Avatar of ljcor
ljcorFlag for United States of America

asked on

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
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Try

Set ExpFile = Nothing

instead.

mx
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
Avatar of ljcor

ASKER

MX,

Set ExpFile = Nothing

I did that but got the same error.
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

Does the code work up to this point - for sure ?

mx
Avatar of ljcor

ASKER

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.

Jack ... a quick fix for the moment is:

On Error Resume Next
 ExpFile.Close
Err.Clear

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

mx
Avatar of ljcor

ASKER

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)
OOOOH ... sorry ... I thought the problem was at the very LAST Line
 ExpFile.Close

Crap ... ignore everything I've said.

Avatar of ljcor

ASKER

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
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
Avatar of ljcor

ASKER

No problem.
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
Avatar of ljcor

ASKER

It does not show an;y compile errors.

Have a good hour - or so.

I'm pretty sure this will still be here.

jack
Avatar of ljcor

ASKER

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.

ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of ljcor

ASKER



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!


nah .... 60 GB on my notebook ...

using True Image

www.acronis.com

the best

mx
Avatar of ljcor

ASKER

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
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