We help IT Professionals succeed at work.

Show the number of records returned in a filter

d10u4v
d10u4v asked
on
241 Views
Last Modified: 2013-11-28
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

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:


 
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

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
I have figured it out.

Thank you for your help withthe initial problem!

Tom
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.