Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SupaHoop
Starting Member
8 Posts |
 Posted - 2004-04-27 : 05:00:50
|
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 = 1Me.Recordset.MoveFirstDo Until Me.Recordset.Fields("Trusskey") = gblTrussKeySave Debug.Print "RecCount = " & intreccount & " Trusskey = " & Me.Recordset.Fields("Trusskey") & " EOF = " & Me.Recordset.EOF Me.Recordset.MoveNext intreccount = intreccount + 1Loop[/CODE] 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[/CODE] and it's set to TRUE. I can also display [CODE]me.recordset.recordcount[/CODE] 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?____QPR's Blue & White blood runs through the veins.RTID |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-04-28 : 04:54:00
|
| where do you set gblTrussKeySave?? |
 |
|
|
SupaHoop
Starting Member
8 Posts |
Posted - 2004-04-28 : 06:08:29
|
| gblTrussKeySave is a glabal variable available to my whole ADP and is set in another form that calls the form I am having problemsw with. Basically the user views a form with all the trusses for a job and can then click on one of them to view the details. So in the main screen I set the gblTrussKeySave to the value of the TrussKey they wish to view.____QPR's Blue & White blood runs through the veins.RTID |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-04-28 : 06:55:30
|
| Ok, then why bother looping through the recordset at all? Why not just use the Find method on your recordset? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-04-28 : 10:28:52
|
| Why not include it in the query so that you return a single record.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
False EOF Marker