406 SOF T WARE ENGINEERING AND TESTING
7. If RST.EOF = True Then ‘ i.e. NO RECORD FOUND
8. MsgBox “This Id does not exist”, vbCritical, “Edit”
9. RST.Close ‘ CLOSE THE RECORDSET BEFORE EXIT
10. Exit Sub
11. End If
‘ASK FOR CONFIRMATION TO DELETE THE RECORD
12. Dim X As Integer ‘MsgBox RETURNS AN INTEGER
13. X = MsgBox(“Are you sure to Delete the record”, vbQuestion +
vbYesNo)
14. If X = 7 Then ‘ i.e. ‘NO’ BUTTON IS CLICKED
15. RST.Close ‘ CLOSE THE RECORDSET BEFORE EXIT
16. Exit Sub
17. Else
18. CONN.Execute “DELETE FROM STUDENT WHERE ID = “ & IDNO & “”
19. MsgBox “Record Deleted”, vbInformation, “Delete”
20. End If
21. RST.Close ‘CLOSE THE RECORDSET BEFORE ENDING SUBROUTINE
End Sub
Private Sub Cmdedit_Click()
1. If Cmdedit.Caption = “Update” Then
2. Call Update ‘CALLS UPDATE PROCEDURE TO SAVE THE RECORD AFTER
CHANGE
3. Exit Sub
4. End If
5. IDNO = InputBox(“Please Enter Student Id”, “Edit”)
‘EXIT FROM SUBROUTINE IF NO ID IS ENTERED
6. If IDNO = “” Then
7. Exit Sub
8. End If
‘MAKE AN OBJECT OF THE VARIABLE TO STORE THE DATA
9. Set RST = New ADODB.Recordset
‘CHECK WHETHER THE RECORD OF GIVEN ID EXIST
10. Set RST = New ADODB.Recordset
11. RST.Open “SELECT * FROM STUDENT WHERE ID = “ & IDNO & “”, CONN,
adOpenDynamic, adLockOptimistic
PROGRAMMING IN VISUAL BASIC WITH MS ACCESS 2000 407
12. If RST.EOF = True Then ‘ i.e. NO RECORD FOUND
13. MsgBox “This Id does not exist”, vbCritical, “Edit”
14. RST.Close ‘ CLOSE THE RECORDSET BEFORE EXIT
15. Exit Sub
16. End If
‘ASK FOR CONFIRMATION TO MODIFY THE RECORD
17. Dim X As Integer ‘MsgBox RETURNS AN INTEGER
18. X = MsgBox(“Are you sure to edit the record”, vbQuestion +
vbYesNo)
19. If X = 7 Then ‘ i.e. ‘NO’ BUTTON IS CLICKED
20. RST.Close ‘ CLOSE THE RECORDSET BEFORE EXIT
21. Exit Sub
22. Else
‘DISPLAY THE DATA IF ‘YES’ BUTTON IS CLICKED
23. Txtname = RST.Fields(“NAME”)
24. Txtage = RST.Fields(“AGE”)
25. Txtclass = RST.Fields(“STD_CLASS”)
26. DtpAdmDate = RST.Fields(“ADMISSION_DATE”)
27. Txtincome = RST.Fields(“FATHER_MONTHLY_INCOME”)
28. End If
29. RST.Close ‘CLOSE THE RECORDSET BEFORE ENDING SUBROUTINE
30. Cmdedit.Caption = “Update” ‘CHANGE THE CAPTION TO CALL
‘Update’
PROCEDURE
End Sub
Private Sub Cmdsave_Click()
‘CHECK NULL ENTRIES
1. If Txtname = “” Then
2. MsgBox “Please Enter Student Name”, vbExclamation, “Incomplete
Record”
3. Txtname.SetFocus
4. Exit Sub
5. End If
6. If Txtage = “” Then
7. MsgBox “Please Enter Age”, vbExclamation, “Incomplete
Record”
408 SOF T WARE ENGINEERING AND TESTING
8. Txtage.SetFocus
9. Exit Sub
10. End If
11. If Txtclass = “” Then
12. MsgBox “Please Enter Class”, vbExclamation, “Incomplete
Record”
13. Txtclass.SetFocus
14. Exit Sub
15. End If
16. If Txtincome = “” Then
17. MsgBox “Please Enter Father’s Monthly Income”, vbExclamation,
“Incomplete Record”
18. Txtincome.SetFocus
19. Exit Sub
20. End If
‘MAKE AN OBJECT OF THE VARIABLE TO STORE THE DATA
21. Set RST = New ADODB.Recordset
‘INCREMENT THE ID
22. Dim i As Integer
23. RST.Open “SELECT MAX(ID) FROM STUDENT”, CONN, adOpenDynamic,
adLockOptimistic
24. If IsNull(RST.Fields(0)) = False Then
25. i = RST.Fields(0) + 1
26. Else
27. i = 1
28. End If
29. RST.Close
‘RECORD INSERTION IN THE TABLE
30. CONN.Execute “INSERT INTO STUDENT VALUES(“ & i & “,’” & Txtname
& “‘,” & Txtage & “,’” & Txtclass & “‘,’” & DtpAdmDate & “‘,”
& Txtincome & “)”
31. MsgBox “Student Record Saved”, vbInformation, “Save”
‘CLEAR THE TEXT BOX FOR NEW ENTRY
32. Call Cmdnew_Click
End Sub
Private Sub Cmdcancel_Click()
1. Unload Me
PROGRAMMING IN VISUAL BASIC WITH MS ACCESS 2000 409
End Sub
Private Sub Cmdnew_Click()
1. Txtname = “”
2. Txtage = “”
3. Txtclass = “”
4. Txtincome = “”
5. DtpAdmDate = Date
6. Txtname.SetFocus
End Sub
Private Sub Form_Load()
‘DISPLAY THE FORM AT THE CENTER OF MDIFORM
1. Me.Left = MDIForm1.Width \ 2 - Me.Width \ 2
2. Me.Top = MDIForm1.ScaleHeight \ 2 - Me.Height \ 2
‘DISPLAY CURRENT DATE IN DTPICKER
3. DtpAdmDate = Date
End Sub
Private Sub Update()
‘TO CHECK NULL ENTRIES
1. If Txtname = “” Then
2. MsgBox “Please Enter Student Name”, vbExclamation, “Incomplete
Record”
3. Txtname.SetFocus
4. Exit Sub
5. End If
6. If Txtage = “” Then
7. MsgBox “Please Enter Age”, vbExclamation, “Incomplete
Record”
8. Txtage.SetFocus
9. Exit Sub
10. End If
11. If Txtclass = “” Then
12. MsgBox “Please Enter Class”, vbExclamation, “Incomplete
Record”
13. Txtclass.SetFocus
14. Exit Sub

Get Software Engineering and Testing now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.