ljcor
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.Fi leSystemOb ject")
strWriteFile = "C:\Starbldr\AP_" & Format(Now(), "dd-mm-yy") & " " & Format(Now(), "h-mm-ss") & ".txt"
Set ExpFile = fs.CreateTextFile(strWrite File, 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.Fi leSystemOb ject")
strWriteFile = "C:\Starbldr\AP_" & Format(Now(), "dd-mm-yy") & " " & Format(Now(), "h-mm-ss") & ".txt"
Set ExpFile = fs.CreateTextFile(strWrite File, 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
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.Fi
strWriteFile = "C:\Starbldr\AP_" & Format(Now(), "dd-mm-yy") & " " & Format(Now(), "h-mm-ss") & ".txt"
Set ExpFile = fs.CreateTextFile(strWrite
'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.Fi
strWriteFile = "C:\Starbldr\AP_" & Format(Now(), "dd-mm-yy") & " " & Format(Now(), "h-mm-ss") & ".txt"
Set ExpFile = fs.CreateTextFile(strWrite
'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
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
If rst.RecordsetCount > 0 Then
Instead:
If rst.RecordsetCount = 0 then
' ** no records - outta here
Else
' *** what you are doing now
ASKER
MX,
Set ExpFile = Nothing
I did that but got the same error.
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
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
mx
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.
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
On Error Resume Next
ExpFile.Close
Err.Clear
.... or ... just get rid of that statement.
mx
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(strWrite File, True)
strWriteFile = "C:\Starbldr\AP_" & Format(Now(), "dd-mm-yy") & " " & Format(Now(), "h-mm-ss") & ".txt"
Set ExpFile = fs.CreateTextFile(strWrite
OOOOH ... sorry ... I thought the problem was at the very LAST Line
ExpFile.Close
Crap ... ignore everything I've said.
ExpFile.Close
Crap ... ignore everything I've said.
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
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.Fi leSystemOb ject")
because
fs
has not been defined ... eg
Dim fs as Object ' (or whatever it needs to be)
Then go from there.
mx
Option Compare Database
Option Explicit
Then compile
The first compile error will be on
Set fs = CreateObject("Scripting.Fi
because
fs
has not been defined ... eg
Dim fs as Object ' (or whatever it needs to be)
Then go from there.
mx
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
good luck ... get rid of all the compile errors first.
mx
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
Have a good hour - or so.
I'm pretty sure this will still be here.
jack
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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!
Are we done here, lol ?
mx
mx
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
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
oh ... don't forget to close this Q :-)
mx
Set ExpFile = Nothing
instead.
mx