Lesson 22: Creating an Advanced VB database application using ADO control
In previous lessons, you have learned to design database applications using data control and ADO control. However, those are very simple and plain applications. In this lesson, you will learn to create a more advance database application using ADO control. The application you are going to create is known as an electronic library. This electronic library will be able to accept users' registration as well as handling login command that require the use of password, thus enhancing the security aspect of the database. Basically, the application will constitute a welcome menu, a registration menu, a Login menu and the main database menu. The sequence of the menus are illustrated as follow:
Welcome Registered Users Registration Login Database
¡¡
¡¡
¡¡
¡¡
¡¡
¡¡
¡¡
¡¡
First of all, you need to design the Welcome menu. You can follow the example as follow:
¡¡
In this form, you need to insert three command buttons and set their properties as follow:
| Form name | main_menu |
| command button 1 Name | cmdRegister |
| command button 1 Caption | Register |
| command button 2 Name | cmdLogin |
| command button 2 Caption | Login |
| command button 3 Name | cmdCancel |
| command button 3 Caption | Cancel |
The codes are as follow:
Private Sub cmdCancel_Click()
End
End Sub
Private Sub cmdLogin_Click()
main_menu.Hide
Login_form.Show
End Sub
Private Sub cmdRegister_Click()
main_menu.Hide
Register.Show
End Sub
If a new user click the Register button, the registration form will appear. An example is illustrated as follow:
This registration forms consist of two text boxes , three command buttons and an ADO control. Their properties are set as follow:
| Form name | Register |
| textbox 1 name | txtName |
| textbox 2 name | txtpassword |
| textbox 2 PasswordChar | * |
| command button 1 name | cmdConfirm |
| command button 1 Caption | Confirm |
| command button 2 name | cmdClear |
| command button 2 Caption | Clear |
| command button 3 name | cmdCancel |
| command button 3 Caption | Cancel |
| ADO control name | UserInfo |
note that the PasswordChar of textbox 2 is set as * which means users will not be able to see the actual characters they enter, they will only see the * symbol.
The codes are as follow:
Private Sub cancel_Click( )
End
End Sub
Private Sub cmdClear_Click( )
txtName.Text = ""
txtpassword.Text = ""
End Sub
Private Sub cmdConfirm_Click()
UserInfo.Recordset.Fields("username") = txtName.Text
UserInfo.Recordset.Fields("password") = txtpassword.Text
UserInfo.Recordset.Update
Register.Hide
Login_form.Show
End Sub
Private Sub Form_Load()
UserInfo.Recordset.AddNew
End Sub
¡¡
The Login menu is illustrated as follow:
There are two text boxes and a command button, their properties are set as follow:
| Textbox 1 name | txtName |
| Textbox 2 name | txtpassword |
| Command button 1 name | cmdLogin |
| Command button 1 Caption | Login |
| Form name | Login_form |
The codes are as follow:
Private Sub cmdLogin_Click()
Dim usrname As String
Dim psword As String
Dim usernam As String
Dim pssword As String
Dim Msg As String
Register.UserInfo.Refresh
usrname = txtName.Text
psword = txtpassword.Text
Do Until Register.UserInfo.Recordset.EOF
If Register.UserInfo.Recordset.Fields("username").Value = usrname And Register.UserInfo.Recordset.Fields("password").Value = psword Then
Login_form.Hide
frmLibrary.Show
Exit Sub
Else
Register.UserInfo.Recordset.MoveNext
End If
Loop
Msg = MsgBox("Invalid password, try again!", vbOKCancel)
If (Msg = 1) Then
Login_form.Show
txtName.Text = ""
txtpassword = ""
Else
End
End If
End Sub
¡¡
The main database menu is illustrated as follow:
The properties of all controls are listed in the table below:
¡¡
| Form name | frmLibrary |
| ADO control name | adoLibrary |
| ADO visible | False |
| TextBox 1 name | txtTitleA |
| TextBox 2 name | txtAuthor |
| TextBox 3name | txtPublisher |
| TextBox 4 name | txtYear |
| TextBox 5 name | txtCategory |
| Command button 1 name | cmdSave |
| Command button 1 caption | &Save |
| Command button 2 name | cmdNew |
| Command button 2 caption | &New |
| Command button 3 name | cmdDelete |
| Command button 3 caption | &Delete |
| Command button 4 name | cmdCancel |
| Command button 4 caption | &Cancel |
| Command button 5 name | cmdNext |
| Command button 5 caption | N&ext |
| Command button 6 name | cmdPrevious |
| Command button 6 caption | &Previous |
| Command button 7 name | cmdExit |
| Command button 7 caption | E&xit |
¡¡
The codes are as follow:
¡¡
Private Sub cmdCancel_Click()
txtTitle.Text = ""
txtAuthor.Text = ""
txtPublisher.Text = ""
txtYear.Text = ""
txtCategory.Text = ""
End Sub
Private Sub cmdDelete_Click()
Confirm = MsgBox("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If Confirm = vbYes Then
adoLibrary.Recordset.Delete
MsgBox "Record Deleted!", , "Message"
Else
MsgBox "Record Not Deleted!", , "Message"
End If
End Sub
Private Sub cmdExit_Click()
End
End Sub
Private Sub cmdNew_Click()
adoLibrary.Recordset.AddNew
End Sub
Private Sub cmdNext_Click()
If Not adoLibrary.Recordset.EOF Then
adoLibrary.Recordset.MoveNext
If adoLibrary.Recordset.EOF Then
adoLibrary.Recordset.MovePrevious
End If
End If
End Sub
Private Sub cmdPrevious_Click()
If Not adoLibrary.Recordset.BOF Then
adoLibrary.Recordset.MovePrevious
If adoLibrary.Recordset.BOF Then
adoLibrary.Recordset.MoveNext
End If
End If
End Sub
Private Sub cmdSave_Click()
adoLibrary.Recordset.Fields("Title").Value = txtTitle.Text
adoLibrary.Recordset.Fields("Author").Value = txtAuthor.Text
adoLibrary.Recordset.Update
End Sub
discuss this topic to forum
