Filtering search results

[Underpaidadmin]

I have a custom search form that returns results in a subform. I don't know
if this is possible but how can I display just 1 record even if multiple
search results match it? Here is a snippet of the code that creates the
where statement and opens the subform filter:

Private Sub btnSearch_Click()
Dim strWhere As String

If Not IsNull(Me.txtCaseNum) Then
strWhere = strWhere & "tblCasesMain.CaseNum LIKE '*" & Me.txtCaseNum & "*'"
End If
'----------
If Not IsNull(Me.txtFirstName) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNameslst.FirstName LIKE '*" & Me.txtFirstName & "*'"
End If
'----------
If Not IsNull(Me.curAmount) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCasesMain.Exposure = " & Me.curAmount & " OR " &
"tblBalances.InitBal= " & Me.curAmount & " OR " &
"tblPaymentslst.PaymentAmount= " & Me.curAmount
Debug.Print strWhere
End If

If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
With Me.sbfBrowse.Form
.Filter = strWhere
.FilterOn = True
End With
End Sub

 

[Wolfgang Kais]

Hello Underpaidadmin.

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1 record
even if multiple search results match it? [... ]

How about changing the DefaultView property of the subform?

 

[Underpaidadmin]

As far as I can see changing the Defaultview property just changes 'look' of
the output. I want to actually filter out certain records from displaying if
certain fields match. In fact I have just recently learned about the
DISTINCT property of SQL and was hoping someone could perhaps help me
understand how this could work to solve my problem.

Hello Underpaidadmin.

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1 record
even if multiple search results match it? [... ]

How about changing the DefaultView property of the subform?

 

[Wolfgang Kais]

Hello "Underpaidadmin"

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1 record
even if multiple search results match it? [... ]

How about changing the DefaultView property of the subform?

As far as I can see changing the Defaultview property just changes
'look' of the output. I want to actually filter out certain records from
displaying if certain fields match. In fact I have just recently learned
about the DISTINCT property of SQL and was hoping someone could
perhaps help me understand how this could work to solve my problem.

The question is: How do you filter in the subform?
You can of course bas the subform on a query that does not contain the
primary key of the table the data is based on and ise the HideDuplicates
property of that query (this will create a "sekect distinct" query). If all
matching records contain the same data, this will show only one matching
record.
If your subform is not linked to the master form, you could specify a
"select top 1" query as the recordsource of the subform in the click event
for a filter button, including the filter criteria, this will result in only
the first matching record.

 

[Underpaidadmin]

My subform is in the form footer section of the main form. It is based off a
query. The main form gets the information the user wants to search for
creates a where statement and applies it as a filter to the subform in the
form footer section. Hmm I can't find this HideDuplicates setting anywhere.
It doesn't mention anything about queries having a HideDuplicates property am
I not looking in the right place?

Hello "Underpaidadmin"

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1 record
even if multiple search results match it? [... ]
How about changing the DefaultView property of the subform?

As far as I can see changing the Defaultview property just changes
'look' of the output. I want to actually filter out certain records from
displaying if certain fields match. In fact I have just recently learned
about the DISTINCT property of SQL and was hoping someone could
perhaps help me understand how this could work to solve my problem.

The question is: How do you filter in the subform?
You can of course bas the subform on a query that does not contain the
primary key of the table the data is based on and ise the HideDuplicates
property of that query (this will create a "sekect distinct" query). If all
matching records contain the same data, this will show only one matching
record.
If your subform is not linked to the master form, you could specify a
"select top 1" query as the recordsource of the subform in the click event
for a filter button, including the filter criteria, this will result in only
the first matching record.

 

[Underpaidadmin]

Ok it is called UniqueValues or UniqueRecords properties. Neither of these
changed my results in any way so perhaps I best explain the mechanics of my
search form better. The main form is unbound. The user types in or selects
various criteria from list boxes. When they hit the Search button it takes
the information they provided and makes a where statement:

If Not IsNull(Me.curAmount) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCasesMain.Exposure = " & Me.curAmount & " OR " &
"tblBalances.InitBal= " & Me.curAmount & " OR " &
"tblPaymentslst.PaymentAmount= " & Me.curAmount
End If

It uses this where statement to set the filter of the subform located in the
footer section. This subform is based on a query that draws all records from
the 8 linked tables. So when if all fields are empty the subform displays
all records. If they are filled in they display matching records. My
problem comes when I have a record in one table that links to multiple
records in another table, I get the same record listed several times. In
some cases I want this to happen, such as when a user searches a first, last,
or company name. However if they are searching for payments as well they do
not want to see 5 of the same records if there are only two names linked to
it.

I felt like I didn't explain it very well before which is why I wasn't
understanding the answers. I apologize for that and thank you for persisting
with me.

My subform is in the form footer section of the main form. It is based off a
query. The main form gets the information the user wants to search for
creates a where statement and applies it as a filter to the subform in the
form footer section. Hmm I can't find this HideDuplicates setting anywhere.
It doesn't mention anything about queries having a HideDuplicates property am
I not looking in the right place?

Hello "Underpaidadmin"

I have a custom search form that returns results in a subform.
I don't know if this is possible but how can I display just 1 record
even if multiple search results match it? [... ]

How about changing the DefaultView property of the subform?

As far as I can see changing the Defaultview property just changes
'look' of the output. I want to actually filter out certain records from
displaying if certain fields match. In fact I have just recently learned
about the DISTINCT property of SQL and was hoping someone could
perhaps help me understand how this could work to solve my problem.

The question is: How do you filter in the subform?
You can of course bas the subform on a query that does not contain the
primary key of the table the data is based on and ise the HideDuplicates
property of that query (this will create a "sekect distinct" query). If all
matching records contain the same data, this will show only one matching
record.
If your subform is not linked to the master form, you could specify a
"select top 1" query as the recordsource of the subform in the click event
for a filter button, including the filter criteria, this will result in only
the first matching record.