How to develop a commercial e-Statement solution in vb6 step by step – Part 8 – Send e-Statements to your Clients – Manage Client List

Send e-Statements to your Clients – Manage Client List

Before we start sending our clients e-Statements, we need to have a database of our client details, most importantly their email addresses. So in our post today, we are going to create a way of managing our client`s details and have a way of searching and updating our client database in case their details might have change.

ATTENTION: This post is as a continuation of previous posts. You will need to start from the start to be able to follow this post.

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

1. Create a Client Management Form
2. Create ADD, UPDATE, DELETE buttons
3. Create a SEARCH functionality by Client Name/Account Number/Email
4. Disable/Enable a client

Create a Client Management Form

Open you SmartMail project and add a new form. Name your from as ‘frmClientList’ and give it a Caption of ‘Clients Email List’.

On the form add the following Controls

Your Form should look like below

Once our form is created , what remains is the coding of each action.

Copy and paste the code below, and if you have followed all the instruction it should work perfectly

'eStatement Application Development
'Client Management Module
'www.smarttechdiary.com

Option Explicit
Dim rs As New Recordset
Dim AccountNo As String
Dim ClientName As String
Dim Email As String
Dim Disabled As String
Dim SearchBy As String

Private Sub cmdAdd_Click()
'Add a new client in to the clients SQL Table

'User Input Validation

'Check if User has typed Account No
If txtAccount.Text = "" Then
MsgBox "Please enter an account number", vbExclamation, "Client Details"
txtAccount.SetFocus
End If
'Check if User has typed Client Name
If txtClientName.Text = "" Then
MsgBox "Please enter client name", vbExclamation, "Client Details"
txtClientName.SetFocus
End If
'Check if User has typed Email Addredd
If txtEmail.Text = "" Then
MsgBox "Please enter client email address", vbExclamation, "Client Details"
txtEmail.SetFocus
End If

'Check if the Account No already exists before we add
With rs
If .State = 1 Then .Close
.Open "SELECT * From Clients WHERE AccountNo like '" & txtAccount.Text & "'", CN, adOpenDynamic, adLockOptimistic
If Not rs.EOF Then
MsgBox "Account number already exists", vbExclamation, "Client Details"
txtAccount.SetFocus
Exit Sub
Else

'If Account No is not in the Clients Table, add the new client
.AddNew
rs!AccountNo = txtAccount.Text
rs!Name = txtClientName.Text
rs!Email = txtEmail.Text
rs!CreatedBy = CurrUser.USER_NAME
rs!isDisabled = chkDisable.Value
.Update
End If

.Close
End With
End Sub

Private Sub cmdSearch_Click()
'Search a client using either:
'   1 Client Name
'   2 Client Email
'   3 Client Account

With rs
If .State = 1 Then .Close
SearchBy = cboSearchBy.Text
.Open "SELECT * From Clients WHERE " & SearchBy & " like '" & txtSearch.Text & "'", CN, adOpenDynamic, adLockOptimistic
If Not .EOF Then
txtAccount = rs!AccountNo
txtClientName = rs!Name
txtEmail = rs!Email

End If

.Close
End With
End Sub

Private Sub Form_Load()
'Add Items to the Search Combobox
cboSearchBy.AddItem "AccountNo"
cboSearchBy.AddItem "Email"
cboSearchBy.AddItem "Name"
End Sub

See how the Client Management Form looks like when running

I hope you enjoyed this post.In case of any questions please leave your comments below or contact me directly.
Kindly like and share with your friends using the below buttons.