How to develop a commercial e-Statement solution in vb6 step by step – Part 4 – Code the Modules and the menus

Building a Commercial e-Statement Application in VB 6 – Create E-Statement Modules and Menus

During our previous post on this series on How to Create an e-Statement Application, we created the Smart Mail database in Microsoft SQL Server and its tables. Today we are going to create Modules,Menus and ODBC  Data Source to enable us connect  to the SQL Server database and create a login system (next post) for the e-statement application which will control who has access to the system and their security rights.

ATTENTION: Before you continue make sure you have created the SmartMail database in SQL Server plus all its tables!

In Summary, we are going to do the following in this article

1. Add the needed Components and References to the SmartMail project
2. Add  MDI Form
3. Add Toolbar Menu to the MDI Form
4. Create e-Statement Modules
5. Create ODBC Data Source

Add the needed Components and References to the SmartMail e-Statement Project

Before we go any further we need to add the correct Components and References that out application need,for it to run properly.

Components

Open you SmartMail project in VB6. On the main menu Click on Project > Components or simply hit CRTL + T on your keyboard.

Make sure you have the following controls selected and applied.

• Microsoft Common Dialog Control 6.0(SP6)
• Microsoft DataList Controls 6.0(OLEDB)
• Microsoft Windows Common Controls 6.0(SP6)
• Microsoft Windows Common Controls-2.6.0(SP4)

References

To add the required references Click on the Project > References

Select the following References and add them to your project

1. Visual Basic For Applications
2. Visual Basic runtime objects
3. Visual Basic objects and procedure
4. Microsoft CDO for Windows 2000 Library
5. Microsoft Active Data Objects 2.5 Library

Add  MDI Form

MDI stands for Multiple Document Interface.To Add ,Right Click on SmartMail Project choose Add and choose MDI Form. Remember we can only add one MDI Form since  mine is already added my MDI Form has been grayed out.But you should be able to add the MDI Form.

Please save your progress , and remember to save the MDI Form in the Forms folder. Save the MDI Form as MAIN.frm see below:

Add Toolbar Menu to the MDI Form

Go to your Toolbox and select Toolbar and drag and drop to your MDI Form.

Once you have added the Toolbar control on your MDI Form,click the toolbar and rename it from Toolbar1 to TbMenu

We need to add a menu to our MAIN.frm like below using the Menu Editor. Click on the Menu Editor and Add the following menu for now

Create an ODBC Data Source

In this section I will show you how to connect the e-statement application to the SQL database using a data source.

On your windows machine type ODBC on the search box, to open the ODBC window.

Click on Add button, on the window that appears, select SQL Server as your driver and click Finish

Give a data source name,description and the Server name which is the name of the SQL Instance. Click Next.

On the next screen we need to authenticate with the SQL Server for us to be able to successfully connect to the SmartMail database.

Choose the “With SQL Server Authentication…”, you the sa username and the password you used while Installing SQL Server

When you are done,click on Next.

On the next window,change the default database to SmartMail database or to the name of the e-statement database you have created. Click next

Click Next and Finish.

Test your connection by clicking on the Test data source button

If you have everything correctly, you should have TEST COMPLETED SUCCESSFULLY!

Create e-Statement Modules

Before we can create the Login System, we need to create the following Module. These modules have different functions in our e-Statement application like database connection and others.

In summary create the following modules:

1. modPublicVar
2. modProcedure
3. modFunctions
4. modConnect
5. modVarType

Module ‘modPublicVar’

In this module we will be defining all Public Variables

Option Explicit


Public CurrUser                     As USER_INFO
Public CurrBiz                      As BUSINESS_INFO
Public CN                           As New Connection

Module ‘modProcedure’

In this module we will put together all the procedures that we will be creating for the e-statement application

Put the following code in this module

'eStatement Application Development
'www.smarttechdiary.com
'Procedure used to bind data combo
Public Sub bind_dc(ByVal srcSQL As String, ByVal srcBindField As String, ByRef srcDC As DataCombo, Optional srcColBound As String, Optional ShowFirstRec As Boolean)
    Dim RS As New Recordset
   ' OpenDB
    RS.CursorLocation = adUseClient
    RS.Open srcSQL, CN, adOpenStatic, adLockOptimistic
    
    With srcDC
        .ListField = srcBindField
        .BoundColumn = srcColBound
        Set .RowSource = RS
        'Display the first record
        If ShowFirstRec = True Then
            If Not RS.RecordCount < 1 Then
                .BoundText = RS.Fields(srcColBound)
                .Tag = RS.RecordCount & "*~~~~~*" & RS.Fields(srcColBound)
            Else
                .Tag = "0*~~~~~*0"
            End If
        End If
    End With
    Set RS = Nothing
End Sub


'Procedure used to highlight text when focus
Public Sub HLText(ByRef sText)
    On Error Resume Next
    With sText
        .SelStart = 0
        .SelLength = Len(sText.Text)
    End With
End Sub

Module ‘modFunctions’

This module will have all the functions for the e-statement application

'eStatement Application Development
'www.smarttechdiary.com
'Function that return the value of a certain field
Public Function getValueAt(ByVal srcSQL As String, ByVal whichField As String) As String
    Dim RS As New Recordset
    
    RS.CursorLocation = adUseClient
    RS.Open srcSQL, CN, adOpenStatic, adLockReadOnly
    If RS.RecordCount > 0 Then getValueAt = RS.Fields(whichField)
    
    Set RS = Nothing
End Function

'Function used to change the yes/no value
Public Function changeYNValue(ByVal srcStr As String) As String
    Select Case srcStr
        Case "Y": changeYNValue = "1"
        Case "N": changeYNValue = "0"
        Case "1": changeYNValue = "Y"
        Case "0": changeYNValue = "N"
    End Select
End Function

Module ‘modVarType’

In this module we will be defining all our variable types that are not already existing .Copy and paste the following code to your module.

'eStatement Application Development
'www.smarttechdiary.com
'Variable structure for user

Public Type USER_INFO
    USER_PK As Long
    USER_NAME As String
    USER_ISADMIN As Boolean
End Type

Public Type BUSINESS_INFO
    BUSINESS_NAME As String
    BUSINESS_ADDRESS As String
    BUSINESS_CONTACT_INFO As String
End Type

Module ‘modConnect’

This module is very important. It is the link between our e-statement application and the SQL Server database. It how ever depends on all the other modules.Copy and paste the code below to you module.

This module needs the data source name and the sa password to the SQL Server. Change the password and the data source name to match what you have

'eStatement Application Development
'www.smarttechdiary.com
Public Const myPass As String = "YourSQLPassword"
'Function to Open a Connection to SQL Through Data Source
Public Function OpenDB() As Boolean
Dim isOpen As Boolean
Dim ANS As VbMsgBoxResult
isOpen = False
On Error GoTo err
Do Until isOpen = True
CN.CursorLocation = adUseClient
CN.Open "SmartMail", "sa", myPass 'SmartMail is the Data Source Name.Change if needed
isOpen = True
Loop
OpenDB = isOpen
Exit Function
err:
ANS = MsgBox("Error Number: " & err.Number & vbCrLf & "Description: " & err.Description, _
vbCritical + vbRetryCancel)
If ANS = vbCancel Then
OpenDB = vbCancel
ElseIf ANS = vbRetry Then
OpenDB = vbRetry
End If
End Function

Public Sub CloseDB()
'Close the connection
CN.Close
Set CN = Nothing
End Sub

I think we have covered a lot today and we can continue with this development of e-statement application on the next post.Since we have created all our modules needed so far and the database connection, next post we will create the Login System. In case you have any question am ready to help, just contact me any time.

Kindly share will all your friends, good day.