Searching a subform from a textbox on main form

[desjardins]

I have a main form, frm_search, based on a table, tbl_people. The main form has a subform, sfrm_people. All I want to do is have the user type all or part of the person's last name on the main form, and have a list of ALL people with the matching string in the subform. i.e. If there are two people named Smith, they will both appear ON THE SAME DATASHEET. Or if the user types in son, Anderson and Robertson both appear. Currently I have to use record navigation on the main form to get from Jane Smith to Bob Smith. The forms are linked by PersonID.

I have tried this:


Private Sub command7_click()
Dim strfilter As String

If Me.Text8 = "" Then
Me.FilterOn = False
Me.Text8.SetFocus
Exit Sub
End If
strfilter = "LastName Like '*' & '" & Me.Text8 & "' & '*'"
Me.Filter = strfilter
Me.FilterOn = True

End Sub


I tried replacing me.filter with me.sfrm_people.form.filter but then it returns no results at all. Please post the code here, I cannot download files at work.

Thanks in advance!

 

[John Big Booty]

Welcome to the forum.

Perhaps the sample posted here is the sort of thing you are looking for?

 

[desjardins]

Yes, that works great! My ultimate goal was to be able to delete the selected person, so I've combined it with some code for deleting from a listbox. (I'm too new to link to the page where I found the code, apparently.)

I'm still kind of amazed that subforms don't work how I think they should, but the listbox got around it, so all is well.

 

[Mari111]

Hi, I've the solution posted here which works but MS Access 2010 crashes if a user types into the search box at a regular speed, they have to type v_e_r_y slowly. I also want to use a subform instead of a list box.

My code for the form and subform is pretty much exactly the same as Desjardins posted here, and it also isn't working. Anyone know how to fix it? There must be a way using subforms and not just list boxes.

Thanks.
Mari