Search or filter records on a form [modified]
-
Wonder if any of you guys could point me in the right direction? Everywhere I've searched on Google always shows me how to do this with a datagrid - but not a form - and it just doesn't seem to want to work how I want it work. I've created a form which correctly displays all my fields in a recordset. I can scroll through them, add new records, delete records, etc. etc. and everything is fine. I now want to add a Search field to the form so that when the user enters a search string, it automatically searches through the dataset for EITHER the CompanyName OR the CompanyID - and refreshes the display to display the appropriate search result (ie. populates the currently open form with the appropriate record). -- modified at 18:21 Wednesday 31st May, 2006 I partly figured it out - but can't get it to work with an OR part of the SQL. Any advice would be appreciated. The CompanyID is an Autonumber field.
Dim strSearch As String = "SELECT * FROM tblCustomersParent WHERE CustomerName Like '%" & txtSearchString.Text & "%'" Me.OleDbSelectCommand1.CommandText = strSearch Me.LoadDataSet() -
Wonder if any of you guys could point me in the right direction? Everywhere I've searched on Google always shows me how to do this with a datagrid - but not a form - and it just doesn't seem to want to work how I want it work. I've created a form which correctly displays all my fields in a recordset. I can scroll through them, add new records, delete records, etc. etc. and everything is fine. I now want to add a Search field to the form so that when the user enters a search string, it automatically searches through the dataset for EITHER the CompanyName OR the CompanyID - and refreshes the display to display the appropriate search result (ie. populates the currently open form with the appropriate record). -- modified at 18:21 Wednesday 31st May, 2006 I partly figured it out - but can't get it to work with an OR part of the SQL. Any advice would be appreciated. The CompanyID is an Autonumber field.
Dim strSearch As String = "SELECT * FROM tblCustomersParent WHERE CustomerName Like '%" & txtSearchString.Text & "%'" Me.OleDbSelectCommand1.CommandText = strSearch Me.LoadDataSet()Are you trying to do a like on the auto number field? "SELECT * FROM tblCustomersParent WHERE CustomerName Like '%" & txtSearchString.Text & "%' or CompanyID = " & txtCompanyName You should really look at using parameterized queries as well. Mike Lasseter
-
Are you trying to do a like on the auto number field? "SELECT * FROM tblCustomersParent WHERE CustomerName Like '%" & txtSearchString.Text & "%' or CompanyID = " & txtCompanyName You should really look at using parameterized queries as well. Mike Lasseter
Hi - yes, the query was pretty much like you've suggested. I had tried:
"Select * From tblCustomersParent Where CompanyName Like '%" & txtSearchString.Text & "%'" & _ " or CompanyID Like '%" & txtSearchString.Text & "%'"and then..."Select * From tblCustomersParent Where CompanyName Like '%" & txtSearchString.Text & "%'" & _ " or CompanyID Like " & txtSearchString.Text... and ended up just commenting out the OR bit :sigh: What do you mean when you say, "parameterized queries"? Could you show me an example?