-When the form loads, the cursor sets focus in Search text box and all records are populated in the list box .The first record be selected in the list box automatically(See Search form above).
-When you enter ProductID(P003) and click Search command button, that record will be selected in the list box. See the figure below:
-If you click Search command button without entering ProductID in the text box, one message box will appear:
-When the form cannot find the ProductID you entered, another message box will appear:
After you clicked Ok command button of message box, the data in the text box is selected and the list box is unselected. See the figure below:
To have a form as the figure above, you have to create the form in Form Design. See the figure below:
Note: Unselect Use Control Wizards before you drag these controls to the form.
- Drag and drop a text box, a list box and a command buttons.
- Set Name properties of three controls:
Then apply the VBA Code below:
Option Compare Database
Dim Rs As dao.Recordset
Private Sub Form_Load()
Set Rs = CurrentDb.OpenRecordset("TblProduct", dbOpenDynaset)
lstData.RowSourceType = "Table/Query"
lstData.ColumnHeads = True
lstData.ColumnCount = 4
lstData.RowSource = "Select * from TblProduct"
txtSearch = ""
Private Sub cmdSearch_Click()
If txtSearch <> "" Then
Rs.FindFirst "ProductID='" & txtSearch & "'"
If Rs.NoMatch = True Then
MsgBox "The record not found.", vbInformation
lstData.ListIndex = -1
txtSearch.SelLength = Len(txtSearch)
MsgBox "Enter what you want to find.", vbInformation
Sub SelectList(i As Integer)
lstData.ListIndex = 0
lstData.ListIndex = i
This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages: