Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ACCESS 2000 - "Set rst" error message

Posted on 2007-09-27
22
Medium Priority
?
474 Views
Last Modified: 2009-12-16
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
0
Comment
Question by:ljcor
  • 13
  • 9
22 Comments
 
LVL 75
ID: 19976483
Try

Set ExpFile = Nothing

instead.

mx
0
 
LVL 75
ID: 19976494
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
 

Author Comment

by:ljcor
ID: 19976511
MX,

Set ExpFile = Nothing

I did that but got the same error.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 75
ID: 19976530
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
 
LVL 75
ID: 19976531
Does the code work up to this point - for sure ?

mx
0
 

Author Comment

by:ljcor
ID: 19976575
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
 
LVL 75
ID: 19976578
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
 

Author Comment

by:ljcor
ID: 19976582
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
 
LVL 75
ID: 19976585
OOOOH ... sorry ... I thought the problem was at the very LAST Line
 ExpFile.Close

Crap ... ignore everything I've said.

0
 

Author Comment

by:ljcor
ID: 19976588
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
 
LVL 75
ID: 19976591
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
 

Author Comment

by:ljcor
ID: 19976593
No problem.
0
 
LVL 75
ID: 19976596
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
 

Author Comment

by:ljcor
ID: 19976620
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
 

Author Comment

by:ljcor
ID: 19976730
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 19976772
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
 
LVL 75
ID: 19976780
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
 

Author Comment

by:ljcor
ID: 19976873


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
 
LVL 75
ID: 19976888
nah .... 60 GB on my notebook ...

using True Image

www.acronis.com

the best

mx
0
 
LVL 75
ID: 19976889
Are we done here, lol ?

mx
0
 

Author Comment

by:ljcor
ID: 19976933
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
 
LVL 75
ID: 19976939
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month20 days, 23 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question