recordset.MovePrevious

  • Thread starter Thread starter newguy
  • Start date Start date
N

newguy

I am currently working on a project that will require me
to compare 2 items in a query based recordset. The items
are in the same field, just 1 record apart. Basically, I
need to compare the 2 values to determine whether or not I
should do one thing or another. For example:

"Query Based Recordset"
VALUE
A
A
B

Value is the name of the column in the recordset. I need
to look at the value, store it as a variable, then look at
the next value, store that, compare the 2, and do
something. This is what I tried to do:

Dim stFirstVal, stSecondVal As String
stFirstVal = rstQryRecordset("Value")
rstQryRecordset.MoveNext
stSecondVal = rstQryRecordset("Value)
rstQryRecordset.MovePrevious

If stFirstVal = stSecondVal THen
"do this and that"
Else
"do that and this"
End If

Everything works fine, except I cannot get the
MovePrevious to work, I get an error that basically says I
cannot do it, and I have no idea why. Please help.
 
Without seeing how you've opened the recordset, it's possible that you've
opened it as Forward-only, in which case, you won't be able to go backwards.

....otherwise, check to make sure you're not at the beginning of the dataset:
If Not rstQueryRecordset.BOF Then rstQryRecordset.MovePrevious

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
You need to cut & paste the exact code you are using - from & including the
declaration of rstQryRecordset, thru & including the final if-test. The code
that you have shown is incomplete, & clearly not a cut & paste (since it
contains at least one syntax error that would stop it running >at all<).

Also, you need to quote the exact text of the error(s) you get, & show what
line(s) they occur on. It is waaay not enough to say, "I get an error that
basically says I cannot do it".

"Value" is not a good name for a field. It is a so-called "resevred word" in
Access. Check out "reserved words" in online help.

HTH,
TC
 
I don't follow. If a MoveNext works, how can an immediately following
MovePrevious fail?

I'm off for 2 days but will see your answer when I return.

Cheers :-)
TC
 
TC,

<< I don't follow. If a MoveNext works, how can an immediately following
MovePrevious fail?>>
Because you can't issue a MovePrevious against a forward-only recordset,
even after having previously issued a MoveNext. Try this:

Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("MSysObjects", dbOpenForwardOnly)

Debug.Print rs.Fields(0)
rs.MoveNext
Debug.Print rs.Fields(0)
rs.MoveNext
Debug.Print rs.Fields(0)
rs.MovePrevious '*********
Debug.Print rs.Fields(0)

On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing

With regard to your previous comment:
<<He can't be at the beginning if the MoveNext worked>>
My mistake - I misunderstood what you were referring to.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Sorry for the delay, I've been busy for a few days.

I understand about forward-only recordsets. But you said: "Without seeing
how you've opened the recordset, it's possible that you've opened it as
Forward-only, in which case, you won't be able to go backwards. Otherwise,
check to make sure you're not at the beginning of the dataset."

My point is, that the "Otherwise" part does not make sense. If he has just
done a successful MoveNext, he can not possibly be at the beginning of the
recordset - foreward-only or otherwise!

That's why I said: "He can't be at the beginning if the MoveNext worked".

Cheers,
TC
 
Back
Top