VBA example - Microsoft Access : search data form

Form to search for data

In this page, you learn how to find student’s data on a form in VBA example - Microsoft Access. You can search student’s data through one of the three fields: Student ID, Student Name, or Sex; select which option you like.

We have a form for searching students’ data as shown below:

ms access search form  

-When a form loaded, it looks like the figure above; all values of controls are blank (No selection on option buttons and no check on check box). In addition, it doesn’t show list box yet unless you check on check box.
-When you click on StudentID option button, all students’ ID in table student will be populated into the combo box.
-When you click  on StudentName option button, all students’ names in table student will be listed into combo box.
-Sex option button does the same way as studentID and studentName above.
-When you check View Student Info check box, it will show all students’ data in list box as figure below:

form seach 1

-When you select data in combo box after you clicked which option button, the data will show in list box
-When you click on New Student Command Button, Student form will load for students’ new data entry.

form search 2


Note: For student form, you have to create it by yourself. You don’t need to create the same form as I showed you above.
To do what I elaborated above, you have to do the following:

Form Design

Drop and drag the Control and rename them:
-One option group
-3 option buttons
-One combo box
-One check box
-One command button
-One list box


-On Form load
Private Sub Form_Load()
ChckViewStuInfo.Value = 0
OptGroup.Value = 0
cboCondition.Value = ""
Me.InsideHeight = Me.InsideHeight - 4000
End Sub

-On OptGroup
Private Sub OptGroup_Click()
If OptGroup.Value = 1 Then
cboCondition.RowSource = ""
cboCondition.RowSource = "Select StudentId from TblStudent"
cboCondition.Value = cboCondition.ItemData(0)
lstViewStudentInfo.RowSource = "Select * from Tblstudent where studentID='" & cboCondition.ItemData(0) & "'"
ElseIf OptGroup.Value = 2 Then
cboCondition.RowSource = ""
cboCondition.RowSource = "Select StuName from TblStudent"
cboCondition.Value = cboCondition.ItemData(0)
lstViewStudentInfo.RowSource = "Select * from Tblstudent where stuName='" & cboCondition.ItemData(0) & "'"
cboCondition.RowSource = ""
cboCondition.RowSource = "Select Sex from TblStudent"
cboCondition.Value = cboCondition.ItemData(0)
lstViewStudentInfo.RowSource = "Select * from Tblstudent where sex='" & cboCondition.ItemData(0) & "'"
End If
End Sub

-On cboCondition
Private Sub cboCondition_Change()
If OptGroup.Value = 1 Then
Me.lstViewStudentInfo.RowSourceType = "Table/Query"
Me.lstViewStudentInfo.RowSource = "Select * from TblStudent where StudentID ='" & cboCondition.Text & "'"
ElseIf OptGroup.Value = 2 Then
Me.lstViewStudentInfo.RowSource = "Select * from TblStudent where StuName='" & cboCondition.Text & "'"
Me.lstViewStudentInfo.RowSource = "Select * from Tblstudent where Sex='" & cboCondition.Text & "'"
End If
End Sub

-On ChkViewStuInfo
Private Sub ChckViewStuInfo_Click()
If ChckViewStuInfo.Value = -1 Then
lstViewStudentInfo.Visible = True
 Me.InsideHeight = Me.InsideHeight + lstViewStudentInfo.Height
lstViewStudentInfo.RowSourceType = "Table/Query"
lstViewStudentInfo.RowSource = "Select * from TblStudent"
 lstViewStudentInfo.Visible = False
 Me.InsideHeight = Me.InsideHeight - lstViewStudentInfo.Height
End If
End Sub

-On cmdOpenNewStudent
Private Sub cmdOpenNewStudent_Click()
DoCmd.OpenForm "frmStudent", acNormal


mura comment




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.