Filter subform from text box on main form

[ittechguy]

I'm trying to create a search box that searches through my subform and displays both records from that subform as well as the related records in my main form.



I can easily create a search box that filters records from my main form however fields which need to be searched in my subform are not in my main form's record source.

To create a search box for my main form, I created a text box and a button. I opened up the macro builder and went to apply filter, and typed this: [RoomName] Like "*" & [Forms]![frmMainRooms]![txtSearch] & "*" as my where condition.

To try to make this work with my subform, i changed it to [forms]![frmMainRooms]![frmSubFacilityMgr]![LastName] Like "*" & [Forms]![frmMainRooms]![txtSearch] & "*"

When I try this, it opens up a blank record. What am I doing wrong?

Also I realize I should be doing this with VBA and not macro builder. I don't understand enough about VBA to get this done.

[ittechguy]

After more research, I believe the proper syntax is this: [Forms]![frmMainRooms]![frmSubFacilityMgr].[Form]![LastName] Like "*" & [Forms]![frmMainRooms]![txtSearch] & "*"

I've tested this by creating a text box on my main form and using "[Forms]![frmMainRooms]![frmSubFacilityMgr].[Form]![LastName]" as the control source. That text box then displays the last name of the person in my subform. However, when I try to search for this persons last name it keeps displaying a blank form.

[June7]

Haven't we already discussed this in another thread?

[ittechguy]

Haven't we already discussed this in another thread?

I don't think so. We discussed how to call a value from another form. Which is similar to this. We discussed search forms. I was given some very helpful links on how to build a search form (thanks Orange!). But actually searching a sub form from a main form, I don't think so.

[June7]

Customary approach is to filter main form based on a value entered into a control on main form then the subform just tags along. I don't understand what you are trying to do.

[ittechguy]

I ended up using vba. I'm making progress but it's painfully slow.

I have a thread under programming that explains this better.