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:
Private Sub search_company_Change()Dim SQL As StringDim recClone As ObjectMe!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 = SQLSet recClone = Me.RecordsetClone' Check to see if there are no recordsIf recClone.RecordCount = 0 Then cmdNext.enabled = False cmdPrevious.enabled = False cmdFirst.enabled = False cmdLast.enabled = FalseElseMe![RecordCount] = "Contact " & (recClone.AbsolutePosition + 1) & " of " & _ DCount("company_id", "tbl_contacts", SQL)recClone.CloseDoCmd.RunCommand acCmdRefreshPageEnd IfEnd Sub
Open in new window