Hi All,

I have a series of tables with two fields, LA and IndicatorValue. I
have a recordset that selects this data from one table at a time and
orders it by IndicatorValue (ascending).

Using the RecordCount property, I have calculated how many records are
in the table and stored this as a variable (then used it to update a
summary table).

I now need to be able to look down the table and store the record
number where the LA = "Peterborough". Looking mannually, I can see
for the first table Peterborough is in record 68 - how can I get the
VBA code to look through the table and store this record number as a
variable?

I basically need to know that Peterborough is record X out of Y - and
I know the result for Y already.

I have experimented with absolute position but cant get this to do
what I need ... does anyone have any ideas please?

Many thanks

Laura wrote:

Maybe this will help, Maybe not. Cut and paste into a module, change
the table "test" name in strSQL to a table name you use.
Sub Junk()
Dim strSQL As String
Dim rst As Recordset

strSQL = "SELECT test.* FROM test;"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

'move so we can get an accurate recordcount
rst.MoveLast
rst.MoveFirst

MsgBox "There are " & rst.RecordCount & " Records."

'since I'm on the first record, move 6 records to the 7th.
rst.Move 6

'Fields(0) is the first field name of this table/query. Inits at 0.
'the first record's absoluteposition is 0. So add 1 to get the
'actual record
MsgBox "Field1 is " & rst.Fields(0) & vbNewLine & _
" Field1 is " & rst.Fields(0).Name & vbNewLine & _
" and the absolute postion is " & rst.AbsolutePosition & vbNewLine & _
" and this is the " & rst.AbsolutePosition + 1 & " record."

rst.Close
Set rst = Nothing

MsgBox "Done"
End Sub

Laura wrote:
How are the records ordered so that Petersborough is in record 68? Is there
an incrementing ID field?

"Record number" is a concept in some database software, but not in Access
nor in relational database theory, so attempting to use it can be
frustrating. "Absolute position" applies only to the open recordset, which
may not be in the same order in which the table is stored, and that is
likely why you had poor luck trying to use it.

It is quite possible that there is a simple, effective approach to
accomplishing the result you desire other than the one you are trying. If
you'll explain what you are trying to _do_, rather than _how_ you intended
to do it, someone may be able to be of even more help than giving you some
VBA code.

For example, you could use a query with criteria of "Where [LA] =
'Peterborough'" to return a single record on which you could perform
calculations, etc. One of the great advantages of using database software
is that you don't have to do as much (and maybe no) traversing of all the
records in recordsets.

If you have used an Autonumber as record ID, don't feel that is the
equivalent of Record Number, even though it may appear so, because it may
NOT always be a monotonically increasing value; but, its intent is to
provide a unique id value for a record, so might be what you need to use
instead of "record number".

Give us enough information and someone is very likely to be able to help.

Larry Linson
Microsoft Office Access MVP


"Laura" <laurajayne.cozens (AT) peterborough (DOT) gov.uk> wrote

Access Developer wrote:
Right. I was intending to suggest something like

select count(*) from table where autonumberfield <=
autonumberfield_of_Peterborough_record

but I didn't want to go there without more details