Filter Main Form by SubForm Criteria

[SodaJim]

Hello,

I'm attempting to Filter a main form with a tab control that has several tabs, one of which is Address. On this tab, is a SubForm with basic address information such as street number, street name, direction, type etc...

On the main form, I have a cmd button Search that will open a search form with the same fields as the Address SubForm.
Once criteria is entered into any of the search fields, I would like to filter the main form with the address criteria...

Any direction on literature that discusses this type of technique...?

I'm not an accomplished programmer so simplicity is welcomed. I have attempted a few samples that generate an sql string to filter by which may or may not be correct, but also I can'y seem to get the reference to filter the main form and not the subform.

Any direction appreciated,
Jim

 

[WayneRyan]

Hi Jim,

If the search form is not bound to a table, then
you will essentially "lose" any information in it
when the form closes.

Prior to closing the search form you can move field
contents like:

Forms![YourMainForm]![SomeField] = Me.txtSearch1

Then base your main form on a query where, in the
criteria for your fields make them

=Forms[YourMainForm]![SomeField] for whatever column
the search criteria represents.

Or, you can just set the RowSource for the main form
like:

Me.RowSource = "Select * from MyTable where " & _
"CriteriaField = " & Me.SomefIELD

Then do a Me.Requery

hth,
Wayne

 

[SodaJim]

Hello again Wayne,

You are correct; the Search Form is unbound. Perhaps, I could just hide the form... I thought utilizing SQL would make the search/filter more flexible and efficient...

The Search Form has address related fields that I need my end users to input data to filter/retrieve/view records. I don't know which fileds will be utilized for the search/filter, so I'm not sure a query based search may apply...?
Ex:
User1 may need to see all addresses w/ street_direction = NE and street_type = ST
User2 may need to see street_direction = E and street_name = 31 and street_type = BLVD

I'm a little unsure how implementing a query can accomplish this task...

Or, you can just set the RowSource for the main form
like:

Me.RowSource = "Select * from MyTable where " & _
"CriteriaField = " & Me.SomefIELD

Then do a Me.Requery

Problem with this technique is the main table doesn't store addresses; this info is in an address_info table which is what the SubForm Addresses is based from for each record in the main form.

Does this clear things a little...

You time is appreciated!

 

[WayneRyan]

Jim,

Sure, you can hide the sub-form.

Not sure I understand the relationship between
the main form and the subform with the addresses.

If the search criteria readily determine the
population of the subform, doesn't that also
determine the "parents" recordset?

wayne

 

[SodaJim]

Thanks for the reply!

Hello Wayne,

I've posted a link to an image of the relationships for this DB:
Relationships.gif

My thinking was to generate an SQL string for the filter since there are a few fields that an end user could search by... It was mentioned by another person to create an "InnerJoin" between the Main Table and the Address Table to only show records in the Main Table that meet the criteria established in the Search String from the Search From. My problem is that I'm not very familiar with this type of code in order to add it to the Filter Source of the Main Form.

Here's the code I'm currently attempting to get functioning:

CODE:
---------------------------------
Private Sub cmdSearch_Click()

Dim strWhereHydrant As String
strWhereHydrant = ""

' Build a filter string for criteria specified...
If Not IsNothing(Me!AddressNumber) Then
strWhereHydrant = "[AddressNumber] Like " & Chr$(34) & Me!AddressNumber
If Right$(Me!AddressNumber, 1) = "*" Then
strWhereHydrant = strWhereHydrant & Chr$(34)
Else
strWhereHydrant = strWhereHydrant & "*" & Chr$(34)
End If
End If

If Not IsNothing(Me!StreetDirection) Then
If IsNothing(strWhereHydrant) Then
strWhereHydrant = "[StreetDirection] Like " & Chr$(34) & Me!StreetDirection
Else
strWhereHydrant = strWhereHydrant & " AND [StreetDirection] Like " & Chr$(34) & Me!StreetDirection
End If
If Right$(Me!StreetDirection, 1) = "*" Then
strWhereHydrant = strWhereHydrant & Chr$(34)
Else
strWhereHydrant = strWhereHydrant & "*" & Chr$(34)
End If
End If

If Not IsNothing(Me!StreetName) Then
If IsNothing(strWhereHydrant) Then
strWhereHydrant = "[StreetName] Like " & Chr$(34) & Me!StreetName
Else
strWhereHydrant = strWhereHydrant & " AND [StreetName] Like " & Chr$(34) & Me!StreetName
End If
If Right$(Me!StreetName, 1) = "*" Then
strWhereHydrant = strWhereHydrant & Chr$(34)
Else
strWhereHydrant = strWhereHydrant & "*" & Chr$(34)
End If
End If

If Not IsNothing(Me!StreetType) Then
If IsNothing(strWhereHydrant) Then
strWhereHydrant = "[StreetType] Like " & Chr$(34) & Me!StreetType
Else
strWhereHydrant = strWhereHydrant & " AND [StreetType] Like " & Chr$(34) & Me!StreetType
End If
If Right$(Me!StreetType, 1) = "*" Then
strWhereHydrant = strWhereHydrant & Chr$(34)
Else
strWhereHydrant = strWhereHydrant & "*" & Chr$(34)
End If
End If

If Not IsNothing(Me!ComplexName) Then
If IsNothing(strWhereHydrant) Then
strWhereHydrant = "[ComplexName] Like " & Chr$(34) & Me!ComplexName
Else
strWhereHydrant = strWhereHydrant & " AND [ComplexName] Like " & Chr$(34) & Me!ComplexName
End If
If Right$(Me!ComplexName, 1) = "*" Then
strWhereHydrant = strWhereHydrant & Chr$(34)
Else
strWhereHydrant = strWhereHydrant & "*" & Chr$(34)
End If
End If

' If no criteria, then nothing to do!
If IsNothing(strWhereHydrant) Then
MsgBox "You didn't enter any criteria." + vbCr & vbLf + "Please enter Search Information.", _
vbQuestion, " Missing Criteria"
Exit Sub
End If

' Hide myself and turn on Hourglass
Me.Visible = False
DoCmd.Hourglass True
If IsLoaded("frmHydrantsMain") Then ' If Hydrants Main form already open,
' Then just filter it
Forms!frmHydrantsMain.SetFocus
DoCmd.ApplyFilter , strWhereHydrant
If Forms!frmHydrantsMain.RecordsetClone.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No Fire Hydrants meet your criteria", vbExclamation, " Fire Hydrants"
DoCmd.ShowAllRecords
Me.Visible = True
'Exit Sub
End If
End If


' DoCmd.OpenForm FormName:="frmAddresses", WhereCondition:=strWhereHydrant

End Sub

---------------------------------
END CODE