INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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 

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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...

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

(OP)
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...

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

Why do you think that anything is wrong? In your db, what is the LineNO for those "first 3 records?"

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)

Quote:

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...

Quote:

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

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

🤓

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

What VBA did you try that did not work?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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...

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

It appears lineNo is a string

Quote:

((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 *

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
Thank you MajP & Skip!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close