Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

rs.MoveFirst doesn't seem to be working...

Status
Not open for further replies.

irethedo

Technical User
Feb 8, 2005
429
US
I have the following Recordset code and when I step through with my first pass of the code
with the debugger, I expect to see the rs!SPNote of the first record ("012434")
but the watch displays the variable for rs!SPNote = "10193" which is the value of rs!SPNote of
the second record.

What am I doing wrong?

Thanks

Code:
    Dim rs As dao.Recordset
    Dim LineNum As Integer
    Dim newSPNote As String
    Dim oldSPNote As String

    LineNum = 0
    oldSPNote = "-"
    newSPNote = "-"
   
    strSql = "Select * from SPnoteOrder_tbl order by LineNO"
    Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
   
   If Not (rs.BOF And rs.EOF) Then
        rs.MoveFirst    ' Get first record in Table
    End If
   
    Do While Not rs.EOF

      newSPNote = rs!SPNote     ' grab SPNote from record
 
Hi,

I'd do it this way...
Code:
'
    Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
   
    On Error Resume Next

    rs.MoveFirst

    If err.Number = 0 then
       Do While Not rs.EOF

           newSPNote = rs!SPNote     ' grab SPNote from record 
       '''''
    Else
       'do something if an error
    End If
   
/code]

Skip,
[sub]
[glasses]Just traded in my [b]OLD subtlety[/b]...
for a [b]NUance![/b][tongue][/sub]
 
Thanks Skip-

I tried your code and at the first instance of the line:

newSPNote = rs!SPNote ' grab SPNote from record

I am still seeing the watch variable for rs!SPNote as "10193"

The Recordset is pointed to my table SPnoteOrder_tbl which has the following
(first 3 records) values for SPNote:

SPNote
012434
10193
015968

Code:
   Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

   On Error Resume Next

   rs.MoveFirst

   If Err.Number = 0 Then
   Do While Not rs.EOF

    newSPNote = rs!SPNote ' grab SPNote from record

Not sure what I am doing wrong...
 
Ok, after dumping the Select * from SPnoteOrder_tbl order by LineNO
into a query I realize that this is not displaying the records in the same order as they appear In the table...
 
Why do you think that anything is wrong? In your db, what is the LineNO for those "first 3 records?"

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Why do you think that anything is wrong? In your db, what is the LineNO for those "first 3 records?"

Not anymore... All LineNO were 11. The query was just displaying them in an different order than
these records were displayed in my table which threw me off when stepping through the code in the debugger...

after dumping the Select * from SPnoteOrder_tbl order by LineNO into a queryI realize that this is not displaying the records in the same order as they appear In the table...

Thanks for your help Skip
 
I tried following this up with the following query that works as a standalone query to remove
unwanted records:

DELETE SPnoteOrder_tbl.LineNO, *
FROM SPnoteOrder_tbl
WHERE (((SPnoteOrder_tbl.LineNO)="0"));

But I would like to run this from my function as a strSql, but so far haven't found the
correct syntax for this in the strSql statement.

 
What VBA did you try that did not work?

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
By itself, this query works and removes all of the records where LineNO = 0:
Code:
DELETE SPnoteOrder_tbl.LineNO, *
 FROM SPnoteOrder_tbl
 WHERE (((SPnoteOrder_tbl.LineNO)="0"));

but within my VBA, I do not get the same results with the following as all of the records where LineNO = 0 are still in the table after running this:

Code:
 strSql = "DELETE SPnoteOrder_tbl.LineNO, * FROM SPnoteOrder_tbl WHERE SPnoteOrder_tbl.LineNO =0 "
    CurrentDb.Execute strSql

I am thinking that I have the syntax incorrect in the VBA...
 
It appears lineNo is a string
((SPnoteOrder_tbl.LineNO)="0"))
So your VBA is
"DELETE * FROM SPnoteOrder_tbl WHERE SPnoteOrder_tbl.LineNO = '0' "
Also you do not need to call out a field and then *
 
Because LineNO, according to the SQL that runs, is a TEXT field, you must supply a TEXT value in the criteria...
Code:
...Where LineNO = '0'"

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top