Public class login form in vb - Free Essay Example

Public Class loginForm Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles okButton2.Click Try mappDB.UserName = usernameTextBox.Text : mappDB.Password = passwordTextBox.Text Call mappDB.connect() MainForm.Show() Catch ex As Exception Call showError(ex.Message) End Try End Sub Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click If MessageBox.Show(Do you really want to quit now?, strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then Application.Exit() End Sub Private Sub loginForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Me.Text = strApplicationName Login passwordTextBox.Text = String.Empty todo remove when launch usernameTextBox.Text = admin passwordTextBox.Text = 7151epeN End Sub End Class Imports System.Data.OleDb Imports System.Configuration Public Class MainForm Structure _gridFilter Dim _sql As String Dim _criteria As String Dim _orderBy As String End Structure Dim gridFilter As New _gridFilter Dim cmdLocal As OleDbCommand Dim blIsActiveLoan As Boolean Dim bsGLSummary As New BindingSource Private Sub exitToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) If MessageBox.Show(Quit application now?, strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then Application.Exit() End Sub Private Sub MainForm_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated loginForm.Visible = False End Sub Private Sub MainForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing Call mappDB.close() : Call mappDB.dispose() mappDB = Nothing End Sub Private Sub NewLoanToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NewLoanToolStripMenuItem.Click borrowForm.ShowDialog() End Sub Private Sub BorrowerToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BorrowerToolStripMenuItem.Click borrowerForm.ShowDialog() End Sub Private Sub MainForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Me.Text = strApplicationName for ConfigurationManager.AppSettings(client).ToString dateLabel.Text = System.DateTime.Now.ToString(MMM dd yyyy) loginUserNameLabel.Text = mappDB.UserName loginsinceLabel.Text = System.DateTime.Now.ToString(MMM dd yyyy hh:mm tt) filtercriteriaComboBox.SelectedIndex = 1 With glSumTotalDataGridView .ReadOnly = True .Rows.Add() : .Rows.Add() : .Rows.Add() .Item(0, 0).Value = Total Cr .Item(0, 1).Value = Total Dr .Item(0, 2).Value = Balance .Columns(0).Width = 550 .Columns(1).Width = 150 .Cursor = Cursors.Hand .SelectionMode = DataGridViewSelectionMode.FullRowSelect End With End Sub Private Sub searchfilterButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchfilterButton.Click Me.Cursor = Cursors.WaitCursor gridFilter._sql = SELECT pk_borrowerID,lastname as [Last name],firstname as [First Name] FROM tblborrower gridFilter._orderBy = ORDER BY lastname Select Case filtercriteriaComboBox.SelectedIndex Case 0 firstname gridFilter._criteria = WHERE firstname like filtervalueTextBox.Text % Case 1 lastname gridFilter._criteria = WHERE lastname like filtervalueTextBox.Text % Case 2 company gridFilter._criteria = WHERE company like filtervalueTextBox.Text % End Select Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, searchBrDataGridView, True) Me.Cursor = Cursors.Default End Sub Private Sub refreshGLSumToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles glsum_refreshToolStripButton.Click Me.Cursor = Cursors.WaitCursor gridFilter._sql = SELECT fk_borrowerid,fullname as [Borrower Name],sumofcr as [Sum of Cr], sumofdr as [Sum of Dr], Balance FROM qGLsummary If Not incZeroBalToolStripButton.Checked Then gridFilter._criteria = Else gridFilter._criteria = WHERE balance 0 End If gridFilter._orderBy = ORDER BY Fullname Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, glSumDataGridView, True, bsGLSummary) glsumRowCountToolStripLabel.Text = of glSumDataGridView.Rows.Count 1 Call firstToolStripButton_Click(sender, e) Call glsum_totals() Me.Cursor = Cursors.Default Call TotalOfGLSum() End Sub Private Sub Label9_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles drtotalal_glsumLabel.Click End Sub Private Sub viewGLSummaryToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles glsum_openToolStripButton.Click Try Me.Cursor = Cursors.WaitCursor Call TabControl1.SelectTab(1) gridFilter._sql = Select pk_mloanid , pk_loanref as [Loan Ref],isactive as [Active], Dated FROM tblloanmaster gridFilter._orderBy = ORDER BY dated DESC gridFilter._criteria = WHERE fk_borrowerid= glSumDataGridView(0, glSumDataGridView.CurrentRow.Index).Value.ToString Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, brloanlistDataGridView, True) brloanlistDataGridView.Columns(2).Width = 40 brloanlistDataGridView.Columns(3).Width = 40 brloanlistDataGridView.Columns(3).DefaultCellStyle.Format = MMM dd yyyy brNameToolStripLabel.Text = glSumDataGridView(1, glSumDataGridView.CurrentRow.Index).Value.ToString generalledgerDataGridView.DataSource = Nothing Catch ex As Exception Call showError(ex.Message.ToString) Finally Me.Cursor = Cursors.Default End Try End Sub Private Sub borrowerDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles searchBrDataGridView.CellContentClick End Sub Private Sub borrowerDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles searchBrDataGridView.CellDoubleClick gridFilter._sql = SELECT fk_borrowerid,fullname as [Borrower Name],sumofcr as [Sum of Cr], sumofdr as [Sum of Dr], Balance FROM qGLsummary gridFilter._criteria = WHERE fk_borrowerid = searchBrDataGridView(0, searchBrDataGridView.CurrentRow.Index).Value.ToString gridFilter._orderBy = ORDER BY Fullname Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, glSumDataGridView, True) Call TotalOfGLSum() Call glsum_totals() End Sub Private Sub borrowerloanDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles brloanlistDataGridView.CellContentDoubleClick End Sub Private Sub generalledgersummaryDataGridView_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles glSumDataGridView.CellDoubleClick Call viewGLSummaryToolStripButton1_Click(sender, e) End Sub Private Sub upd_newToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_newToolStripButton.Click Try upd_datedDateTimePicker.Value = Now upd_ToComboBox.SelectedIndex = 1 upd_isactiveCheckBox.Checked = True Call combolist(SELECT pk_accntID,pk_code FROM tblaccount ORDER BY description, pk_accntid, pk_code, upd_AccntComboBox) Call upd_AccntComboBox_SelectionChangeCommitted(sender, e) upd_AmtNumericUpDown.Value = 0 upd_nextduedateDateTimePicker.Value = DateAdd(DateInterval.Month, 1, CType(duedateTextBox.Text.ToString, Date)) upd_statusComboBox.SelectedIndex = 0 Call toggleOnUpdate() Catch ex As Exception Call showError(ex.ToString) End Try End Sub Private Sub upd_editToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles upd_editToolStripButton.Click Try Dim a As Integer = brloanDetailsDataGridView.CurrentRow.Index Call combolist(SELECT pk_accntID,pk_code FROM tblaccount ORDER BY description, pk_accntid, pk_code, upd_AccntComboBox) Call toggleOnUpdate() With brloanDetailsDataGridView upd_recidLabel.Text = .Item(0, a).Value.ToString upd_AccntComboBox.Text = .Item(1, a).Value.ToString upd_datedDateTimePicker.Value = CType(.Item(2, a).Value, Date) If CType(.Item(3, a).Value, Double) 0 Then entry is Cr upd_ToComboBox.Text = Cr upd_AmtNumericUpDown.Value = CType(.Item(3, a).Value, Decimal) Else upd_ToComboBox.Text = Dr upd_AmtNumericUpDown.Value = CType(.Item(4, a).Value, Decimal) End If upd_nextduedateDateTimePicker.Value = CType(duedateTextBox.Text.ToString, Date) upd_isactiveCheckBox.Checked = isactiveCheckBox.Checked End With Catch ex As Exception If Err.Number = 91 Then Call showError(Please click entry in General Ledger Details and click edit.) Else Call showError(Err.Number ex.Message) End If End Try End Sub Private Sub upd_applyToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_applyToolStripButton.Click If Not IsNumeric(upd_recidLabel.Text) Then MessageBox.Show(Please select entry from GL Details. Click OK to continue., strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information) Exit Sub End If If upd_AmtNumericUpDown.Value = 0 Then Call showError(Enter amount value.) Exit Sub End If Dim i As Integer, _PostAmountTo As String = String.Empty If MessageBox.Show(Are all entries correct?, strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then Try If upd_ToComboBox.Text.ToUpper = DR Then _PostAmountTo = dr Else _PostAmountTo = Cr If upd_recidLabel.Text = 0 Then cmdLocal = New OleDbCommand(INSERT INTO tblloandetails(fk_mloanid,fk_accntid,dated, _PostAmountTo ) VALUES(@fk_mloanid,@fk_accntid,@dated,@amt), mappDB.conn) Else cmdLocal = New OleDbCommand(UPDATE tblloandetails SET [emailprotected]/* */_mloanid,[emailprotected]/* */_accntid,[emailprotected]/* */, _PostAmountTo [emailprotected]/* */ WHERE pk_dloanid= upd_recidLabel.Text.ToString, mappDB.conn) End If With cmdLocal.Parameters .Add(@fk_mloanid, OleDbType.Integer, 0, fk_mloanid).Value = recIDLabel.Text .Add(@fk_accntid, OleDbType.Integer, 0, fk_accntid).Value = upd_AccntComboBox.SelectedValue .Add(@dated, OleDbType.Date, 0, dated).Value = upd_datedDateTimePicker.Value.ToString(MMM/dd/yyyy) .Add(@amt, OleDbType.Decimal, 0, _PostAmountTo).Value = upd_AmtNumericUpDown.Value End With i = cmdLocal.ExecuteNonQuery gridFilter._sql = SELECT pk_dloanid,pk_code as Particular,Dated,Cr,Dr FROM qloandetails gridFilter._criteria = WHERE fk_mloanid = brloanlistDataGridView(0, brloanlistDataGridView.CurrentRow.Index).Value.ToString gridFilter._orderBy = ORDER BY dated,pk_dloanid Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, brloanDetailsDataGridView, True) Call gldetails_totals() blIsActiveLoan = False For i = 0 To brLoanDetailsSumDataGridView.Rows.Count 1 If CType(brLoanDetailsSumDataGridView.Item(4, i).Value, Double) 0 Then blIsActiveLoan = True Exit For End If Next cmdLocal = New OleDbCommand(UPDATE tblloanmaster SET [emailprotected]/* */ WHERE pk_mloanid= recIDLabel.Text.ToString, mappDB.conn) With cmdLocal.Parameters .Add(@isactive, OleDbType.Boolean, 0, isactive).Value = blIsActiveLoan End With cmdLocal.ExecuteNonQuery() brloanlistDataGridView.Item(2, brloanlistDataGridView.CurrentRow.Index).Value = blIsActiveLoan end status check If blIsActiveLoan And MessageBox.Show(Update due date?, strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then update loan due date cmdLocal = New OleDbCommand(UPDATE tblloanmaster SET [emailprotected]/* */ WHERE pk_mloanid= recIDLabel.Text.ToString, mappDB.conn) With cmdLocal.Parameters .Add(@duedate, OleDbType.Date, 0, duedate).Value = upd_nextduedateDateTimePicker.Value.ToString(MMM dd yyyy) End With cmdLocal.ExecuteNonQuery() End If Call toggleOnUpdate() MessageBox.Show(brNameToolStripLabel.Text.ToUpper general ledger update success. Click OK to continue., strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information) Catch ex As Exception Call showError(ex.ToString) Finally i = Nothing cmdLocal = Nothing mappDB.close() End Try End If End Sub Private Sub gldetails_totals() With brloanDetailsDataGridView .Columns(2).DefaultCellStyle.Format = MMM dd yyyy .Columns(3).Width = 80 .Columns(3).DefaultCellStyle.Format = 0,0.00 .Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight .Columns(4).Width = 80 .Columns(4).DefaultCellStyle.Format = 0,0.00 .Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight End With gridFilter._sql = SELECT fk_mloanid,pk_code as Particular, sumofcr as Cr, sumofDr as Dr,Balance FROM qloandetailssum gridFilter._criteria = WHERE fk_mloanid = brloanlistDataGridView(0, brloanlistDataGridView.CurrentRow.Index).Value.ToString gridFilter._orderBy = Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, brLoanDetailsSumDataGridView, True) With brLoanDetailsSumDataGridView .Columns(2).DefaultCellStyle.Format = MMM dd yyyy For i As Integer = 2 To 4 .Columns(i).Width = 80 .Columns(i).DefaultCellStyle.Format = 0,0.00 .Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight Next End With End Sub Private Sub glsum_totals() Dim _totalCr As Double = 0 Dim _totalDr As Double = 0 Try For i As Integer = 0 To glSumDataGridView.Rows.Count 1 _totalCr += CType(glSumDataGridView.Item(2, i).Value, Double) _totalDr += CType(glSumDataGridView.Item(3, i).Value, Double) Next Catch End Try Try add grid formatting With glSumDataGridView .Columns(1).Width = 350 .Columns(2).DefaultCellStyle.Format = 0,0.00 .Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight .Columns(3).DefaultCellStyle.Format = 0,0.00 .Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight .Columns(4).DefaultCellStyle.Format = 0,0.00 .Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight End With With glSumTotalDataGridView .Item(1, 0).Value = FormatNumber(_totalCr, 2) .Item(1, 1).Value = FormatNumber(_totalDr, 2) .Item(1, 2).Value = FormatNumber(_totalCr _totalDr, 2) .Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight End With Catch ex As Exception Call showError(ex.Message) End Try End Sub Private Sub upd_calcToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_calcToolStripButton.Click Try System.Diagnostics.Process.Start(calc) Catch ex As Exception Call showError(ex.ToString) End Try End Sub Sub haltKeys(ByVal sender As Object, ByVal e As KeyPressEventArgs) Handles datedTextBox.KeyPress, duedateTextBox.KeyPress, _ comakerTextBox.KeyPress, remarkTextBox.KeyPress, payableTextBox.KeyPress, monthlyintTextBox.KeyPress, monthlypenaltyTextBox.KeyPress, _ principalTextBox.KeyPress, monthlyintTextBox.KeyPress, monthlypenaltyTextBox.KeyPress e.Handled = True End Sub Private Sub export_glsumToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles glsum_exportToolStripButton.Click If (glSumDataGridView.Rows.Count 1) 0 Then Exit Sub Dim h As Integer = 0, r As Integer = 4 excelApp = New Excel.Application excelWB = excelApp.Workbooks.Open(Application.StartupPath templatesglsummary.xlt) excelWS = CType(excelWB.Sheets(1), Excel.Worksheet) With excelWS For h = 0 To glSumDataGridView.Rows.Count 1 r = h + 4 4 is the base row .Cells(r, 1) = h + 1 sn .Cells(r, 2) = glSumDataGridView.Item(1, h).Value.ToString .Cells(r, 3) = glSumDataGridView.Item(2, h).Value.ToString .Cells(r, 4) = glSumDataGridView.Item(3, h).Value.ToString .Cells(r, 5) = glSumDataGridView.Item(4, h).Value.ToString Next balance .Cells(6, ExcelColumns.colE) = =C r -D r r += 1 .Cells(r, ExcelColumns.colC) = =SUM(C4:C r 1 ) Cr total .Cells(r, ExcelColumns.colD) = =SUM(D4:D r 1 ) Dr Total .Cells(r, ExcelColumns.colE) = =C r -D r Balance Total .Cells(r, ExcelColumns.colA) = Total .Range(A r :B r).Merge() .Range(A r).HorizontalAlignment = -4131 End With Call drawBorder(A4:E r) excelApp.Visible = True clean up variables h = Nothing excelWS = Nothing excelWB = Nothing excelApp = Nothing End Sub Private Sub gl_viewToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles gldetails_viewToolStripButton.Click If (brloanDetailsDataGridView.Rows.Count 1) 0 Then Exit Sub Dim h As Integer = 0, r As Integer = 0 Dim s As String = String.Empty excelApp = New Excel.Application excelWB = excelApp.Workbooks.Open(Application.StartupPath templatesgldetails.xlt) excelWS = CType(excelWB.Sheets(1), Excel.Worksheet) With excelWS .Cells(3, ExcelColumns.colC) = brNameToolStripLabel.Text.ToString .Cells(4, ExcelColumns.colC) = loanRefLabel.Text.ToString .Cells(5, ExcelColumns.colC) = datedTextBox.Text.ToString .Cells(3, ExcelColumns.colG) = principalTextBox.Text.ToString .Cells(4, ExcelColumns.colG) = monthlyintTextBox.Text.ToString .Cells(5, ExcelColumns.colG) = monthlypenaltyTextBox.Text.ToString For h = 0 To brloanDetailsDataGridView.Rows.Count 1 r = h + 9 is the base row .Cells(r, ExcelColumns.colA) = h + 1 sn .Cells(r, ExcelColumns.colB) = brloanDetailsDataGridView.Item(1, h).Value.ToString .Cells(r, ExcelColumns.colD) = CType(brloanDetailsDataGridView.Item(2, h).Value.ToString, Date).ToString(MMM dd yyyy) .Cells(r, ExcelColumns.colE) = brloanDetailsDataGridView.Item(3, h).Value.ToString cr .Cells(r, ExcelColumns.colF) = brloanDetailsDataGridView.Item(4, h).Value.ToString dr balance col If r 10 Then .Cells(r, ExcelColumns.colG) = =G (r 1) +E r -F r Next r += 1 balance .Cells(6, ExcelColumns.colG) = =E r -F r .Cells(r, ExcelColumns.colE) = =SUM(E8:E r 1 ) .Cells(r, ExcelColumns.colF) = =SUM(F8:F r 1 ) .Cells(r, ExcelColumns.colG) = =E r -F r .Cells(r, ExcelColumns.colA) = Total .Range(A r :D r).Merge() .Range(A r).HorizontalAlignment = -4131 End With Call drawBorder(A10:G r) excelApp.Visible = True h = Nothing excelWS = Nothing excelWB = Nothing excelApp = Nothing End Sub Private Sub what_accntButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles what_accntButton.Click Try cmdLocal = New OleDbCommand(SELECT description from tblaccount WHERE pk_accntID= upd_AccntComboBox.SelectedValue.ToString, mappDB.conn) Dim rd As OleDbDataReader = cmdLocal.ExecuteReader rd.Read() If rd.HasRows Then MessageBox.Show(upd_AccntComboBox.Text = rd(0).ToString . Click OK to continue., strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information) rd = Nothing cmdLocal = Nothing Catch End Try End Sub Private Sub AccountToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AccountToolStripMenuItem.Click rptAccountForm.ShowDialog() End Sub Sub toggleOnUpdate() brloanlistDataGridView.Enabled = Not brloanlistDataGridView.Enabled brloanDetailsDataGridView.Enabled = Not brloanDetailsDataGridView.Enabled upd_newToolStripButton.Enabled = Not upd_newToolStripButton.Enabled upd_editToolStripButton.Enabled = Not upd_editToolStripButton.Enabled upd_applyToolStripButton.Enabled = Not upd_applyToolStripButton.Enabled upd_cancelToolStripButton.Enabled = Not upd_cancelToolStripButton.Enabled End Sub Private Sub upd_cancelToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_cancelToolStripButton.Click If MessageBox.Show(Cancel update?, strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then upd_recidLabel.Text = 0 Call toggleOnUpdate() End If End Sub Private Sub borrowerloanDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles brloanlistDataGridView.CellContentClick End Sub Private Sub TabControl1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TabControl1.Click If upd_applyToolStripButton.Enabled = True Then MessageBox.Show(Please complete updating/cancel GL of brNameToolStripLabel.Text.ToUpper . Click OK to continue., strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information) TabControl1.SelectedIndex = 1 End If End Sub Private Sub DueDateToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DueDateToolStripMenuItem.Click rptDueDateForm.ShowDialog() End Sub Private Sub rptBorrowerToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rptBorrowerToolStripMenuItem.Click Me.Cursor = Cursors.WaitCursor Dim r As Integer = 4 excelApp = New Excel.Application excelWB = excelApp.Workbooks.Open(Application.StartupPath templatesborrower.xlt) excelWS = CType(excelWB.Sheets(1), Excel.Worksheet) cmdLocal = New OleDbCommand(SELECT * from tblborrower ORDER BY lastname, mappDB.conn) Dim rd As OleDbDataReader = cmdLocal.ExecuteReader While rd.Read With excelWS .Cells(r, ExcelColumns.colA) = r 3 .Cells(r, ExcelColumns.colB) = rd(lastname).ToString .Cells(r, ExcelColumns.colC) = rd(firstname).ToString .Cells(r, ExcelColumns.colD) = rd(jobtitle).ToString rd(company).ToString .Cells(r, ExcelColumns.colE) = rd(contactnos).ToString .Cells(r, ExcelColumns.colF) = rd(address).ToString r += 1 End With End While If r 4 Then Call drawBorder(A5:F r 1) excelApp.Visible = True mappDB.close() rd = Nothing cmdLocal = Nothing r = Nothing excelWS = Nothing excelWB = Nothing excelApp = Nothing Me.Cursor = Cursors.Default End Sub Private Sub AccountToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AccountToolStripMenuItem1.Click accountForm.ShowDialog() End Sub Private Sub LogOffToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LogOffToolStripMenuItem.Click Me.Visible = False loginForm.Show() End Sub Private Sub borrowerloanDataGridView_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles brloanlistDataGridView.CellEnter Dim rd As OleDbDataReader recIDLabel.Text = 0 Try gridFilter._sql = SELECT pk_dloanid,pk_code as Particular,Dated,Cr,Dr FROM qloandetails gridFilter._criteria = WHERE fk_mloanid = brloanlistDataGridView(0, brloanlistDataGridView.CurrentRow.Index).Value.ToString gridFilter._orderBy = ORDER BY dated,pk_dloanid Call fillGrid(gridFilter._sql, gridFilter._criteria, gridFilter._orderBy, brloanDetailsDataGridView, True) cmdLocal = New OleDbCommand(SELECT * FROM tblloanmaster WHERE pk_mloanid= brloanlistDataGridView(0, brloanlistDataGridView.CurrentRow.Index).Value.ToString, mappDB.conn) rd = cmdLocal.ExecuteReader rd.Read() If rd.HasRows Then recIDLabel.Text = rd(pk_mloanid).ToString loanRefLabel.Text = rd(pk_loanref).ToString datedTextBox.Text = CType(rd(dated).ToString, Date).ToString(MMM dd yyyy) duedateTextBox.Text = CType(rd(duedate).ToString, Date).ToString(MMM dd yyyy) comakerTextBox.Text = rd(comaker).ToString remarkTextBox.Text = rd(remark).ToString principalTextBox.Text = FormatNumber(rd(principal).ToString, 2).ToString monthlyintTextBox.Text = FormatNumber(rd(interest).ToString, 2).ToString monthlypenaltyTextBox.Text = FormatNumber(rd(penalty).ToString, 2).ToString isactiveCheckBox.Checked = CType(rd(isactive), Boolean) monthInterestNumericUpDown.Value = CType(rd(interest).ToString, Decimal) payableTextBox.Text = FormatNumber(CType(principalTextBox.Text, Double) * (CType(monthlyintTextBox.Text, Double) / 100), 2).ToString monthPenaltyNumericUpDown.Value = CType(rd(penalty).ToString, Decimal) End If Call gldetails_totals() Catch ex As Exception Call showError(ex.Message) Finally mappDB.close() rd = Nothing End Try End Sub Private Sub quitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles quitToolStripMenuItem.Click If MessageBox.Show(Do you really want to quit now?, strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then Application.Exit() End Sub Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick nowToolStripLabel.Text = System.DateTime.Now.ToString(MMM dd yyyy HH:mm:ss tt) End Sub Private Sub previousToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles previousToolStripButton.Click bsGLSummary.MovePrevious() End Sub Private Sub generalledgersummaryDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles glSumDataGridView.CellContentClick End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) brloanDetailsDataGridView.Rows(1).Selected = True End Sub Private Sub BackupDatabaseToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackupDatabaseToolStripMenuItem.Click End Sub Private Sub ToolStripLabel8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) End Sub Private Sub upd_isactiveCheckBox_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_isactiveCheckBox.CheckedChanged End Sub Private Sub upd_isactiveCheckBox_CheckStateChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles upd_isactiveCheckBox.CheckStateChanged End Sub Private Sub upd_isactiveCheckBox_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles upd_isactiveCheckBox.Click If upd_ToComboBox.Text.ToUpper = DR Then upd_isactiveCheckBox.Checked = True GoTo close End If Dim x As Double = 0 For i As Integer = 0 To brLoanDetailsSumDataGridView.Rows.Count 1 x += CType(brLoanDetailsSumDataGridView.Item(2, i).Value, Double) Next If x upd_AmtNumericUpDown.Value 0 Then upd_isactiveCheckBox.Checked = True GoTo close End If Exit Sub close: MessageBox.Show(Zero balance loan cannot be close., strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information) End Sub Private Sub upd_AccntComboBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles upd_AccntComboBox.SelectedIndexChanged End Sub Private Sub upd_AccntComboBox_SelectionChangeCommitted(ByVal sender As Object, ByVal e As System.EventArgs) Handles upd_AccntComboBox.SelectionChangeCommitted retrieve balance of selected account For i As Integer = 0 To brLoanDetailsSumDataGridView.Rows.Count 1 If brLoanDetailsSumDataGridView.Item(1, i).Value.ToString = upd_AccntComboBox.Text.ToString Then upd_AmtNumericUpDown.Value = CType(brLoanDetailsSumDataGridView.Item(4, i).Value, Decimal) Exit For End If Next End Sub Private Sub generalledgersummaryDataGridView_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles glSumDataGridView.CellEnter Try selectedrowindexToolStripTextBox.Text = CStr(e.RowIndex + 1) Catch End Try End Sub Private Sub filtervalueTextBox_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles filtervalueTextBox.KeyDown If e.KeyCode = Keys.Enter Then Call searchfilterButton_Click(sender, e) End Sub Private Sub nextToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles nextToolStripButton.Click bsGLSummary.MoveNext() End Sub Private Sub firstToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles firstToolStripButton.Click bsGLSummary.MoveFirst() End Sub Private Sub lastToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lastToolStripButton.Click bsGLSummary.MoveLast() End Sub Private Sub incZeroBalToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles incZeroBalToolStripButton.Click incZeroBalToolStripButton.Checked = Not incZeroBalToolStripButton.Checked End Sub Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click Dim s As String = strApplicationName s += vbCrLf ConfigurationManager.AppSettings(client).ToString s += vbCrLf vbCrLf Dedicated To: s += vbCrLf Ethan,Shane,Althea and Khristine s += vbCrLf vbCrLf Developed By: vbCrLf SoftIndex Solutions 2009 s += vbCrLf Jonathan Bantang MessageBox.Show(s, strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information) End Sub End Class Imports System.Data.OleDb Public Class accountForm Dim oadmainLocal As OleDbDataAdapter Dim dtmaindatatable As DataTable Dim bs As New BindingSource Private Sub accountForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Call fillGrid(SELECT pk_accntid, pk_code as Code, Description FROM tblaccount, ) End Sub Private Sub mainDataGridView_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles mainDataGridView.CellEnter Try selectedrowindexToolStripTextBox.Text = CStr(e.RowIndex + 1) Catch End Try End Sub Private Sub closeToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles closeToolStripButton.Click Me.Close() End Sub Private Sub saveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveToolStripButton.Click Dim cmdblder As OleDbCommandBuilder = New OleDbCommandBuilder(oadmainLocal) Dim _msg As String = String.Empty Try oadmainLocal.Update(dtmaindatatable) _msg = Update success. Click OK to continue Catch ex As Exception _msg = ERROR : ex.Message Finally MessageBox.Show(_msg, strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information) cmdblder = Nothing _msg = Nothing End Try End Sub Private Sub fillGrid(ByVal _sql As String, ByVal _criteria As String) oadmainLocal = New OleDbDataAdapter(_sql + _criteria + ORDER BY pk_code, mappDB.conn) dtmaindatatable = New DataTable Try oadmainLocal.Fill(dtmaindatatable) bs = New BindingSource bs.DataSource = dtmaindatatable With mainDataGridView .DataSource = Nothing .DataSource = dtmaindatatable .DataSource = bs format grid .Columns(0).Visible = False .Columns(1).Width = 150 .Columns(2).Width = 320 .SelectionMode = DataGridViewSelectionMode.FullRowSelect .Cursor = Cursors.Hand TODO word wrap desc columns selectedrowindexToolStripTextBox.Text = 1 totalrowsToolStripLabel.Text = of .RowCount 1 End With bs = Nothing Catch ex As Exception MessageBox.Show(query error : ex.Message) Finally mappDB.close() End Try End Sub Private Sub removeToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles removeToolStripButton.Click Try mainDataGridView.Rows.Remove(mainDataGridView.CurrentRow) Catch ex As Exception Call showError(ex.ToString) End Try End Sub Private Sub previewToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles previewToolStripButton.Click End Sub Private Sub nextrowToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nextrowToolStripButton.Click bs.MoveNext() End Sub Private Sub firstrowToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles firstrowToolStripButton.Click bs.MoveFirst() End Sub Private Sub lastrowToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lastrowToolStripButton.Click bs.MoveLast() End Sub Private Sub previousrowToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles previousrowToolStripButton.Click bs.MovePrevious() End Sub Private Sub addToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addToolStripButton.Click Call lastrowToolStripButton_Click(sender, e) End Sub Private Sub refreshToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles refreshToolStripButton.Click End Sub End Class Imports System.Data.OleDb Public Class borrowerForm Dim oadmainLocal As OleDbDataAdapter Dim dtmaindatatable As DataTable Dim bs As New BindingSource Dim strAllTableColumns As String = SELECT pk_borrowerid,lastname as [Last Name], firstname as [First Name],jobtitle as [Job Title], contactnos as [Contact Nos],Address,Company FROM tblborrower Private Sub borrowerForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing oadmainLocal = Nothing dtmaindatatable = Nothing End Sub Private Sub borrowerForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Call fillGrid(strAllTableColumns, ) filteroptionToolStripButton.SelectedIndex = 0 End Sub Private Sub mainDataGridView_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles mainDataGridView.CellEnter Try currentRowToolStripTextBox.Text = CStr(e.RowIndex + 1) Catch End Try End Sub local fillgrid. perform table update Private Sub fillGrid(ByVal _sql As String, ByVal _criteria As String) oadmainLocal = New OleDbDataAdapter(_sql + _criteria + ORDER BY lastname, mappDB.conn) dtmaindatatable = New DataTable Try oadmainLocal.Fill(dtmaindatatable) bs = New BindingSource bs.DataSource = dtmaindatatable With mainDataGridView .DataSource = Nothing .DataSource = bs format grid .Columns(0).Visible = False .Columns(1).Width = 120 .Columns(2).Width = 120 .Columns(3).Width = 150 .Columns(4).Width = 120 .Columns(5).Width = 150 .Columns(6).Width = 150 .SelectionMode = DataGridViewSelectionMode.FullRowSelect .Cursor = Cursors.Hand TODO word wrap desc columns currentRowToolStripTextBox.Text = 1 rowCountToolStripLabel.Text = of .RowCount 1 End With Catch ex As Exception MessageBox.Show(query error : ex.Message) Finally mappDB.close() End Try End Sub Private Sub closeToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles closeToolStripButton.Click Me.Close() End Sub Private Sub saveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles saveToolStripButton.Click Dim cmdblder As OleDbCommandBuilder = New OleDbCommandBuilder(oadmainLocal) Dim _msg As String = String.Empty Try oadmainLocal.Update(dtmaindatatable) _msg = Update success. Click OK to continue Catch ex As Exception _msg = ERROR : ex.Message Finally MessageBox.Show(_msg, strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information) cmdblder = Nothing _msg = Nothing End Try End Sub Private Sub searchmainfilterToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles searchmainfilterToolStripButton.Click Select Case filteroptionToolStripButton.SelectedIndex Case 0 all Call fillGrid(strAllTableColumns, ) Case 1 firstname Call fillGrid(strAllTableColumns, WHERE firstname LIKE filtervalueToolStripTextBox.Text.ToString %) Case 2 lastname Call fillGrid(strAllTableColumns, WHERE lastname LIKE filtervalueToolStripTextBox.Text.ToString %) Case 3 company Call fillGrid(strAllTableColumns, WHERE company LIKE filtervalueToolStripTextBox.Text.ToString %) End Select End Sub Private Sub removefilterToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles removefilterToolStripButton.Click filteroptionToolStripButton.SelectedIndex = 0 Call searchmainfilterToolStripButton_Click(sender, e) End Sub Private Sub removeToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles removeToolStripButton.Click Try mainDataGridView.Rows.Remove(mainDataGridView.CurrentRow) Catch ex As Exception Call showError(ex.ToString) End Try End Sub Private Sub nextToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nextToolStripButton.Click bs.MoveNext() End Sub Private Sub firstToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles firstToolStripButton.Click bs.MoveFirst() End Sub Private Sub lastToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lastToolStripButton.Click bs.MoveLast() End Sub Private Sub previousToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles previousToolStripButton3.Click bs.MovePrevious() End Sub Private Sub mainDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles mainDataGridView.CellContentClick End Sub Private Sub addToolStripButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addToolStripButton.Click Call lastToolStripButton_Click(sender, e) bs.AddNew() End Sub End Class Imports System.Data.OleDb Public Class borrowForm Dim cmdLocal As OleDbCommand Private Sub closeToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles closeToolStripButton.Click Me.Close() End Sub Private Sub applyToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles applyToolStripButton.Click If Not IsNumeric(recIDLabel.Text) Then MessageBox.Show(Click New if you want to enter loan. Click OK to continue., strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information) Exit Sub End If If principalNumericUpDown.Value = 0 Then MessageBox.Show(Principal cannot be zero value. Click OK to continue, strApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Information) Exit Sub End If If MessageBox.Show(Are all entries correct?, strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then Exit Sub Call PayableTotal(sender, e) Dim i As Integer = 0 Select Case CType(recIDLabel.Text.ToString, Integer) Case 0 cmdLocal = New OleDbCommand(INSERT INTO tblloanmaster(pk_loanref,fk_borrowerid,dated,duedate,comaker,remark,principal,interest,penalty) values(@pk_loanref,@fk_borrowerid,@dated,duedate,comaker,remark,@principal,@interest,@penalty), mappDB.conn) Case Else cmdLocal = New OleDbCommand(UPDATE tblloanmaster SET [emailprotected]/* */_borrowerid,[emailprotected]/* */,[emailprotected]/* */,[emailprotected]/* */,[emailprotected]/* */,[emailprotected]/* */ WHERE pk_mloanid= recIDLabel.Text, mappDB.conn) GoTo update_only End Select With cmdLocal.Parameters .Clear() .Add(@pk_loanref, OleDbType.VarChar, 50, pk_loanref).Value = loanRefLabel.Text .Add(@fk_borrowerID, OleDbType.Integer, 0, fk_borrowerID).Value = borrowerComboBox.SelectedValue .Add(@dated, OleDbType.Date, 0, dated).Value = loanDateTimePicker.Value.ToString(MMM/dd/yyyy) .Add(@duedate, OleDbType.Date, 0, duedate).Value = duedateDateTimePicker.Value.ToString(MMM/dd/yyyy) .Add(@nextduedate, OleDbType.Date, 0, nextduedate).Value = duedateDateTimePicker.Value.ToString(MMM/dd/yyyy) .Add(@comaker, OleDbType.VarChar, 50, comaker).Value = comakerTextBox.Text .Add(@remark, OleDbType.VarChar, 50, remark).Value = remarkTextBox.Text .Add(@principal, OleDbType.Decimal, 0, principal).Value = principalNumericUpDown.Value .Add(@interest, OleDbType.Decimal, 0, interest).Value = monthInterestNumericUpDown.Value .Add(@penalty, OleDbType.Decimal, 0, penalty).Value = monthPenaltyNumericUpDown.Value End With i = cmdLocal.ExecuteNonQuery() If i 0 Then cmdLocal = New OleDbCommand(SELECT pk_mloanid FROM tblloanmaster WHERE pk_loanref= loanRefLabel.Text.ToString , mappDB.conn) Dim rd As OleDbDataReader = cmdLocal.ExecuteReader rd.Read() recIDLabel.Text = rd(0).ToString rd = Nothing If recIDLabel.Text 0 Then open the last rowid post to principal+due date to GL cmdLocal = New OleDbCommand(INSERT INTO tblloandetails(fk_mloanid,fk_accntid,dated,cr) VALUES(@fk_mloanid,@fk_accntid,@dated,@cr), mappDB.conn) With cmdLocal.Parameters .Add(@fk_mloanid, OleDbType.Integer, 0, fk_mloanid).Value = recIDLabel.Text .Add(@fk_accntid, OleDbType.Integer, 0, fk_accntid).Value = principalComboBox.SelectedValue .Add(@dated, OleDbType.Date, 0, dated).Value = loanDateTimePicker.Value .Add(@Cr, OleDbType.Decimal, 0, Cr).Value = principalNumericUpDown.Value End With cmdLocal.ExecuteNonQuery() post to principal interest+due date to GL cmdLocal = New OleDbCommand(INSERT INTO tblloandetails(fk_mloanid,fk_accntid,dated,cr) VALUES(@fk_mloanid,@fk_accntid,@dated,@cr), mappDB.conn) With cmdLocal.Parameters .Add(@fk_mloanid, OleDbType.Integer, 0, fk_mloanid).Value = recIDLabel.Text .Add(@fk_accntid, OleDbType.Integer, 0, fk_accntid).Value = interestComboBox.SelectedValue .Add(@dated, OleDbType.Date, 0, dated).Value = loanDateTimePicker.Value .Add(@Cr, OleDbType.Decimal, 0, Cr).Value = CType(payableTextBox.Text, Double) End With cmdLocal.ExecuteNonQuery() End If cmdLocal = Nothing End If update_only: mappDB.close() If MessageBox.Show(borrowerComboBox.Text.ToUpper loan is now saved. Create New Loan?, strApplicationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then Call newToolStripButton_Click(sender, e) End Sub Private Sub newLoanForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Call combolist(SELECT * from qborrower, pk_borrowerid, fullname, borrowerComboBox) End Sub Function loanID() As String cmdLocal = New OleDbCommand(SELECT COUNT(pk_mloanid) as x from tblloanmaster, mappDB.conn) Dim rd As OleDbDataReader = cmdLocal.ExecuteReader rd.Read() Return Date.Today.ToString(MMdd) - CStr(Format(CType(rd(0), Integer) + 1, 0000)) rd = Nothing cmdLocal = Nothing mappDB.close() End Function Private Sub newToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles newToolStripButton.Click loanRefLabel.Text = loanID() recIDLabel.Text = 0 loanDateTimePicker.Value = Now duedateDateTimePicker.Value = DateAdd(DateInterval.Month, 1, Now) payableTextBox.Text = 0.00 principalNumericUpDown.Value = 0 remarkTextBox.Text = Loan release date date. monthInterestNumericUpDown.Value = 10 monthPenaltyNumericUpDown.Value = 3 Call combolist(SELECT pk_accntID,pk_code FROM tblaccount ORDER BY description, pk_accntid, pk_code, principalComboBox) Call combolist(SELECT pk_accntID,pk_code FROM tblaccount ORDER BY description, pk_accntid, pk_code, interestComboBox) End Sub Sub PayableTotal(ByVal sender As Object, ByVal e As EventArgs) Handles principalNumericUpDown.ValueChanged, monthInterestNumericUpDown.ValueChanged payableTextBox.Text = CStr(CType(principalNumericUpDown.Value, Double) * (CType(monthInterestNumericUpDown.Value, Double) / 100)) End Sub Private Sub payableTextBox_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles payableTextBox.KeyPress e.Handled = True End Sub Private Sub payableTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles payableTextBox.TextChanged End Sub End Class Imports System.data.OleDb Public Class rptAccountForm Dim cmdlocal As OleDbCommand Dim rd As OleDbDataReader Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Call combolist(SELECT pk_accntid,pk_code from tblaccount, pk_accntid, pk_code, AccntComboBox) End Sub Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click Me.Cursor = Cursors.WaitCursor Dim strCriteria As String = String.Empty Dim i As Integer = 1 Dim r As Integer = 5 If AccntComboBox.Text.ToUpper ALL Then strCriteria = AND pk_accntid= AccntComboBox.SelectedValue.ToString excelApp = New Excel.Application excelWB = excelApp.Workbooks.Open(Application.StartupPath templatesaccount.xlt) excelWS = CType(excelWB.Sheets(1), Excel.Worksheet) excelWS.Cells(1, ExcelColumns.colF) = AccntComboBox.Text.ToString excelWS.Cells(2, ExcelColumns.colF) = fromDateTimePicker.Value.ToString(MMM dd yyyy) Chr(10) toDateTimePicker.Value.ToString(MMM dd yyyy) cmdlocal = New OleDbCommand(SELECT * from qrptaccount WHERE dated BETWEEN # fromDateTimePicker.Value.ToString(MMM dd yyyy) # AND # toDateTimePicker.Value.ToString(MMM dd yyyy) # strCriteria, mappDB.conn) rd = cmdlocal.ExecuteReader While rd.Read With excelWS .Cells(r, ExcelColumns.colA) = r 4 .Cells(r, ExcelColumns.colB) = rd(pk_code).ToString .Cells(r, ExcelColumns.colC) = rd(fullname).ToString .Cells(r, ExcelColumns.colD) = CType(rd(dated).ToString, Date).ToString(MMM dd yyyy) .Cells(r, ExcelColumns.colE) = rd(cr).ToString .Cells(r, ExcelColumns.colF) = rd(dr).ToString r += 1 End With End While add cr and dr sum excelWS.Cells(r, ExcelColumns.colE) = =SUM(E5:E r 1 ) excelWS.Cells(r, ExcelColumns.colF) = =SUM(F5:F r 1 ) excelWS.Cells(r, ExcelColumns.colA) = Total excelWS.Range(A r :D r).Merge() excelWS.Range(A r).HorizontalAlignment = -4131 If r 6 Then Call drawBorder(A6:F r) excelApp.Visible = True clean up variables mappDB.close() rd = Nothing cmdlocal = Nothing r = Nothing excelWS = Nothing excelWB = Nothing excelApp = Nothing Me.Cursor = Cursors.Default End Sub Private Sub ToolStripButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click Me.Close() End Sub Private Sub viewAllToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles viewAllToolStripButton.Click End Sub End Class Imports System.Data.OleDb Public Class rptDueDateForm Dim cmdlocal As OleDbCommand Private Sub ToolStripButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click Me.Cursor = Cursors.WaitCursor Dim r As Integer = 4 excelApp = New Excel.Application excelWB = excelApp.Workbooks.Open(Application.StartupPath templatesduedate.xlt) excelWS = CType(excelWB.Sheets(1), Excel.Worksheet) cmdlocal = New OleDbCommand(SELECT * from qrptduedate WHERE duedate BETWEEN # fromDateTimePicker.Value.ToString(MMM dd yyyy) # AND # toDateTimePicker.Value.ToString(MMM dd yyyy) # ORDER BY duedate, mappDB.conn) Dim rd As OleDbDataReader = cmdlocal.ExecuteReader While rd.Read With excelWS .Cells(r, ExcelColumns.colA) = r 3 .Cells(r, ExcelColumns.colB) = rd(fullname).ToString .Cells(r, ExcelColumns.colC) = rd(pk_loanref).ToString .Cells(r, ExcelColumns.colD) = CType(rd(duedate).ToString, Date).ToString(MMM dd yyyy) .Cells(r, ExcelColumns.colE) = rd(sumofcr).ToString .Cells(r, ExcelColumns.colF) = rd(sumofdr).ToString .Cells(r, ExcelColumns.colG) = rd(balance).ToString r += 1 End With End While add cr and dr sum excelWS.Cells(r, ExcelColumns.colE) = =SUM(E4:E r 1 ) excelWS.Cells(r, ExcelColumns.colF) = =SUM(F4:F r 1 ) excelWS.Cells(r, ExcelColumns.colG) = =SUM(G4:G r 1 ) excelWS.Cells(r, ExcelColumns.colA) = Total excelWS.Range(A r :D r).Merge() excelWS.Range(A r).HorizontalAlignment = -4131 If r 5 Then End If Call drawBorder(A4:G r) excelApp.Visible = True clean up variables mappDB.close() rd = Nothing cmdlocal = Nothing r = Nothing excelWS = Nothing excelWB = Nothing excelApp = Nothing Me.Cursor = Cursors.Default End Sub Private Sub ToolStripButton2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click Me.Close() End Sub End Class