asked on

Show the number of records returned in a filter

Hi,

I'm trying to show the number of records return from a filter in a text box. Currentl it works fo the complete record source (ie, it shows 1 of 101 records etc.) What i'm trying to do is disaply the number of filtered items returns (ie. 1 of 17 etc).

I have included the code i am trying. Currently i get a runtime error 3075 (syntax error in query expresion) on this line:

Me![RecordCount] = "Contact " & (recClone.AbsolutePosition + 1) & " of " & _
DCount("company_id", "tbl_contacts", SQL)

Any ideas where i'm going wrong?

Tom

Private Sub search_company_Change()
Dim SQL As String
Dim recClone As Object
 
Me!lbCompanies.RowSource = "SELECT * FROM tbl_Contacts Where [company_name] Like '" & Me.search_company.Text & "*' ORDER BY [company_name] ASC;"
 
SQL = " SELECT * FROM tbl_Contacts Where [company_name] Like '" & Me.search_company.Text & "*' ORDER BY [company_name] ASC;"
Form.RecordSource = SQL
 
Set recClone = Me.RecordsetClone
 
 
' Check to see if there are no records
 
If recClone.RecordCount = 0 Then
  cmdNext.enabled = False
  cmdPrevious.enabled = False
  cmdFirst.enabled = False
  cmdLast.enabled = False
Else
 
Me![RecordCount] = "Contact " & (recClone.AbsolutePosition + 1) & " of " & _
 DCount("company_id", "tbl_contacts", SQL)
 
 
 
recClone.Close
DoCmd.RunCommand acCmdRefreshPage
 
End If
End Sub

Open in new window

Rey Obrero (Capricorn1)


 
Private Sub search_company_Change()
Dim SQL As String, sWhere as string
Dim recClone As Object
 
Me!lbCompanies.RowSource = "SELECT * FROM tbl_Contacts Where [company_name] Like '" & Me.search_company.Text & "*' ORDER BY [company_name] ASC;"
 
SQL = " SELECT * FROM tbl_Contacts Where [company_name] Like '" & Me.search_company.Text & "*' ORDER BY [company_name] ASC;"
Form.RecordSource = SQL
sWhere= "[company_name] Like '" & Me.search_company.Text & "*'"
Set recClone = Me.RecordsetClone
 
 
' Check to see if there are no records
 
If recClone.RecordCount = 0 Then
  cmdNext.enabled = False
  cmdPrevious.enabled = False
  cmdFirst.enabled = False
  cmdLast.enabled = False
Else
 
Me![RecordCount] = "Contact " & (recClone.AbsolutePosition + 1) & " of " & _
 DCount("company_id", "tbl_contacts", sWhere)
 
 
 
recClone.Close
DoCmd.RunCommand acCmdRefreshPage
 
End If
End Sub

Open in new window

d10u4v

ASKER

I get an error on:

sWhere = "[company_name] Like '" & Me.search_company.Text & "*'"

runtime error 2185: You can't reference a property of method for a control unles the control has focus.

Tom

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

d10u4v

ASKER

That worked.
The only problem now is when the user clicks 'next record' on my navigation buttons the number of records returned from the filter goes back to the full amount even though they can only navigate to the number of records returned from the filter.

How can i keep the number of item displyed as they scroll through the filtered records?

My Next Button is:

Public Function navToNext(frm As Object)
On Error GoTo Err_navToNext
 
DoCmd.GoToRecord acDataForm, frm.Name, acNext
 
 
Exit_navToNext:
  Exit Function
    
Err_navToNext:
  MsgBox Err.description
  Resume Exit_navToNext
 
End Function

Open in new window

d10u4v

ASKER

I have figured it out.

Thank you for your help withthe initial problem!

Tom