Bring up record according to list box selection

[dlewicki]

Private Sub List47_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone


rs.FindFirst "[id] = " & Str(Nz(Me![List47], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


I have a list box on a form. And when I click a row in the list box, I want the other fields of the form to populate according to the data in the list box. I have tried the above code and it does not work. What should I check for? Thanks, Dave

[pbaldy]

Define "does not work". Error? Incorrect result? What is the data type of ID?

[Stressed]

Private Sub List47_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[id] = " & Str(Nz(Me!
[List47], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


I have a list box on a form. And when I click a row in the list box, I want the other fields of the form to populate according to the data in the list box. I have tried the above code and it does not work. What should I check for? Thanks, Dave

Use the column() function.

Lets say you have 2 text boxes. txtName and txtPhoneNUMBER and your list box is called lstSelection. Your table is CONTACTS

I always modify the ROWSOURCE for the list box to use a query (click the ... beside the Rowsource field)

The ROWSOURCE once you closed (not saved) the query builder, your SQL should like something like (SELECT CONTACS.NAME, CONTACTS.PHONE_NUMBER FROM CONTACTS)

Select the table then double click to add each table field in your query. The counting starts at 0 meaning that NAME = 0 and PHONE_NUMBER = 1

In the on Dbl Click event of the list box, use the following code:

txtName = lstSelection.column(0)
txtPhoneNUMBER = lstSelection.column(1)

Repeat this for each field that you wish to populate.

Every time the user double clicks a record in the listbox it will populate the fields on the form.

[dlewicki]

Define "does not work". Error? Incorrect result? What is the data type of ID?

Pbaldy - the existing data in the fields of the form do not change. That's what I mean by does not work... sorry that I wasn't more clear. There is no error message at all.

[pbaldy]

What is the data type of the ID field? Is that the bound column of the listbox?