Link Subform to a list Box (On Click)

[Ports]

Hi,


I have created a search field with a list box/list in a form (search for a learner name), as well as a subform displaying learner details and courses enrolled on.


The search box and search list controls work fine. Once you type some characters in the search box, possible matches appear in the search list (based on Family Name).


What I'd like to accomplish is to link the SearchList to the subform so when you double click on one of the names, the details of that particular learner appear on the right hand side.


I've attached the database file (just a few names with fictional data). Thank you.

 

[theDBguy]

Hi. Can't download your file right now. Are you trying to filter or navigate the records on the subform? is it a single or continuous/datasheet subform?

 

[moke123]

I would use the change event of your searchbox rather than after update.
This will give you a search as you type effect.

Private Sub SearchBox_Change()

    Dim strSql As String

    strSql = "SELECT LearnRefNumber, FamilyName,GivenNames FROM Valid_Learner " & _
             "WHERE FamilyName  Like  ""*" & Me.SearchBox.Text & "*"""

    Me.SearchList.RowSource = strSql

End Sub

to filter the subform use

Private Sub SearchList_AfterUpdate()

    Dim strFilter As String

    strFilter = "LearnRefNumber = """ & Me.SearchList & """"

    Me.frmLearnerDetails.Form.Filter = strFilter

    Me.frmLearnerDetails.Form.FilterOn = True

End Sub

*I Set the subforms recordsource to the table.

 

[arnelgp]

another flavor you may want to taste.

 

[Ports]

Brilliant. Thank you. Both solutions work like a charm. Really like the option to choose what you'd like to search for (eg. ref or name)



In the first solution, was there any reason to change the rowsource from a query to a table? Is one more preferrable to the other?

 

[Dreamweaver]

I use the Link Master and child fields so when a record is selected in he combo/Listbox (Wont matter which) .