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

Microsoft Access

Last Comment

d10u4v

8/22/2022 - Mon

Rey Obrero (Capricorn1)

7/19/2008


 
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

7/19/2008

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)

7/19/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

d10u4v

7/19/2008

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

d10u4v

7/19/2008

ASKER

I have figured it out.

Thank you for your help withthe initial problem!

Tom