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:
First of all, you need to design the Welcome menu. You can follow the example as shown in Figure 29.1 below:
In this form, you need to insert three command buttons and set their properties as shown in Table 29.1 below:
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 |
Private Sub cmdLogin_Click()
main_menu.Hide
Login_form.Show
Private Sub cmdRegister_Click()
main_menu.Hide
Register.Show
End Sub
If a new user click the Register button, the registration form will appear as illustrated in Figure 29.3 below:
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:
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.
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 in Table 29.3 below:
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 |
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
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.
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.
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.
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.
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
Copyright©2008 Dr.Liew Voon Kiong. All rights reserved |Contact|Privacy Policy