I have a form which contains several subforms. My main form displays building name and room name and then everything else is in subforms. There's a RoomPOC subform, a facilitymgr subform, and a cabinets subform. I need to be able to search from the main form by either building/room name or by a customer's last name, or by a cabinet name, or by a switch name (inside my cabinets subform). Basically, I want to be able to search through a subform and pull up that record in my main form. So if I search for building 1 room 1, it will show building 1, room 1 and then the room POC, facility manager, cabinets, switches, etc. If I search for Joe Smith and Joe Smith is the facility Manager for building 1, 2, and 3, I should see 3 records... for building 1, 2, and 3 and I should see all of the subforms within those 3 records.
I've created a simple search form using this code here:
Code:
- Private Sub cmdSearch_Click()
- Dim strWhere As String
- If Not IsNull(Me.cboRoomName) Then
- strWhere = strWhere & "([RoomName] Like ""*" & Me.cboRoomName & "*"") "
- End If
- Me.Filter = strWhere
- Me.FilterOn = True
- End Sub
This works great for room name, however I cannot figure out how to search for everything else I need. The problem I have is only building name and room name is in the recordsource for my main form. Everything else (last name, first name, cabinet, switch, etc) is located in my subforms. I've tried adding AND and then
Code:
If Not IsNull(Me.txtSearchLastName) Then
strWhere = strWhere & "([Forms]![frmMainRooms]![frmSubFacilityMgr].[Form]![LastName] Like ""*" & Me.txtSearchLastName & "*"")"
End If
However, that returns a blank form. I should add, I shouldn't even be doing it like that because if I specify the full path for my control name, it will not work when I open the form under my navigation form. I have a navigation form and I need this to work both inside navigation form as well as from frmMainRooms.
What am I doing wrong?