troubleshooting Question

Show the number of records returned in a filter

Avatar of d10u4v
d10u4vFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft Access
5 Comments1 Solution245 ViewsLast Modified:

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?

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
Me![RecordCount] = "Contact " & (recClone.AbsolutePosition + 1) & " of " & _
 DCount("company_id", "tbl_contacts", SQL)
DoCmd.RunCommand acCmdRefreshPage
End If
End Sub
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros