.Sort and .Seek in the same recordset (ADO) possible?

D

Douglas J. Steele

Care to use the space to explain what you're trying to do, as opposed to
simply posting a subject?
 
M

Mac

I apologize, I accidentally hit the enter key, prior to writing a message and
the question was posted twice! It feels like a Monday. Sorry.

My problem is that I have a .Seek algorithm that is supposed to open a
history (ADO) recordset, find the most recent entry for a particular serial
number, grab a field value and then update the same field in a different
recordset for the same serial number. The history maintains the number of
times a piece of equipment has been used and increments each time a entry is
made in the history table. For this reason when the main table is viewed on a
data entry form the "usage count" must be calculated dynamically being
"pulled" from the history table. My .Seek seems to be working properly but
because the history recordset seems to open by default as sorted ascending by
my indexed field, the serial number, the records are not in the order in
which they were created.

My idea was to use a .Seek, as I have done for many other subs in this
program, to find the last entry in the history for a seral number by using
rsHistory.Seek rsMainData("Serial Number"), adSeekLastEQ. This should find
the last instance of that serial number in the history recordset. BUT because
the history recordset is not sorted by the autonumber field (Entry #) the
..Seek is not returning the desired values.

I attempted to just add a simple .Sort by Entry # on the histry recordset
but I keep getting errors like "Current provider does not support the
necessary interface for Index functionality".

I tried a .Sort and a mySQL ORDER BY with the same results.

My tables are local, I have the Serial Number set to Indexed YES duplicates
allowed.

I have had a few responses with alternate methods to do what I need to do
but have not had success with those. I still yet have not had a clear
response from any contributor about my actual question regarding how to do a
..Sort and then a .Seek in the same ADO recordset. Any ideas? Any help is
greatly appreciated.
 
P

Pieter Wijnen

SELECT COUNT(*) As Cnt FROM HistoryTable WHERE Serial =theSerial

Will Give you the count

HTH

Pieter
 
B

Brendan Reynolds

I still yet have not had a clear
response from any contributor about my actual question regarding how to do
a
.Sort and then a .Seek in the same ADO recordset.
Click to expand...
<snip>

Well admittedly my response was somewhat buried in the midst of the previous
thread, and I was being a bit cautious about saying a definitive "it can't
be done", but I'm reasonably certain the answer to that specific question
is, in fact, that it can't be done. It appears that Sort is only supported
when the CursorLocation property is set to adUseClient, while Index and Seek
are only supported when the CursorLocation property is set to adUseServer,
which would seen to preclude the possibility of using both with the same
recordset.
 
M

Mac

Thank you, That does make sense. I will concentrate on a different method.
Possibly the method suggested by Pieter.
 
M

Mac

Thank you I will try that.
--
Regards, Michael


Pieter Wijnen said:
SELECT COUNT(*) As Cnt FROM HistoryTable WHERE Serial =theSerial

Will Give you the count

HTH

Pieter
Click to expand...
 
J

John W. Vinson

Thank you, That does make sense. I will concentrate on a different method.
Possibly the method suggested by Pieter.
Click to expand...

Why not just include criteria in the SQL upon which you base the recordset,
rather than opening the whole table and then narrowing down the results? A
Subquery can be used to find the most recent date.

John W. Vinson [MVP]
 
Top