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" 2 21920
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"
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" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ronny Sigo |
last post by:
Hello all,
I am trying out arrays for the first time now. While running this code below
I get the error: "No current record" (or at least something like that, cause
I have the dutch version)
Can...
|
by: Jamey |
last post by:
I perused old posts for an answer to this for at least an hour, and
I've found a work-around, but no definitive answer.
Synopsis of the problem:
On NotInList or ctl.Requery commands where a...
|
by: ydprasad |
last post by:
I am trying to convert the code that was written in VB using DAO to ADO.
But when i tried to do following getting an error '3251'.
*************code**************************
Dim cn As New...
|
by: ruvi |
last post by:
I am getting runtime error 3021 - Either EOF or BOF is true or the current record has been deleted.....
I have 2 combo boxes in a form- One for the client and the other for the project.
When the...
|
by: darrel |
last post by:
Hi there, can someone tell me what is wrong with my code am getting a run time error 3021:
Here my code:
If rs.State = adStateOpen Then rs.Close
rs.Open "Select * from where ID like...
|
by: jmarcrum |
last post by:
i need some help
i have a table with 5 city divisions
1 = D1
2 = D2
3 = D3
4 = D4
and
|
by: THEAF |
last post by:
i'm trying to create a reminder form, when i add a new reminder its ok but when i try to delete a reminder then i get No Current Record problem.
Form Load
Private Sub Form_Load()
Dim s() As...
|
by: Jamen98 |
last post by:
I'm running into a problem with running multiple queries. I have two tables that look like:
Risk_Mgmt_Table
Fields: (Values)
Filing_State: (CA)
Membership: (Yes)
Membership_Rate: (.95)...
|
by: Ferwayne Yalung |
last post by:
The code works when I logged in as a admin, teacher or student.But when i try an unregistered username, runtime error 3021 (either BOF or EOF is true, or the current record
has been deleted....
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |