Not finding EOF - 'no current Record' error

  • Thread starter Thread starter Evi
  • Start date Start date
E

Evi

Hi all
I have Access 2000.
My database is a questionaire. The main form is the person taking the
questoinaire. The first subform FrmPersonAnswerSub - a single form linked to
the MainForm- holds the questions.
The second subform FrmPersonAnswerSub2 - linked to the first subform,
presents multi-choice answers for the user to select from.

A button on the main form, cmbNextQuestion moves to the next record.
The code works until I reach the last record, correctly reading a field in
FrmPersonAnswerSub. But it doesn't spot the EndOfFile and tries to move the
next record, giving me a No Current Record error.
What have I done wrong.

Private Sub cmbNextQuestion_Click()

Dim frm As Form
Dim rst As DAO.Recordset
Dim MySub As Control
Dim MyAnsType As Integer

Set frm = Me.FrmPersonAnswerSub.Form
Set rst = frm.RecordsetClone

MsgBox frm.RecordsetClone.RecordCount
'works correctly, counting the correct number of records
If Not rst.EOF Then
'doesn't recognize this bit.
rst.MoveNext
frm.Bookmark = rst.Bookmark
MyAnsType = rst!QuestID
MsgBox MyAnsType
'code correctly reads QuestID field in the first subform
Me.FrmPersonAnswerSub2.Requery
'requery second subform which is linked to the first subform
Else
'the code never gets to this next bit
MsgBox "That was the last question"
Me.SurvEndDate.SetFocus
'so that I can disable the NextQuestion button
Me.cmbNextQuestion.Enabled = False
Me.SurvEndDate = Date
Set frm = Nothing
Exit Sub
End If
Set rst = Nothing
Set frm = Nothing

End Sub


Evi
 
Hi Evi,

I changed a few lines in your code. you were trying to move next after you
checked for EOF. And I try not to use negative logic.... I get confused....
so the FI() function is reversed. I did a little testing so this *should*
run.... If is still errors, I have another way that will fix it.

Try this:

'----------code beg------------
Private Sub cmbNextQuestion_Click()
Dim frm As Form
Dim rst As DAO.Recordset
Dim MySub As Control
Dim MyAnsType As Integer

' I changed this next line
Set frm = Forms("FrmPersonAnswerSub")
Set rst = frm.RecordsetClone

'works correctly, counting the correct number of records
MsgBox frm.RecordsetClone.RecordCount

rst.MoveNext

If rst.EOF Then
MsgBox "That was the last question"
Me.SurvEndDate.SetFocus
'disable the NextQuestion button
Me.cmbNextQuestion.Enabled = False
Me.SurvEndDate = Date
Else
' not EOF
frm.Bookmark = rst.Bookmark
MyAnsType = rst!QuestID
MsgBox MyAnsType
'code correctly reads QuestID field in the first subform
Me.FrmPersonAnswerSub2.Requery
'requery second subform which is linked to the first subform
End If
Set rst = Nothing
Set frm = Nothing

End Sub
'----------code end------------

HTH
 
There is something else going on with this thing, Steve.
I've just opened it again and this time it is throwing a wobbly in the On
Load event which is meant to turn to the first question in the subform

Private Sub Form_Load()
Dim MySub As Control
Dim MyFrm As Form

Set MySub = Me.FrmPersonAnswerSub

Set MyFrm = Me.FrmPersonAnswerSub.Form
'highlighted line

It's giving an Invalid Reference to a Form or Report error - Error 2455

Yet that bit was working just 20 minutes ago. It's done a few of these,
things were working one moment and then they weren't.

Something is rotten in the state of Denmark

I would have thought a reference problem was to blame but I've nothing
missing and I've set them up as they are for my other dbs which use
recordsets

I think I'm going to have to start re-creating this form

Evi
 
When I tried to test your code for Private Sub cmbNextQuestion_Click(), I
kept getting an error on this line:

Set frm = Me.FrmPersonAnswerSub.Form

When I tried to compile (Toolbar- DEBUG/COMPILE ), I would get an error also.
I looked on "The Access Web" site and found an example that used

theForm = "MyFormName"
Set frm = Forms(theForm)


So I used: Set frm = Forms("FrmPersonAnswerSub")


Try using:

Private Sub Form_Load()
Dim MySub As Control
Dim MyFrm As Form

Set MySub = Me.FrmPersonAnswerSub

Set MyFrm = Forms("FrmPersonAnswerSub")



Is the first two lines of every code page

Option Compare Database
Option Explicit

Try toolbar - DEBUG/COMPILE. Does the code compile??
 
Thanks for giving it a go Steve.
I don't know why but the form now opens but the Next
button still wouldn't work as expected

It clogged up on
Set frm = Forms("FrmPersonAnswerSub")
When I look in the Debug line it says that Access can't find the Form

So I've got it working (sort of) by avoiding the EOF - it's not ideal but it
does work though I can't, for the life of me, think why it works.

It relies on the highest Question Number to spot the 'End Of File'
It seems happy enough when i force it into the subform with a findFirst.
You'd think I'd be happy that its working, but until I know why it wasn't
working in the first place and why it is working now, I've got an
uncomfortable feeling that it will all go wrong again :(


Dim frm As Form
Dim rst As DAO.Recordset
Dim MySub As Control
Dim MyAnsType As Integer
Dim CurrQ As Integer
Dim MaxQues As Integer

Set frm = Me.FrmPersonAnswerSub.Form
Set rst = frm.RecordsetClone
MaxQues = Nz(DMax("[QuestNum]", "TblQuestion"))
CurrQ = Me.FrmPersonAnswerSub.Form.QuestNum
'works correctly, counting the correct number of records
..
If CurrQ <> MaxQues Then
rst.FindFirst "[QuestNum]=" & CurrQ
'this seems to work OK
rst.MoveNext
frm.Bookmark = rst.Bookmark
MyAnsType = rst!TypeID

'code correctly reads TypeID field in the first subform
Me.FrmPersonAnswerSub2.Requery
'requery second subform which is linked to the first subform
Else

MsgBox "That was the last question"
Me.SurvEndDate.SetFocus
'so that I can disable the NextQuestion button
Me.cmbNextQuestion.Enabled = False
Me.SurvEndDate = Date
Set rst = Nothing
Set frm = Nothing
Exit Sub
End If
 
Hi Steve

I tried
Private Sub Form_Load()
Dim MySub As Control
Dim MyFrm As Form

Set MySub = Me.FrmPersonAnswerSub

Set MyFrm = Forms("FrmPersonAnswerSub")

etc but am still getting Error 2450 Can't find the form FrmPersonAnswerSub

when I open the form

Yes, the code compiles OK,

yes I have

Option Compare Database
Option Explicit

And I've checked my subform control name

If I put this in the On Load event, it works. But why?


Set MySub = Me.FrmPersonAnswerSub
MySub.SetFocus
Set MyFrm = Me.FrmPersonAnswerSub.Form
Me.FrmPersonAnswerSub.Form.RecordsetClone.MoveFirst


Evi
 
I tried the code again and it threw an error. I closed the mdb and opened it
again....no error. The tables are on A2K format using A2K3.

If you want, I could look at it. If you send it to me, please first remove
any confidential info, compact and zip it.

Sorry I couldn't be of more help.
 
Hi Steve
I've recoded the form so that it runs without the recordset code by simply
filtering on the question numbers
I've only got one bit of Recordset code in it, which edits a field in the
subform when I choose something in an Option Group but only once the main
form and subforms have been well and truly open and that seems to work OK

So far it all seems to be working. It's possible that the subform wasn't
recognised as being open while it was being changed via its linked fields or
that this problem is somehow Reference based.

Thanks so much for giving it a try and for your useful advice on avoiding
negative code. Ive definitely followed your suggestion on that and it is
much easier to follow.

Evi






Evi said:
Thanks for giving it a go Steve.
I don't know why but the form now opens but the Next
button still wouldn't work as expected

It clogged up on
Set frm = Forms("FrmPersonAnswerSub")
When I look in the Debug line it says that Access can't find the Form

So I've got it working (sort of) by avoiding the EOF - it's not ideal but it
does work though I can't, for the life of me, think why it works.

It relies on the highest Question Number to spot the 'End Of File'
It seems happy enough when i force it into the subform with a findFirst.
You'd think I'd be happy that its working, but until I know why it wasn't
working in the first place and why it is working now, I've got an
uncomfortable feeling that it will all go wrong again :(


Dim frm As Form
Dim rst As DAO.Recordset
Dim MySub As Control
Dim MyAnsType As Integer
Dim CurrQ As Integer
Dim MaxQues As Integer

Set frm = Me.FrmPersonAnswerSub.Form
Set rst = frm.RecordsetClone
MaxQues = Nz(DMax("[QuestNum]", "TblQuestion"))
CurrQ = Me.FrmPersonAnswerSub.Form.QuestNum
'works correctly, counting the correct number of records
.
If CurrQ <> MaxQues Then
rst.FindFirst "[QuestNum]=" & CurrQ
'this seems to work OK
rst.MoveNext
frm.Bookmark = rst.Bookmark
MyAnsType = rst!TypeID

'code correctly reads TypeID field in the first subform
Me.FrmPersonAnswerSub2.Requery
'requery second subform which is linked to the first subform
Else

MsgBox "That was the last question"
Me.SurvEndDate.SetFocus
'so that I can disable the NextQuestion button
Me.cmbNextQuestion.Enabled = False
Me.SurvEndDate = Date
Set rst = Nothing
Set frm = Nothing
Exit Sub
End If



Steve Sanford said:
Hi Evi,

I changed a few lines in your code. you were trying to move next after you
checked for EOF. And I try not to use negative logic.... I get confused....
so the FI() function is reversed. I did a little testing so this *should*
run.... If is still errors, I have another way that will fix it.

Try this:

'----------code beg------------
Private Sub cmbNextQuestion_Click()
Dim frm As Form
Dim rst As DAO.Recordset
Dim MySub As Control
Dim MyAnsType As Integer

' I changed this next line
Set frm = Forms("FrmPersonAnswerSub")
Set rst = frm.RecordsetClone

'works correctly, counting the correct number of records
MsgBox frm.RecordsetClone.RecordCount

rst.MoveNext

If rst.EOF Then
MsgBox "That was the last question"
Me.SurvEndDate.SetFocus
'disable the NextQuestion button
Me.cmbNextQuestion.Enabled = False
Me.SurvEndDate = Date
Else
' not EOF
frm.Bookmark = rst.Bookmark
MyAnsType = rst!QuestID
MsgBox MyAnsType
'code correctly reads QuestID field in the first subform
Me.FrmPersonAnswerSub2.Requery
'requery second subform which is linked to the first subform
End If
Set rst = Nothing
Set frm = Nothing

End Sub
'----------code end------------

HTH
Click to expand...
field
in
Click to expand...
move
the
Click to expand...
Click to expand...
 
Chuck said:
Huge snip

Evi,

If you have read some of my posts in Access.Reports you know that I don't write
code. That doesn't mean that I can't read it though.

I think you are home free with your last version of Private Sub
cmbNextQuestion_Click()

Unless things have changed over the years, EOF is a special mark at the
absolute end of a file. (If you use the old DOS debug command to find the
number of bytes in a file, it looks for the EOF mark to know when to stop
counting.) If your pointer is at the last Question, that probable is not the
end of the file. The EOF mark is probably after the '?' mark of the last
question.

Looking for the last question number as you do in your latest sub would seem
the better approach.

There does appear that the line of code before 'IfCurrQ <> MaxQues Then'
contains a period (.) as the only entry. I have no idea if that could cause
problems or not. On second look. I'll bet it is just line wrap in my reader.

Chuck
Click to expand...

Hi Chuck
Thanks for having a look. - no, there wasn't a spare dot in the CurrQ line
(although if it had, I wouldn't be surprised if I had missed it). I wonder
if my rather awkward structure had an ill effect. I wanted to link my
Question subform as well as my answer subformto PersID in the main form so I
tried a db structure where I had TblPersonQuestion and TblPersonAnswer

So I had a full circle in my relationships with the Questions Table being
linked to the Answers Table and also in this convoluted mess. It just seemed
a good idea at the time!! I could update queries directly but the forms were
horrible.

I wonder if recordset code is extra picky about that kind of thing or if it
was all down to when it considered that the forms were actually open for it
to read.
I'm still using bits of it - for updating records in the subform for the
'multi-choice but one answer only allowed' type of question but it's
behaving itself OK so far.

Evi
Click to expand...
 
Chuck said:
Is there an error message? While the code is stepping through its commands, if
"the code never gets to this next bit", it's got to go somewhere, where does it
go? Why? Can you step through the code one line at a time to see exactly what
is happening at every line? The error may very well be before "the code never
gets to this next bit" but doesn't have any effect until several lines later.

Could the circular reference from one form to another be the problem? Possibly
the code never 'sees' an EOF. Can you insert an "emergency break out" in the
'circle' to help find the problem. Can you make a data dump to a text file
that may indicate where things 'aren't quite right'?

Is it possible to make a 'canned' input for which you know what the output
should be and compare to what the output is? Maybe this is only good for
number crunching programs.

Chuck
Click to expand...
Thanks Chuck, some good trouble shooting tips there.
Click to expand...
 
Back
Top