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
- Create a Client Management Form
- Create ADD, UPDATE, DELETE buttons
- Create a SEARCH functionality by Client Name/Account Number/Email
- 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
Control | Name | Caption |
Command Button | cmdSearch | Search |
Command Button | cmdAdd | Add |
Command Button | cmdUpdate | Update |
Command Button | cmdDelete | Delete |
Combo Box | cboSearchBy | |
Text Box | txtSearch | |
Text Box | txtAccount | |
Text Box | txtClientName | |
Text Box | txtEmail | |
Check Box | chkDisable | Disable Client |
Label | Search By | |
Label | Search Value | |
Label | Account No | |
Label | Client Name | |
Label |
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.
Nice one Boss!!
Thank you Timothy 🙂