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
- Add the needed Components and References to the SmartMail project
- Add MDI Form
- Add Toolbar Menu to the MDI Form
- Create e-Statement Modules
- 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.
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)
To add the required references Click on the Project > References
Select the following References and add them to your project
- Visual Basic For Applications
- Visual Basic runtime objects
- Visual Basic objects and procedure
- Microsoft CDO for Windows 2000 Library
- 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:
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
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
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
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
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.