ListBox Instead of TextBox

[OOzy]

I have a table as

ID 1,2,3
Name John, Mike, Bill
Age, 22, 12, 34

The ID field is bounded to a list box. I would like to do the following:

When I click on any ID on the listbox the table should go to that record, i.e. if I click on ID 2, the form should show Mike and 12.

 

[TheAceMan1]

How are ya OOzy . . . .

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

In the code below, you substitute proper names in [purple]purple[/purple]:

[blue]   Dim rst As DAO.Recordset
   
   Set rst = Me.RecordsetClone
   
   rst.FindFirst "[[purple][b]IDFieldName[/b][/purple]] = " & Me![purple][b]ListBoxName[/b][/purple]
   Me.Bookmark = rst.Bookmark
   
   set rst = Nothing[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

OOzy . . . . .

My prior post was for form with table recordsource. Are you strictly interested in table only?

See Ya! . . . . . .

 

[TheAceMan1]

OOzy . . . . .

I may have bee a little premature. My prior post was for form with table recordsource.

Are you strictly interested in table only?

See Ya! . . . . . .

 

[hkaing79]

Have you thought about using a combobox instead of a listbox?

 

[Jduenyas]

Here you go Oozy:

This uses ADO so you need to have set your reference table to an ADO Library Object

Private Sub List1_Click()

Dim rstRst as ADODB.Recordset

If Not IsNull(List1) And List1.Column(0) <> "" Then

Set rstRST = New ADODB.Recordset
With rstRST
.ActiveConnection = CurrentProject.Connection
.Open "SELECT NAME, AGE FROM <YOURTABLENAME>" & _
"WHERE ID = " & List1.Column(0)

Text1.Value = rstRST!ID
Text2.Value = rstRST!NAME
Text3.Value = mrstRST!AGE
.Close
End With
Set rstRST = Nothing

End if

End sub



Good luck

Josh