Looking up records with a combo box?

[chestyxbond]

How can I make it so that selecting an item from a combo box will bring up the entire record in a form?

For instance, there is a combo box at the top of a customers form that says "Name," and gets its list of values from the field "Name" in the customers table. How can I make it so that when you select "John Doe" from the combo box, the form jumps to John Doe's record, where I can edit his information?

Please respond if you need more clarification.

Thanks,
Daniel

 

[jebry]

Hi Daniel!

You can use the following code in the combo boxes change or before update event procedure:

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst (YourNameField = '" & YourComboBox.Text & "'"

Me.BookMark = rst.BookMark

Set rst = Nothing

One more thing you need to know. Make the combo box unbound and display the name in a text box on the form it the user may need to change it at any time.

Another thought, is the name a primary key? If it isn't then you will need to do a little more with the box, unless you want all the choices which match the name to be returned.

hth
Jeff Bridgham

 

[chestyxbond]

Didn't quite work. The Name field is not the primary key, though it should be different for every record. Good idea about displaying the name in a text box, I'll definitely do that when I get it all working.

Anyway, this is exactly what I pasted in to the Change event (I also tried it in the Before Update event and got the same errors). The name field's name is investors.Investor Name (the form is linked to a query with multiple tables), and the combo box name is NameCombo. If you need any other info to figure out what's going on, just let me know what it is.

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.FindFirst (investors.Investor Name = '" & NameCombo.Text & "'"

Me.Bookmark = rst.Bookmark

Set rst = Nothing


When I move the cursor off the code I get an error that says "Unexpected list separator or )" What did I do wrong?

Thanks for all the help,

Daniel

 

[jebry]

Hi again!

Nothing you did wrong, I did a typo. The open parentheses should be a double quote. Sorry X-)

Jeff Bridgham