MoveFirst

  • Thread starter Thread starter George
  • Start date Start date
G

George

I am trying to use the MoveFirst method in VBA. I have
several Access tables with two fields, Age and Rate. I
define a recordset and open the table in VBA. Then I use
the MoveFirst method to go to the start of the table.
Then I use the Move(age) method to move to the desired
row in the table. However, when I assign the value of
the Rate field to a variable, I get a different value in
the table. It appears the index is corrupted, so that
the MoveFirst method does not go to the first record (age
0). How can I fix this? Other tables work fine - when I
want a rate for age 50, the correct value is assigned to
the variable.

Or, is there a better way to index into a recordset/table?

Thanks,
George
 
You are using the move method incorrectly.
Move(50) moves the pointer 50 records down.
in your other tables, it worked OK because you had one record per age
In this table, I suspect, you have more than one record per age, which is
why it may be failing.

try using rst.FindFirst "Age = " &intAge - or similar syntax

HS
 
I'm not sure that using the Move method like that is going to be reliable.
You would have to make sure the recordset was correctly sorted by age (just
opening a table recordset won't necessarily do this). You would also need to
be sure that the table just contained one record for each age, and no ages
missing.

I think it would be better to use the FindFirst method.
 
Thank you for your reply. Just to clarify, my table does
not have any duplicate entries - there is only one record
for each age. When I do the MoveFirst method, it goes to
age 33 for this table. For other tables, it goes to age
0.

My code is simply:
rst.MoveFirst
rst.Move (Age)
myRate = rst("Rate")


The code for your suggestion seems more efficient:
rst.FindFirst "Age = " & Age
myRate = rst("Rate")


Is this correct?
 
yup, that should work

HS

George said:
Thank you for your reply. Just to clarify, my table does
not have any duplicate entries - there is only one record
for each age. When I do the MoveFirst method, it goes to
age 33 for this table. For other tables, it goes to age
0.

My code is simply:
rst.MoveFirst
rst.Move (Age)
myRate = rst("Rate")


The code for your suggestion seems more efficient:
rst.FindFirst "Age = " & Age
myRate = rst("Rate")


Is this correct?
Click to expand...
 
You MUST specify the Ordering when you create the
Recordset (using the ORDER BY Clause) to guarantee that
the rows in the Recordset are in a partcular order. If
you don't specify the ordering, the rows may be random.

If you have a unique index on Age, and the Table is local,
the Seek Method is probably the most effcient. Otherwise,
you should use the FindFirst Method. I never used the
Move Method.

HTH
Van T. Dinh
MVP (Access)
 
Back
Top