visual basic help to create a desktop PhoneBook application (5)

Connecting to Microsoft Access 2003 through Microsoft Visual Basic 6.0 and then create a report for it is an easy task even for a beginners whether using the connectiong wizard or using the code, all you need is a practise. We will now connect the database we designed with the application project that we designed using the ADO2.8 techonology to connect.
To created a connection to the MS-Database (MyBook.mdb) let say it's called (CN) where will store the connection string to the MS-Access database, (RS) is a connection variable to store the connection string to the table called (Phones) to Save/Retrieve data. The coding will start from the Module we created called (evry1falls.bas), like this :

	'1) Using ADO2.8 Technology to connect your Access DB.
	Global CN As New ADODB.Connection
	Global RS As New ADODB.Recordset

view raw gistfile1.vb hosted with ❤ by GitHub

First thing to do when working with database from your application is to make sure that the Database connection is closed (Off) and the table is closed (Off) because we will open both of them only when we try to save and retrieve. We will execute the connection (CN) and (RS) from MainFrm (Form_Load()) event like this :

	Private Sub Form_Load()
	'Apply Graphics.
	Form_Paint
	'2-Connect to Database
	If CN.State = 1 Then CN.Close
	CN.Open ("Provider = Microsoft.Jet.OleDB.4.0 ; Data Source =" & App.Path & "\MyBook.Mdb")
	'3)Connect Table Phones
	If RS.State = 1 Then RS.Close
	RS.CursorLocation = adUseClient
	RS.Open ("Select * From Phones"), CN, adOpenDynamic, adLockOptimistic
	'5)MyPic, if no other photo has been picked then this is the default picture.
	PicNm = App.Path & "/Phone1.Jpg"
	Timg.Picture = LoadPicture(PicNm)
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

To choose a picture for the friend by clicking the Image control using the (Timg Click()) event :

	Private Sub Timg_Click()
	'When clicking on the Timg control,
	'Chat box appears to pic a photo
	Cdl.Filter = ("Jpeg Jpg Photo Type (.Jpg) \|.Jpg")
	Cdl.CancelError = False
	Cdl.DialogTitle = ("Choose a friend photo")
	Cdl.ShowOpen
	If Cdl.FileName = Trim("") Then
	PicNm = App.Path & "/Phone1.Jpg"
	Else
	PicNm = ("")
	PicNm = Cdl.FileName
	End If
	Timg.Picture = LoadPicture(PicNm)
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

To save Data into the Database table, we will use this code in the (Cmd_Save Click()) event :

	Private Sub CmdSave_Click()
	'4)Add new friend's phone #
	'- Making sure the friend's name TextBox is not Empty,
	'you can always add phone #s laters.
	If Tnm.Text = Trim("") Then
	MsgBox "Can't save data" & vbCrLf & _
	"The name field is empty," & vbCrLf & "please fill the name field.", vbCritical, _
	"Warning"
	Tnm.SetFocus
	Exit Sub
	End If
	'-Save the data to the Database.
	' A good practise on how to save/retrieve
	'Photos [http://evry1falls.freevar.com/VB6/photo.html]
	RS.AddNew
	RS!Fname = Trim(Tnm.Text)
	RS!Fmob = Trim(Tmob.Text)
	RS!Fphone = Trim(Thp.Text)
	'Saving the picture using the Binary method.
	Rs_Stream.Type = adTypeBinary
	Rs_Stream.Open
	Rs_Stream.LoadFromFile PicNm
	If Rs_Stream.Size > 0 Then
	RS("Fpic") = Rs_Stream.Read
	RS.Update
	MsgBox "(1) entry saved successfully", vbOKOnly, "Done"
	'Open stream must be closed, or you will get error
	'(Operation not allowed in this context)
	Rs_Stream.Close
	Set Rs_Stream = Nothing
	ClearTxtBox
	End If
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

To reset all the fields of the Textboxs and the Image control, use this code :

	'General Declaration
	Dim TxtCtrl As Control
	Private Sub ClearTxtBox()
	For Each TxtCtrl In Me.Controls
	If TypeOf TxtCtrl Is TextBox Then
	TxtCtrl.Text = Trim("")
	End If
	Next
	Timg.Picture = LoadPicture(App.Path & "/Phone1.Jpg")
	End Sub

	Private Sub CmdRes_Click()
	'Reset Button
	'Call the pre-defined sub ClearTextBox
	ClearTxtBox
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Now, save some names of your friends along with thier Phone numbers and photos from your computer and display them in the (Display.frm) form we created previously.
From the IDE of the Display.frm form, we wil create the codes of retrieving the data from the database table. We will also have to make sure the connection to the database is Off and the Table connection is Off before openning it, so the Form_Load() event for the form Display.frm will be like this :

	Private Sub Form_Activate()
	'connection for Holding the photo stream.
	If Rs_Stream.State = adStateOpen Then Rs_Stream.Close
	End Sub

	Private Sub Form_Load()
	'Display.frm code
	Form_Paint
	'Load friends from Database Table into ListBox control.
	'Database is already connected as we did not terminate
	'the main form (MainFrm)
	'Open Table
	If RS.State = 1 Then RS.Close
	RS.CursorLocation = adUseClient
	RS.Open ("Select * From Phones"), CN, _
	adOpenDynamic, adLockOptimistic
	'Check for Data
	If RS.RecordCount = 0 Then Exit Sub
	'Prepare the ListBox Control
	LstFrnds.Clear
	RS.MoveFirst
	Do Until RS.EOF = True
	LstFrnds.AddItem Trim$(RS!Fname)
	RS.MoveNext
	Loop
	PicNm = ""
	'Next step is to retrieve each friend's Data assigned to the name
	'in the listbox into the (Img, Name, Mobile, Phone) controls,
	'2) LstFrnds.Click event is next
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Now, by clicking on the (LstFrnds) control, we want the Data to be successfullt transfered from the Database table onto the form related controls (Textboxs and Image), by using the (LstFrnds Click()) even and the code:

	Private Sub LstFrnds_Click()
	'2) Retrieval of the stored data
	'Searching the Database Table, where Criteria is Friend Name (Fname)
	RS.MoveFirst
	RS.Find "Fname = '" & LstFrnds.Text & "'"
	If RS.EOF Then
	MsgBox ("Something went wrong")
	Exit Sub
	End If
	Label2.Caption = Trim("Name")
	Label2.Caption = Label2.Caption & " : " & RS!Fname

	FriendID = RS!ID
	Tnm.Text = RS!Fname
	Tmob.Text = RS!Fmob
	Thp.Text = RS!Fphone
	ReadPic
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

To update the friends info stored in the database table, use this code in the (CmdUp Click()) event like this :

	Private Sub CmdUp_Click()
	'Updating friend Info
	'Make sure the update proccess is actually an UPDATE ....
	'1)
	If Tnm.Text = Trim$("") Then
	MsgBox "Please first, pick up a friend to update " & _
	"his/her data", vbCritical, "Invalid Update Proccess"
	Exit Sub
	End If
	'2)
	If RS.RecordCount = 0 Then Exit Sub
	'OK, we can update now.
	RS.MoveFirst
	RS.Find "ID = '" & FriendID & "'"
	If RS.EOF Then
	MsgBox "Something went wrong", _
	vbCritical, "Update Proccess"
	Exit Sub
	End If
	RS!Fname = Trim(Tnm.Text)
	RS!Fmob = Trim(Tmob.Text)
	RS!Fphone = Trim(Thp.Text)

	'Updating the photo
	'Notice : when you click (Lstfrnds) to retrieve Info,
	'the image retrieved is stored in the default Picture
	'Phone.Jpg
	If Rs_Stream.State = adStateOpen Then Rs_Stream.Close
	Rs_Stream.Type = adTypeBinary
	Rs_Stream.Open
	Rs_Stream.LoadFromFile PicNm
	If Rs_Stream.Size > 0 Then
	RS("Fpic") = Rs_Stream.Read
	RS.Update
	MsgBox "(1) entry updated successfully", vbOKOnly, "Done"
	'Open stream must be closed, or you will get error
	'(Operation not allowed in this context)
	Rs_Stream.Close
	Set Rs_Stream = Nothing
	'Prepare the ListBox Control
	LstFrnds.Clear
	RS.MoveFirst
	Do Until RS.EOF = True
	LstFrnds.AddItem Trim$(RS!Fname)
	RS.MoveNext
	Loop
	End If
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

To delete a friend from the database table along with its photo by (CmdDel Click()) event using this code :

	Private Sub CmdDel_Click()
	'Deleting a friend.
	'Make sure the update proccess is actually an UPDATE ....
	'1)
	If Tnm.Text = Trim$("") Then
	MsgBox "Please first, pick up a friend to remove " & _
	"from friends list", vbCritical, "Invalid Delete Proccess"
	Exit Sub
	End If
	'2)
	If RS.RecordCount = 0 Then Exit Sub
	'3)
	Dim Xs As String
	Xs = MsgBox("Are you 100% sure that you wish to remove : " & vbCrLf & _
	"" & Tnm.Text & vbCrLf & _
	"from your friends list?", vbCritical + vbYesNoCancel, "Warning delete")
	If Xs = vbNo Then
	Exit Sub
	ElseIf Xs = vbCancel Then
	Exit Sub
	Else
	'OK, we can Delete now.
	RS.MoveFirst
	RS.Find "ID = '" & FriendID & "'"
	If RS.EOF Then
	MsgBox "Something went wrong", vbCritical, "Deleting Proccess"
	Exit Sub
	End If
	RS.Delete
	MsgBox "(1) entry deleted successfully", vbOKOnly, "Deleting Done"
	'Prepare the ListBox Control
	LstFrnds.Clear
	RS.MoveFirst
	Do Until RS.EOF = True
	LstFrnds.AddItem Trim$(RS!Fname)
	RS.MoveNext
	Loop
	ClearTxtBox
	End If
	End Sub

view raw gistfile1.vb hosted with ❤ by GitHub

Now try the application and eport any bugs. The following Part#6 we will see how to create Crystal Reports report and how to design it and include it in the application and how to write codes for it using the (CmdPrint) button.