retrieve one value from Listbox

[Alkaline]

I have a listbox that uses data from a query. The query contains the names of people and their ID. I have the listbox only displaying the name. When I select an item in the list box, I want to use the AfterUpdate() in VBA to change the current recordset to the ID of the person just selected. Users are not able to select more than one item in the listbox

Here is some code which I got from one of the example databases on this forum

Private Sub List26_AfterUpdate()

Dim Cursisten As String
Dim ctl As Control
Dim Itm As Variant

Set ctl = Me.List2

For Each Itm In ctl.ItemsSelected
If Len(Cursisten) = 0 Then
Cursisten = ctl.ItemData(Itm)
Else
Cursisten = Cursisten & "," & ctl.ItemData(Itm)
End If
Next Itm
Me.txtCursisten = Cursisten
End Sub

 

[Oldsoftboss]

Use the access wizard to create a listbox and choose the 3rd option - find a record.

 

[Alkaline]

Yes that works, but it needs to use data from a table that is a 'subsection' of the table the form uses. So the ID will come from a different table than the one used by the form, and then use that ID to select the ID in the table that the form is built on.

 

[Oldsoftboss]

After the creation of the listbox, simply edit the source of the listbox. In the properties of the listbox you will find the rowsource property. "Select blah blah" When you click in there a small box on th RHS will be displayed. When you click on that the query editor will open and you can construct your record source just as you would for a query. This will not effect the code, simply give you a different recordsource.

Dave