Lookup a single record and display in a form

[shallman]

Access 101 question from a beginner!

Very simply, I want to create a data entry form that allows lookup of a specific unique key as entered by the user, displays the associated record from the table, and allows a user to edit the data of the record. What I don't know how to do is allow the user to enter a value for the unique index key and retrieve the associated record to display on the form, or if not found, generate a message. For example, enter a customer account number (unique key), look up the customer record, display it and allow the address to be modified.

This seems so simple but I can't find examples or references anywhere. Currently, I have the form with the bound controls and one unbound text control as the first control that allows entry of the key data. I call a macro upon exit of that control, the macro runs OpenQuery to a query on the control data, and Close, but the form wont display the record of the matching key. It always displays the first record of the table.

I am obviously going about this wrong, can anyone offer some insight?

Thanks,
SHallman

 

[jason246day]

create a textbox, and use this code to move records. Are you using a command button to submit the data? If so, put this code in the onClick event of the button.

Set rstForm = Me.RecordsetClone
enteredID = Forms![form_name]![textbox]

Me.RecordsetClone.FindFirst "[UniqueID] = " & enteredID
Me.Bookmark = Me.RecordsetClone.Bookmark

 

[shallman]

Thanks, that worked perfect!

 

[srodolff]

Jason246day,
I am trying to follow your example as I have the same issue. I have just enough Access knowledge to be dangerous.

If I have a Table called (Data) and it contains 25 fields.
I create a new form with a textbox and a submit button.

What code goes into the textbox and what code goes in the button?

I created a form for the selected record to edit.

How do I reference the Data table and the output form?

I keep getting a 7951 error. What am I doing wrong?

Your help is greatly appreciated.

Steve

 

[jason246day]

that code goes into the onClick event of the command button. but you would probably need some error handling too. that way when someone enters an invalid value, they know what they did wrong.