Prblems with EOF Marker

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a problem with a simple loop through a recordset.
I am using MS Access 2000 to link to a SQL 2000 database.
I have a simple select query that I know returns 86
records. However when I try and iterate through this
recordset to try and find a certain record, Access is
displaying an EOF error after iterating through just 50
records. The code I use is extremely simple:

Code: 
Me.RecordSource = "SELECT * FROM Trusses WHERE
Quantity_Build > 0 AND JobIndex = " & Me.JobIndex & "
Order by MarkOrder"
intreccount = 1
Me.Recordset.MoveFirst
Do Until Me.Recordset.Fields("Trusskey") = gblTrussKeySave
Debug.Print "RecCount = " & intreccount & " Trusskey
= " & Me.Recordset.Fields("Trusskey") & " EOF = " &
Me.Recordset.EOF
Me.Recordset.MoveNext
intreccount = intreccount + 1
Loop

As you can see I have put a counter in the loop and a
debug.print so that I know exactly where in the loop I
have reached when this EOF error comes up. If I chose to
debug, I can display
Code: 
me.recordset.eof
and
it's set to TRUE. I can also display
Code: 
me.recordset.recordcount
and this is correctly set
to 86. If I display the value of the intRecCount variable
it is 51. The last debug.print line displayed in the
debug window shows:
"RecCount = 50 Trusskey = 41820 EOF = False". The
maxrecords property to 1000.

If I click on f8 to continue debugging, the loops carries
on correctly until it finds the record I am searching
for. It's as if the .EOF marker has been reset?

This is driving me nuts - has anyone come across this
sort of problem before?
Any idea's as to what could be causing Access to rasie
this EOF marker before the end of the recordset?
 
I have a problem with a simple loop through a recordset.
I am using MS Access 2000 to link to a SQL 2000 database.
I have a simple select query that I know returns 86
records. However when I try and iterate through this
recordset to try and find a certain record, Access is
displaying an EOF error after iterating through just 50
records. The code I use is extremely simple:

Code: 
Me.RecordSource = "SELECT * FROM Trusses WHERE
Quantity_Build > 0 AND JobIndex = " & Me.JobIndex & "
Order by MarkOrder"
intreccount = 1
Me.Recordset.MoveFirst
Do Until Me.Recordset.Fields("Trusskey") = gblTrussKeySave
Debug.Print "RecCount = " & intreccount & " Trusskey
= " & Me.Recordset.Fields("Trusskey") & " EOF = " &
Me.Recordset.EOF
Me.Recordset.MoveNext
intreccount = intreccount + 1
Loop

As you can see I have put a counter in the loop and a
debug.print so that I know exactly where in the loop I
have reached when this EOF error comes up. If I chose to
debug, I can display
Code: 
me.recordset.eof
and
it's set to TRUE. I can also display
Code: 
me.recordset.recordcount
and this is correctly set
to 86. If I display the value of the intRecCount variable
it is 51. The last debug.print line displayed in the
debug window shows:
"RecCount = 50 Trusskey = 41820 EOF = False". The
maxrecords property to 1000.

If I click on f8 to continue debugging, the loops carries
on correctly until it finds the record I am searching
for. It's as if the .EOF marker has been reset?

This is driving me nuts - has anyone come across this
sort of problem before?
Any idea's as to what could be causing Access to rasie
this EOF marker before the end of the recordset?
Click to expand...

This a post I made recently to Comp Databases MS-Access.

****
When an ADP loads a continuous form it goes to the server and gets 50
records for its initial show. Later when everything is rendered, it goes
back and gets the rest of the records.

If one wants to run a traditional, search on recordset on open (so that we
can set the bookmark of the form to that record) for a record which might
be greater then 50 in the order the form loads its record, the search will
fail.
It’s not because the record isn't in the record set, it's because the form
has not yet loaded all the records, (just the first fifty), and so records
from 51 on up cannot be found.

I don't know if this happens all the time, but I do know it happens some of
the time.

My current solution;

I tried various things, including building in a pause with the form's
timer, and examining the form's recordset to wait until it was finished
fetching or executing (that is waiting until its State OR adStateOpen =
adStateOpen) before searching for my record.
I could make these work but they both resulted in a noticeable pause and a
noticeable screen flash.
So, I opened a recordset in the form's open or load event, found the record
I wanted and set the form's recordset to that recordset.
For a small recordset it's instantaneous, and there is no screen flash.
There is no need to set book marks. If the created recordset is at record #
56 (however 56 is defined) and the form's recordset is set to that
recordset then the form will open there.

example (you will have your own style for opening an adodb recordset; this
is mine)

Private Sub Form_Open(Cancel As Integer)
Dim c As ADODB.Connection
Dim m As ADODB.Command
Dim r As ADODB.Recordset

Set c = New ADODB.Connection
With c
.ConnectionString = "PROVIDER=SQLOLEDB.1;" _
& "PERSIST SECURITY INFO=FALSE;" _
& "INITIAL CATALOG=Some Database;" _
& "DATA SOURCE=Some Data Source;" _
& "USER ID=Some User;" _
& "PASSWORD=Some Password"
.CursorLocation = adUseClient
.Open
End With

Set m = New ADODB.Command
With m
.ActiveConnection = c
.CommandType = adCmdStoredProc
.CommandText = "spGet4060148Transactions"
Set r = .Execute()
End With

With r
.Find "TransactionID = 56"
If .EOF Then .MoveFirst
End With

Set Me.Recordset = r

End Sub
****
 
-----Original Message-----
[email protected]:

I have a problem with a simple loop through a recordset.
I am using MS Access 2000 to link to a SQL 2000 database.
I have a simple select query that I know returns 86
records. However when I try and iterate through this
recordset to try and find a certain record, Access is
displaying an EOF error after iterating through just 50
records. The code I use is extremely simple:

Code: 
Me.RecordSource = "SELECT * FROM Trusses WHERE
Quantity_Build > 0 AND JobIndex = " & Me.JobIndex & "
Order by MarkOrder"
intreccount = 1
Me.Recordset.MoveFirst
Do Until Me.Recordset.Fields("Trusskey") = gblTrussKeySave
Debug.Print "RecCount = " & intreccount & " Trusskey
= " & Me.Recordset.Fields("Trusskey") & " EOF = " &
Me.Recordset.EOF
Me.Recordset.MoveNext
intreccount = intreccount + 1
Loop

As you can see I have put a counter in the loop and a
debug.print so that I know exactly where in the loop I
have reached when this EOF error comes up. If I chose to
debug, I can display
Code: 
me.recordset.eof
and
it's set to TRUE. I can also display
Code: 
me.recordset.recordcount
and this is correctly set
to 86. If I display the value of the intRecCount variable
it is 51. The last debug.print line displayed in the
debug window shows:
"RecCount = 50 Trusskey = 41820 EOF = False". The
maxrecords property to 1000.

If I click on f8 to continue debugging, the loops carries
on correctly until it finds the record I am searching
for. It's as if the .EOF marker has been reset?

This is driving me nuts - has anyone come across this
sort of problem before?
Any idea's as to what could be causing Access to rasie
this EOF marker before the end of the recordset?
Click to expand...

This a post I made recently to Comp Databases MS-Access.

****
When an ADP loads a continuous form it goes to the server and gets 50
records for its initial show. Later when everything is rendered, it goes
back and gets the rest of the records.

If one wants to run a traditional, search on recordset on open (so that we
can set the bookmark of the form to that record) for a record which might
be greater then 50 in the order the form loads its record, the search will
fail.
It's not because the record isn't in the record set, it's because the form
has not yet loaded all the records, (just the first fifty), and so records
from 51 on up cannot be found.

I don't know if this happens all the time, but I do know it happens some of
the time.

My current solution;

I tried various things, including building in a pause with the form's
timer, and examining the form's recordset to wait until it was finished
fetching or executing (that is waiting until its State OR adStateOpen =
adStateOpen) before searching for my record.
I could make these work but they both resulted in a noticeable pause and a
noticeable screen flash.
So, I opened a recordset in the form's open or load event, found the record
I wanted and set the form's recordset to that recordset.
For a small recordset it's instantaneous, and there is no screen flash.
There is no need to set book marks. If the created recordset is at record #
56 (however 56 is defined) and the form's recordset is set to that
recordset then the form will open there.

example (you will have your own style for opening an adodb recordset; this
is mine)

Private Sub Form_Open(Cancel As Integer)
Dim c As ADODB.Connection
Dim m As ADODB.Command
Dim r As ADODB.Recordset

Set c = New ADODB.Connection
With c
.ConnectionString = "PROVIDER=SQLOLEDB.1;" _
& "PERSIST SECURITY INFO=FALSE;" _
& "INITIAL CATALOG=Some Database;" _
& "DATA SOURCE=Some Data Source;" _
& "USER ID=Some User;" _
& "PASSWORD=Some Password"
.CursorLocation = adUseClient
.Open
End With

Set m = New ADODB.Command
With m
.ActiveConnection = c
.CommandType = adCmdStoredProc
.CommandText = "spGet4060148Transactions"
Set r = .Execute()
End With

With r
.Find "TransactionID = 56"
If .EOF Then .MoveFirst
End With

Set Me.Recordset = r

End Sub
****
Click to expand...
Many thanks for this Lyle, it's good to know that it's
not something I'm doing, but a problem with the way
Access retrieves records.

However, I am trying to display a Single Form with
navigation buttons, so as well as displaying the
particular record chosen by the user, I also need to show
the full recordset.

Unless I am mistaken, and please don't hesitate to
correct me as I often am, your solution would only
display a sinlge record in this form and the navigation
buttons would be redundant?
 
-----Original Message-----
[email protected]:

I have a problem with a simple loop through a recordset.
I am using MS Access 2000 to link to a SQL 2000 database.
I have a simple select query that I know returns 86
records. However when I try and iterate through this
recordset to try and find a certain record, Access is
displaying an EOF error after iterating through just 50
records. The code I use is extremely simple:

Code: 
Me.RecordSource = "SELECT * FROM Trusses WHERE
Quantity_Build > 0 AND JobIndex = " & Me.JobIndex & "
Order by MarkOrder"
intreccount = 1
Me.Recordset.MoveFirst
Do Until Me.Recordset.Fields("Trusskey") = gblTrussKeySave
Debug.Print "RecCount = " & intreccount & " Trusskey
= " & Me.Recordset.Fields("Trusskey") & " EOF = " &
Me.Recordset.EOF
Me.Recordset.MoveNext
intreccount = intreccount + 1
Loop

As you can see I have put a counter in the loop and a
debug.print so that I know exactly where in the loop I
have reached when this EOF error comes up. If I chose to
debug, I can display
Code: 
me.recordset.eof
and
it's set to TRUE. I can also display
Code: 
me.recordset.recordcount
and this is correctly set
to 86. If I display the value of the intRecCount variable
it is 51. The last debug.print line displayed in the
debug window shows:
"RecCount = 50 Trusskey = 41820 EOF = False". The
maxrecords property to 1000.

If I click on f8 to continue debugging, the loops carries
on correctly until it finds the record I am searching
for. It's as if the .EOF marker has been reset?

This is driving me nuts - has anyone come across this
sort of problem before?
Any idea's as to what could be causing Access to rasie
this EOF marker before the end of the recordset?
Click to expand...

This a post I made recently to Comp Databases MS-Access.

****
When an ADP loads a continuous form it goes to the server and gets 50
records for its initial show. Later when everything is rendered, it goes
back and gets the rest of the records.

If one wants to run a traditional, search on recordset on open (so that we
can set the bookmark of the form to that record) for a record which might
be greater then 50 in the order the form loads its record, the search will
fail.
It's not because the record isn't in the record set, it's because the form
has not yet loaded all the records, (just the first fifty), and so records
from 51 on up cannot be found.

I don't know if this happens all the time, but I do know it happens some of
the time.

My current solution;

I tried various things, including building in a pause with the form's
timer, and examining the form's recordset to wait until it was finished
fetching or executing (that is waiting until its State OR adStateOpen =
adStateOpen) before searching for my record.
I could make these work but they both resulted in a noticeable pause and a
noticeable screen flash.
So, I opened a recordset in the form's open or load event, found the record
I wanted and set the form's recordset to that recordset.
For a small recordset it's instantaneous, and there is no screen flash.
There is no need to set book marks. If the created recordset is at record #
56 (however 56 is defined) and the form's recordset is set to that
recordset then the form will open there.

example (you will have your own style for opening an adodb recordset; this
is mine)

Private Sub Form_Open(Cancel As Integer)
Dim c As ADODB.Connection
Dim m As ADODB.Command
Dim r As ADODB.Recordset

Set c = New ADODB.Connection
With c
.ConnectionString = "PROVIDER=SQLOLEDB.1;" _
& "PERSIST SECURITY INFO=FALSE;" _
& "INITIAL CATALOG=Some Database;" _
& "DATA SOURCE=Some Data Source;" _
& "USER ID=Some User;" _
& "PASSWORD=Some Password"
.CursorLocation = adUseClient
.Open
End With

Set m = New ADODB.Command
With m
.ActiveConnection = c
.CommandType = adCmdStoredProc
.CommandText = "spGet4060148Transactions"
Set r = .Execute()
End With

With r
.Find "TransactionID = 56"
If .EOF Then .MoveFirst
End With

Set Me.Recordset = r

End Sub
****
Click to expand...
Many thanks for this Lyle, it's good to know that it's
not something I'm doing, but a problem with the way
Access retrieves records.

However, I am trying to display a Single Form with
navigation buttons, so as well as displaying the
particular record chosen by the user, I also need to show
the full recordset.

Unless I am mistaken, and please don't hesitate to
correct me as I often am, your solution would only
display a sinlge record in this form and the navigation
buttons would be redundant?
Click to expand...

For me it displays all the records; the top visible record is the found
record. One can scroll up, or use the navigation buttons to move to the
previous records, or to those below.
 
Back
Top