I have looked high and low to figure this one out, and all I can think of is that it's so easy that nobody even has to ask about this. Of course I can't figure it out, and so here I ask:

How do you make a control on a form so you can CHOOSE the record that you view instead of just changing the value in the current record for that field? PLEASE someone has to know this. It seems like something that should be right in front of your face built in to Access.

Note that I am trying to select the record based on the primary key field, so there are no duplicates, and the field is indexed.

Thanks in advance!

The combo box wizard can build a combo box with code behind it to navigate to the record chosen in the combo - is that what you want to do? The code runs in the AfterUpdate event of the procedure to "move" to the new row. It gets a copy of the form's RecordsetClone, does a FindFirst on the key value, and if found, assigns the Bookmark of the recordset to the Bookmark of the form to move to that row.


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

Hey,

  If you use the combo box wizard to look for a record in your form, be sure to add an entry to the form's OnCurrent event to synchronize the record on your form with the combo box so that as you scroll through your records, your combo box follows along.

   Add this line to the form's On Current event:

   cboName = RecordID

   where cboName is the name of your combo box, and RecordID is the primary key field name of the records on your form. You need to place the pk on the form, but you can hide it by setting it's properties to Visible/No.

   I also use another method that involves a combo box and button to look up records on a form when I am using a SQL back end or a large Access database on a network in order to keep traffic down. But this wizard works well for most Access apps.


mmcdonal

Hey thanks all! Between the two answers that works exactly as I need it to. I knew that lookup function was sometyhing that I was overlooking.