467,864 Members | 1,799 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,864 developers. It's quick & easy.

runtime error 3021 'no current record' after it runs through the recordset?

I'm trying to write the results of a query, a name, ID number, and
date
out to a notepad .txt file to
print on a "legacy" printer.

I get the output from the first 2 "write" lines over the course of the
recordset, but get the 3021 error for
the third line, on the last record in the recordset.

(I did not include the input boxes code for the dates range because
that's working.)
I tried Debug.Print rs1.Fields(8).NAME and the field name matches
what's
typed into the SQL statement.

Also, I tested for end of file with a msg box:
If rs1.EOF Then
MsgBox "end of file"
End If

and it returned
"end of file"

If someone could point me in the right direction, I would appreciate
it!

Polly
cavavite(removethis)@hotmail.com

Private Sub cmdRunLblsOutput_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim strSQL As String

'++++++++++++++++ Report Variables
Dim strtFn, strtLn, strFnLn, strPID As String

Set db = CurrentDb
strSQL = "" 'initialize strSQL value

'SQL statement
strSQL = "SELECT qselbsln.pid, fn,ln,CASID, "
strSQL = strSQL & "GRPCD, MaxOftubeLogid, " '
strSQL = strSQL & "FAMID, MEMID, dPossibleDraw " 'tblSelectLabels.
strSQL = strSQL & "FROM qselbsln, tblSelectLabels INNER JOIN
qmaxTubeId_Pid ON "
strSQL = strSQL & "tblSelectLabels.PID = qmaxTubeId_Pid.pid "
strSQL = strSQL & "WHERE ((tblSelectLabels.dPossibleDraw) between #"
strSQL = strSQL & dBeg & "# and #" & dEnd & "#) "
strSQL = strSQL & "and ((tblSelectLabels.chkSelectForLabel)=Yes) and "
strSQL = strSQL & "(tblSelectLabels.PID = qselBsln.PID) "

Set rs1 = db.OpenRecordset(strSQL)

Debug.Print rs1.Fields(8).NAME '----------->this works
rs1.MoveFirst

MsgBox rs1!dPossibleDraw '-------------> no message box
comes up

Open "c:\possibleDraws\PossDrws.txt" For Output As #1

Do Until rs1.EOF

rs1.MoveNext

Write #1, strFnLn
Write #1, strPID
If rs1.EOF Then
MsgBox "end of file"
End If
Write #1, rs1![dPossibleDraw]
Loop

Close #1
MsgBox "done"
Nov 13 '05 #1
  • viewed: 21563
Share:
2 Replies
You need the MoveNext *after* you have written the line, immediately before
the Loop line. After the MoveNext you may be at EOF.

Could you achieve the same thing by saving the query, and using just:
Docmd.TransferText acExportDelim, , "Query1", "C:\MyFile.txt"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Polly" <ca******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
I'm trying to write the results of a query, a name, ID number, and
date
out to a notepad .txt file to
print on a "legacy" printer.

I get the output from the first 2 "write" lines over the course of the
recordset, but get the 3021 error for
the third line, on the last record in the recordset.

(I did not include the input boxes code for the dates range because
that's working.)
I tried Debug.Print rs1.Fields(8).NAME and the field name matches
what's
typed into the SQL statement.

Also, I tested for end of file with a msg box:
If rs1.EOF Then
MsgBox "end of file"
End If

and it returned
"end of file"

If someone could point me in the right direction, I would appreciate
it!

Polly
cavavite(removethis)@hotmail.com

Private Sub cmdRunLblsOutput_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim strSQL As String

'++++++++++++++++ Report Variables
Dim strtFn, strtLn, strFnLn, strPID As String

Set db = CurrentDb
strSQL = "" 'initialize strSQL value

'SQL statement
strSQL = "SELECT qselbsln.pid, fn,ln,CASID, "
strSQL = strSQL & "GRPCD, MaxOftubeLogid, " '
strSQL = strSQL & "FAMID, MEMID, dPossibleDraw " 'tblSelectLabels.
strSQL = strSQL & "FROM qselbsln, tblSelectLabels INNER JOIN
qmaxTubeId_Pid ON "
strSQL = strSQL & "tblSelectLabels.PID = qmaxTubeId_Pid.pid "
strSQL = strSQL & "WHERE ((tblSelectLabels.dPossibleDraw) between #"
strSQL = strSQL & dBeg & "# and #" & dEnd & "#) "
strSQL = strSQL & "and ((tblSelectLabels.chkSelectForLabel)=Yes) and "
strSQL = strSQL & "(tblSelectLabels.PID = qselBsln.PID) "

Set rs1 = db.OpenRecordset(strSQL)

Debug.Print rs1.Fields(8).NAME '----------->this works
rs1.MoveFirst

MsgBox rs1!dPossibleDraw '-------------> no message box
comes up

Open "c:\possibleDraws\PossDrws.txt" For Output As #1

Do Until rs1.EOF

rs1.MoveNext

Write #1, strFnLn
Write #1, strPID
If rs1.EOF Then
MsgBox "end of file"
End If
Write #1, rs1![dPossibleDraw]
Loop

Close #1
MsgBox "done"

Nov 13 '05 #2
Dear Allen,
Thank you. That did it.
Thanks for the suggestion to use a docmd statement, but I need to
format the output to the sneaker-net printer. The output was just to
see if I could get it to write to an external file first. If I have
trouble with the formatting or function I'm going to use, I'll be
posting that code, too.

Polly

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
You need the MoveNext *after* you have written the line, immediately before
the Loop line. After the MoveNext you may be at EOF.

Could you achieve the same thing by saving the query, and using just:
Docmd.TransferText acExportDelim, , "Query1", "C:\MyFile.txt"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Polly" <ca******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
I'm trying to write the results of a query, a name, ID number, and
date
out to a notepad .txt file to
print on a "legacy" printer.

I get the output from the first 2 "write" lines over the course of the
recordset, but get the 3021 error for
the third line, on the last record in the recordset.

(I did not include the input boxes code for the dates range because
that's working.)
I tried Debug.Print rs1.Fields(8).NAME and the field name matches
what's
typed into the SQL statement.

Also, I tested for end of file with a msg box:
If rs1.EOF Then
MsgBox "end of file"
End If

and it returned
"end of file"

If someone could point me in the right direction, I would appreciate
it!

Polly
cavavite(removethis)@hotmail.com

Private Sub cmdRunLblsOutput_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim strSQL As String

'++++++++++++++++ Report Variables
Dim strtFn, strtLn, strFnLn, strPID As String

Set db = CurrentDb
strSQL = "" 'initialize strSQL value

'SQL statement
strSQL = "SELECT qselbsln.pid, fn,ln,CASID, "
strSQL = strSQL & "GRPCD, MaxOftubeLogid, " '
strSQL = strSQL & "FAMID, MEMID, dPossibleDraw " 'tblSelectLabels.
strSQL = strSQL & "FROM qselbsln, tblSelectLabels INNER JOIN
qmaxTubeId_Pid ON "
strSQL = strSQL & "tblSelectLabels.PID = qmaxTubeId_Pid.pid "
strSQL = strSQL & "WHERE ((tblSelectLabels.dPossibleDraw) between #"
strSQL = strSQL & dBeg & "# and #" & dEnd & "#) "
strSQL = strSQL & "and ((tblSelectLabels.chkSelectForLabel)=Yes) and "
strSQL = strSQL & "(tblSelectLabels.PID = qselBsln.PID) "

Set rs1 = db.OpenRecordset(strSQL)

Debug.Print rs1.Fields(8).NAME '----------->this works
rs1.MoveFirst

MsgBox rs1!dPossibleDraw '-------------> no message box
comes up

Open "c:\possibleDraws\PossDrws.txt" For Output As #1

Do Until rs1.EOF

rs1.MoveNext

Write #1, strFnLn
Write #1, strPID
If rs1.EOF Then
MsgBox "end of file"
End If
Write #1, rs1![dPossibleDraw]
Loop

Close #1
MsgBox "done"

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Ronny Sigo | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.