Total number of filtered records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been through "Tim's" query and 69 Camaro's response for totalling the
number of records returned by a filter, but it didn't work for me. Perhaps my
situation is different enough (or I'm confused enough!) that someone can
advise how to do this.

First, I have a form based on a query that has some criteria specified, and
all these records are displayed in a subform.

The main form contains fields to allow the user to select multiple search
critiera, which are then passed to a filter. For example:

If Not IsNothing(cboOriginator) Then
If IsNothing(Searchstr) Then
Searchstr = "[OriginatorID]= " & Me.cboOriginator
Else
Searchstr = Searchstr & " AND [OriginatorID]= " & Me.cboOriginator
End If
End If

If Not IsNothing(cboProcess) Then
If IsNothing(Searchstr) Then
Searchstr = "[ProcessID]= " & Me.cboProcess
Else
Searchstr = Searchstr & " AND [ProcessID]= " & Me.cboProcess
End If
End If

Me.Searchstr = Searchstr
Me.Review_Search_subfrm2.Form.Filter = Searchstr
Me.Review_Search_subfrm2.Form.FilterOn = True

When the user clicks "Search" on the main form, the subform displays the
filtered records. If however, the search doesn't return any records, I want
to display a message box.

Like I said, I've tried 69 Camaro's solution, but it didn't work for me. Any
help will be greatly appreciated.
 
You could probably ask the subform for the count:

Me.Review_Search_subfrm2.Form.Recordset.Recordcount

If that doesn't work, then try DCount with all of the filtering parameters
you are using to filter the subform, including the value of the Parent/Child
link. For the Record Source of the DCount statement, you would use the same
table or query that you are using for the subform's Record Source.

Example:
DCount("*", Me.Review_Search_subfrm2.Form.RecordSource, strSearch & " And
[LinkFieldName] = " & Me.txtLinkField)

Adjust the above if the link field is a text field instead of a number.

if the RecordSource contains spaces in its name you may need to modify the
above to:
DCount("*", "[" & Me.Review_Search_subfrm2.Form.RecordSource & "]",
strSearch & " And [LinkFieldName] = " & Me.txtLinkField)
 
Christine,

I have no idea what 69 Camaro's solution is, so I can't comment on that
aspect.

You could try it like this...

...
If DCount("*","YourQuery",Searchstr) = 0 Then
MsgBox "No matching records"
Else
Me.Review_Search_subfrm2.Form.Filter = Searchstr
Me.Review_Search_subfrm2.Form.FilterOn = True
End If
 
Wayne and Steve, Thanks for your replies - My problem before was not writing
DCount correctly and not including the If/Else with it. Your solutions work
great.

Christine

Steve Schapel said:
Christine,

I have no idea what 69 Camaro's solution is, so I can't comment on that
aspect.

You could try it like this...

...
If DCount("*","YourQuery",Searchstr) = 0 Then
MsgBox "No matching records"
Else
Me.Review_Search_subfrm2.Form.Filter = Searchstr
Me.Review_Search_subfrm2.Form.FilterOn = True
End If

--
Steve Schapel, Microsoft Access MVP

I've been through "Tim's" query and 69 Camaro's response for totalling the
number of records returned by a filter, but it didn't work for me. Perhaps my
situation is different enough (or I'm confused enough!) that someone can
advise how to do this.

First, I have a form based on a query that has some criteria specified, and
all these records are displayed in a subform.

The main form contains fields to allow the user to select multiple search
critiera, which are then passed to a filter. For example:

If Not IsNothing(cboOriginator) Then
If IsNothing(Searchstr) Then
Searchstr = "[OriginatorID]= " & Me.cboOriginator
Else
Searchstr = Searchstr & " AND [OriginatorID]= " & Me.cboOriginator
End If
End If

If Not IsNothing(cboProcess) Then
If IsNothing(Searchstr) Then
Searchstr = "[ProcessID]= " & Me.cboProcess
Else
Searchstr = Searchstr & " AND [ProcessID]= " & Me.cboProcess
End If
End If

Me.Searchstr = Searchstr
Me.Review_Search_subfrm2.Form.Filter = Searchstr
Me.Review_Search_subfrm2.Form.FilterOn = True

When the user clicks "Search" on the main form, the subform displays the
filtered records. If however, the search doesn't return any records, I want
to display a message box.

Like I said, I've tried 69 Camaro's solution, but it didn't work for me. Any
help will be greatly appreciated.
Click to expand...
Click to expand...
 
Back
Top