Lesson 29: Creating an Eletcronic Library using ADO control

Creating an Eletcronic Library using ADO control

In previous lessons, you have learned how to design database applications using data control and ADO control. However, those applications are very simple and plain . In this lesson, you will learn how to create a more advanced 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 the user registration as well as handling login command that require the user's  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 is illustrated in Figure 29.1 follow:

Figure 29.1

29.1 The Welcome Menu

First of all, you need to design the Welcome menu. You can follow the example as shown in Figure 29.1 below:

Figure 29.2: The Welcome Menu

In this form, you need to insert three command buttons and set their properties as shown in Table 29.1 below:

Table 29.1

Propery Name	Setting
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 code

Private Sub cmdLogin_Click()

main_menu.Hide
Login_form.Show

End Sub

Private Sub cmdRegister_Click()

main_menu.Hide
Register.Show

End Sub

29.2 The Registration Form

If a new user click the Register button, the registration form will appear as illustrated in Figure 29.3 below:

Figure 29.3: The Registration Form

This registration forms consist of two text boxes , three command buttons and an ADO control. Their properties are set as in Table 29.2 below:

---

  

Table 29.2

Propery Name	Setting
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 code

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
　

---

29.3 The Login Menu

The Login menu is illustrated as follow:

Figure 29.4: The Login Menu

There are two text boxes and a command button,  their properties are set as in Table 29.3 below:

Table 29.3

Propery Name	Setting
Textbox 1 name	txtName
Textbox 2 name	txtpassword
Command button 1 name	cmdLogin
Command button 1 Caption	Login
Form name	Login_form

---

The code

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
　

---

29.4 The Electronic Library

To design the electronic library interface, we need to insert a couple of labels, a couple of text boxes, three option buttons, a DataGrid control and an ADO control. The name and properties of the controls are listed in Table 29.4.

Table 29.4

Propert Name	Setting
Form name	frmLibrary
ADO control name	adoLibrary
ADO visible	False
DataGrid Name	DataLibrary
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	cmdClr
Command button 4 caption	&Clear
Command button 5 name	cmdFirst
Command button 5 Caption	&First
Command button 6 name	cmdNext
Command button 6 caption	N&ext
Command button 7 name	cmdPrevious
Command button 7 caption	&Previous
Command button 8 name	cmdLast
Command button 8 caption	&Last
Command button 9 name	cmdExit
Command button 9 caption	E&xit
Command button 10 name	CmdViewAll
Command button 10 caption	&View All
Command button 11 name	CmdSearch
Command button 9 caption	Search

The electronic library design interface is illustrated in Figure 29.5 below.

Figure 29.5

The runtime interface of the elecronic library is shown in Figure 29.6. In the library, you can browse all the books using the navigation buttons. You can also add, save and delete books. Besides that, you can also search for books based on Author, ISBN or book title.

Figure 29.6

We use SQL keywords to program the search button. The code for the search button is as follows:

Private Sub cmdSearch_Click()
DataLibrary.Visible = True
Dim SearchString As String
SearchString = TxtSearch.Text

If Opt_ISBN.Value = True Then

AdoLibrary.RecordSource = "SELECT * FROM book WHERE ISBN='" & SearchString & "'"
ElseIf Opt_Author.Value = True Then
'Search for Author that starts with the Search String
AdoLibrary.RecordSource = "SELECT * FROM book WHERE Author Like '" & SearchString & "%'"
ElseIf Opt_Title.Value = True Then

AdoLibrary.RecordSource = "SELECT * FROM book WHERE Title Like '" & SearchString & "%'"

End If

AdoLibrary.Refresh
'To reset the column width of datagrid DataLibrary
With DataLibrary

.Columns(0).Width = 2200
.Columns(1).Width = 4500
.Columns(2).Width = 2800
.Columns(3).Width = 2000
.Columns(4).Width = 800
.Columns(5).Width = 1500
End With

DataLibrary.Visible = True

End Sub

* The symbol % is called a wildcard character in SQL. Wildcard characters are used to search for data in a table with the SQL LIKE operator. The wildcard % means a substitute for zero or more characters. In our code above, Using SearchString with % means it will search for a combination of the SearchString with any other characters. For example, SearchString="New", then SearchString&% will include Newyork, Newport, NewCastle, Newton etc.

---

The rest of the code

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 cmdFirst_Click()
AdoLibrary.Recordset.MoveFirst
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 cmdLast_Click()
AdoLibrary.Recordset.MoveLast
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
　

Share on Google Plus

About M

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.