VBA example - 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:

Descriptions:
-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:

-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.

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
    +OptGroup
-3 option buttons
     +OptStudentID
     +OptStudentName
     +OptSex
-One combo box
     +cboCondition
-One check box
     +ChkViewStuInfo
-One command button
     +cmdOpenNewStudent
-One list box
     +lstViewStuInfo

Coding

-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) & "'"
Else
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 & "'"
Else
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"
Else
 lstViewStudentInfo.Visible = False
 Me.InsideHeight = Me.InsideHeight - lstViewStudentInfo.Height
End If
End Sub

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