Go to a record based on a table field.

B

BT

Hello,

I am trying to create a button that will take the user to the record they
specify in a text box. The will know what the primary key, but not the record
number. How can I retrieve the record number based on a tables primary key
field? or if there is another way please let me know.

regards,
Brian
 
B

Barry Gilbert

It depends on what you mean by "record number". Do you mean the position in
the table? Typically, you would use the primary key to go to the desired
record. That or a where clause based on one or more fields.

Barry
 
B

BT

Yes the position in the table. Basically the number that appears at the
bottom of the form. I know how to open a form based on a where
statement.(doCmd.OpenForm). I just can't seem to figure out how to move to a
record based on a where statement. If I use the OpenForm, then the user can't
continue to navigate through the records.

thanks,
Brian
 
B

Barry Gilbert

BT said:
Yes the position in the table. Basically the number that appears at the
bottom of the form. I know how to open a form based on a where
statement.(doCmd.OpenForm). I just can't seem to figure out how to move to a
record based on a where statement. If I use the OpenForm, then the user can't
continue to navigate through the records.
Click to expand...

What about DoCmd.FindRecord? It allows you to find a record based on criteria.

Barry
 
B

Barry Gilbert

BT said:
Yes the position in the table. Basically the number that appears at the
bottom of the form. I know how to open a form based on a where
statement.(doCmd.OpenForm). I just can't seem to figure out how to move to a
record based on a where statement. If I use the OpenForm, then the user can't
continue to navigate through the records.
Click to expand...

What about DoCmd.FindRecord? It allows you to find a record based on criteria.

Barry
 
B

BT

Unfortunately the FindRecord method only searches the current field and won't
work for my intended purpose. If is there a property of a recordset that
returns the record number based on a SQL statement? Sort of like .RecordCount
returns the total.

If I had that, I could just use DoCmd.Goto.

Thanks again for responding
 
B

Barry Gilbert

Recordsets expose an AbsolutePosition property. This might work woth
GoToRecord.

Barry
 
B

BT

Thank you. That's what I needed to know. I finally got it to work by looping
through the record set until it reached the designated record, based on the
table's field. It took me awhile to figure out I needed to base the recordset
on a SQL statement with an Order By clause, rather then just the table.

regards,
Brian
 
Top