Hi there,

I would like to return the record number (row position) of a record in a table, using its private key (no duplicates) by querying it in a form. Is there a query (sql or VB) that can do this?

I intend on using the record number to navigate/Jump to the respective record within a form. I'm using a (selected) list of records from the table in question in a navigation box. Clicking on a record in the box, would cause the fields in the form to jump to that record. I was using;

Private Sub List20_AfterUpdate()
Dim Current As Integer
Current = Me!List20.Value
DoCmd.GoToRecord acDataForm, "frmResultInput", acGoTo, Current
End Sub

List20 was bound to the private key in the table, which at one point was equivalent to its row position...

cheers

Hi nags,
You may wish to describe your issue a little further. Tables do *not* have any order!!!. They are just a bucket of records handed back to you in an order that Jet determines to be the quickest, which can be different every time. You would need to use a query with an ORDER BY clause to have the records presented in a particular sequence. Also, I'm not sure what you mean by private key. Maybe you mean the Primary Key??

Sorry, I had thrown that post together on my way out.
You are correct, the primary key is what I was referring to, i'm very much a hack at access and clearly it shows!

Essentially, I am trying to use a list box to select some related records within a form. I have 'next' and 'previous' buttons built in, and wanted these to operate in a consistent manner, with the order of the records in the list box.

I have since sorted it out by using the FindRecord function in VBA and all seems to hold together OK.

Glad to hear you got it sorted. Thanks for posting back.