reading a foxpro table using the record number (in VB6)

[AnthonyWhitehouse]

Greetings
A process at our site stores a record number.
Can anyone help me with some VB6 code to read from a foxpro table using a record number?
For example - the process stores 35 - which I can see in foxpro is the 35th record in my table.

I have tried the following - but it gets a record that is 40th in foxpro???
Set RsAccount = dbsCompany.OpenRecordset("Account")
RsAccount.MoveFirst
RsAccount.Move (35)

I have tried the following - it gave "undefined function RECNO
strSQL = "Select accountno from account where RECNO() = 35"
Set RsLookup = dbsCompany.OpenRecordset(strSQL, dbOpenForwardOnly)

 

[Andrzejek]

Records in a table are not in any order, they are like marbles on the back of the truck. You have to put them in order when you ask for records.
So if you say: I want 35th record, you need to specify what makes this record the 35th one? It will be a different record depending what you use in ORDER BY part of your Select statement.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[tedsmith]

If you have a field with a unique ID number (like an order number) you can select by this
MyAccount = "12345"
strSQL"Select * from account where accountno = "& MyAccount & " " (if it is a number field or accountno = '"& MyAccount & "'" if text)

 

[tedsmith]

Whatever you move or do in RSAccount won't affect RSLookup.

Or if you really want the 35th record (assuming you have a numerical field recno with the number 35 in it
Dont move it by 35 to start.
strSQL="Select * from account where recno = 35"

If you havent got a field with the recno in it
StrSQL "account"
Set RsLookup = dbsCompany.OpenRecordset(strSQL, dbOpenSnapshot)
RSLookup.Move(35)
The fields then shown will be the 35th field.This will change if any earlier reecords are deleted som it is not a good idea to do anyway.

 

[AnthonyWhitehouse]

Thanks Tedsmith
I managed to resolve this just before your post in the way that you suggested - similar to what I tried before posting the issue - so not quite sure what I did wrong the first time.

Anyway to complete the thread - this is what I did.

If IsNumeric(strTemp) Then
Set RsLookup = dbsCompany.OpenRecordset("Account", dbOpenSnapshot)
RsLookup.MoveFirst
RsLookup.Move (strTemp - 1)
scrNewAccountId.Text = RsLookup!Accountno
End If