The result of searching must have a data matching the search Keyword on all search fields. The result of using AND operator between fields will be narrow than single field. Me.RecordSource = Task Search data from multiple fields using operator “AND” Task = "SELECT * FROM tbl_customer WHERE ((CustomerName Like ""*" & strsearch & "*"") OR (city Like ""*" & strsearch & "*"") OR (Address Like ""*" & strsearch & "*""))" For instance, searching keyword for “Fresno” on three fields: “CustomerName, City or Address” the result will show as follow: strsearch = Me.txtSearch.Value The result of using OR operator between fields will be wider than single field because all data that matching the search keyword from multiple fields will show on the result. Task = "SELECT * FROM tbl_customer WHERE ((CustomerName Like ""*" & strsearch & "*""))"Įnd Sub Search data from multiple fields using operator “OR” MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed" If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then The example below is searching for a Customer Name using Like “*”… keyword….”*” Search on a single field Private Sub Command163_Click()
#MS ACCESS FORM CODE#
Search keyword from the textbox txtSearch: on the Search button, put code below under the On Click Event Procedure on the property sheet. Private Sub Command94_Click() Dim Task As String Task = “SELECT * FROM tbl_Customer” Me.RecordSource = Task End Subġ0. Show All records from table customer: on the Show All button, put code below under the On Click Event Procedure on the property sheet. Task = "SELECT * FROM tbl_customer WHERE (customer_ID)is null"ĩ.
![ms access form ms access form](https://i.stack.imgur.com/2O8N2.png)
‘load form with a yellow color background on a search box Normally, all customers have the customer id in this form because the customer_id is a primary key. Load a blank form on Open: We will load a form with no record from a table customer by using the SQL statement with the criteria of Customer_id is null. Under the property sheet, select a “Dynaset”for a Recordset TypeĨ. Under the property sheet, select a “Continuous Forms” for a Default Viewħ. Insert two buttons for Search and Show all button on the Form Header sectionĦ. Insert a textbox and name it as txtSearchfor a keyword search on the Form Header sectionĥ. Insert a vertical line to separate each field and a horizontal line to separate record.Ĥ. Under the design view, add Customer_id, CustomerName, Address, City fields etc.
![ms access form ms access form](https://www.opengatesw.net/images/tutorials/Access-Detail-Form.png)
Under the property sheet, select table tbl_Customer as a Record Sourceģ. We can search on a single form by using the VBA function to search for data on one field or more fields that we want to search for.Ģ. MS Access 2010 has a built-in text filter function on the datasheet form however, it is still attached to the individual field.
#MS ACCESS FORM HOW TO#
How to Create a Search Form by Keyword using VBA Part 1 ( Related Video)