"no current record" error in custom report in Access 2003

[BethHill]

Hello,

I have a report in Access 2003 with a two groupings. In the detail section
of the report, I have a wide text box (name is Text0) that is not bound. On
the "On Format" procedure of the detail section, I have the following code:

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * from [tbl Main]")

rs.MoveFirst

intLength = 0
stResults = ""

If rs.EOF = True And rs.BOF = True Then Exit Sub

Do Until rs.EOF 'Do this procedure until the End of File is reached.
intLength = 0
stResults = ""
Do Until Len(stResults) >= 50
intLength = Len(rs!Req_ID)
Do Until intLength >= 12
stResults = stResults & rs!Req_ID & " "
intLength = Len(stResults)
Loop

text0.Value = stResults

rs.MoveNext
Loop 'The text box is full
Loop 'End of File

rs.Close
db.Close



End Sub


I have several Msgbox in the code that if taken out, the "no current record"
appears. I want the report to open with the recordset at the end of the code
but at the end of the code, I get the "no current record" error message.
Once I click OK and stop the debugging process, the report opens in design
view. What am I doing wrong?

 

[Nick via AccessMonster.com]

BethHill,

I see two spots that may cause the "No Current Record" message.

The rs.MoveFirst will cause this error if the recordset is empty - I would
recommend
combining it with other code as follows:

If rs.EOF = True And rs.BOF = True Then
Exit Sub
Else
rs.Movefirst
End if

Also, you may have a problem with the Do Loop you set up. The code will move
to the next record in rs until the length is greater than 50. This raises a
question in my mind:
What if there are not enough records in the recordset to reach the 50
character requirement to stop the Do Loop? Say you only have 2 records and
both records + formatting only combine to be 40 characters. The code will
try to move to the next record (even though it doesn't exist) and will cause
an error when you try to reference a property (because it will be at the EOF).
You may need to test for rs.EOF after rs.MoveNext:

If rs.EOF Then Exit Do

If neither of those solves your problem, post back with the code and the
exact line you keep getting the error on. I have had more than enough
headaches caused by "No Current Record" errors, so I should hopefully be able
to help.

HTH,
Nick

Hello,

I have a report in Access 2003 with a two groupings. In the detail section
of the report, I have a wide text box (name is Text0) that is not bound. On
the "On Format" procedure of the detail section, I have the following code:

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * from [tbl Main]")

rs.MoveFirst

intLength = 0
stResults = ""

If rs.EOF = True And rs.BOF = True Then Exit Sub

Do Until rs.EOF 'Do this procedure until the End of File is reached.
intLength = 0
stResults = ""
Do Until Len(stResults) >= 50
intLength = Len(rs!Req_ID)
Do Until intLength >= 12
stResults = stResults & rs!Req_ID & " "
intLength = Len(stResults)
Loop

text0.Value = stResults

rs.MoveNext
Loop 'The text box is full
Loop 'End of File

rs.Close
db.Close


End Sub

I have several Msgbox in the code that if taken out, the "no current record"
appears. I want the report to open with the recordset at the end of the code
but at the end of the code, I get the "no current record" error message.
Once I click OK and stop the debugging process, the report opens in design
view. What am I doing wrong?

 

[BethHill]

Nick,

Thank you for your help. The error message did go away but the report is
only printing the last record in the record set over and over. What am I
doing wrong?

BethHill,

I see two spots that may cause the "No Current Record" message.

The rs.MoveFirst will cause this error if the recordset is empty - I would
recommend
combining it with other code as follows:

If rs.EOF = True And rs.BOF = True Then
Exit Sub
Else
rs.Movefirst
End if

Also, you may have a problem with the Do Loop you set up. The code will move
to the next record in rs until the length is greater than 50. This raises a
question in my mind:
What if there are not enough records in the recordset to reach the 50
character requirement to stop the Do Loop? Say you only have 2 records and
both records + formatting only combine to be 40 characters. The code will
try to move to the next record (even though it doesn't exist) and will cause
an error when you try to reference a property (because it will be at the EOF).
You may need to test for rs.EOF after rs.MoveNext:

If rs.EOF Then Exit Do

If neither of those solves your problem, post back with the code and the
exact line you keep getting the error on. I have had more than enough
headaches caused by "No Current Record" errors, so I should hopefully be able
to help.

HTH,
Nick

Hello,

I have a report in Access 2003 with a two groupings. In the detail section
of the report, I have a wide text box (name is Text0) that is not bound. On
the "On Format" procedure of the detail section, I have the following code:

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * from [tbl Main]")

rs.MoveFirst

intLength = 0
stResults = ""

If rs.EOF = True And rs.BOF = True Then Exit Sub

Do Until rs.EOF 'Do this procedure until the End of File is reached.
intLength = 0
stResults = ""
Do Until Len(stResults) >= 50
intLength = Len(rs!Req_ID)
Do Until intLength >= 12
stResults = stResults & rs!Req_ID & " "
intLength = Len(stResults)
Loop

text0.Value = stResults

rs.MoveNext
Loop 'The text box is full
Loop 'End of File

rs.Close
db.Close


End Sub

I have several Msgbox in the code that if taken out, the "no current record"
appears. I want the report to open with the recordset at the end of the code
but at the end of the code, I get the "no current record" error message.
Once I click OK and stop the debugging process, the report opens in design
view. What am I doing wrong?

 

[Nick via AccessMonster.com]

Beth,

I'm not entirely sure what's going on now with the last record because I
don't have a feel for what is supposed to be achieved through your code. A
few questions:

What is contained in your [tbl Main]? (Fields / data types)
Regarding the 'Do Until Len >= 50' and 'Do Until Len >= 12'
Why the 50 and 12? Are those length restrictions on an output, or some
kind of qualifier?
What is the textbox (Text0) supposed to display?

I just want to be sure I understand the code's purpose before I throw out
some suggestions; I don't want to dig you further into a hole. Once I
understand the above questions I think that we can get this figured out
pretty quickly.

-Nick

Nick,

Thank you for your help. The error message did go away but the report is
only printing the last record in the record set over and over. What am I
doing wrong?

BethHill,

[quoted text clipped - 73 lines]