MSFlexGrid - Help with looping issue. 1

[AccessGuruCarl]

Hello,
Need help fixing a looping issue, and a few others.

First off I'm new to VB, After searching for vb sample of FlexGrid with amortization schedule I had no luck. I found a sample that outputted the data to excel so I starting playing around with it, after finding a few links on how to use the MSFlexGrid.

My 1st issue isn't really a FlexGrid problem it's with the looping routine for the current year. I keep errors when try to get just the current year.

Here is the form controls with names to duplicate form.
Textbox - txtLoan
Combobox - cboTerm
Textbox - txtRate
Label - lblPayment
CommandButton - Command1
MSFlexGrid - MSHFlexGrid1
' This one will come into play later
Textbox - txtDate

Question 1
The button click creates an amortization schedule.
My problem is the looping the current year. And setting the Event... Error is Out of Script!

It should loop the current year, Create Yrly Total then loop again.

I've included my code so you can duplicate the form.

2nd Question...
How do I fix the Final Payment Issue...
From looking at other schedules created on the net,
It looks like they take the balance due as the principle, add the interest.. To create the payment
I end up with a negitive number as a balance no matter what I try

3rd Question...
Now that I have it... How do I print it?

4th Question...
Deals with txtDate
Sometimes you need the beginning of the month, other times you need the end of the month
If you can easily fix this instead of the current code I'd really appreciate it.
I'm guessing this could be a combobox?
Beginning, End
But I'm sure at some time they'll want to enter a date like the 15th of the month. So I'd prefer the Textbox

Here is the code to create the form

Option Explicit
'   Dimension the variables for Loan amount, Interest Rate,
'   and Monthly Payment variable in the Declarations section.
Dim dLoan, dRate, dPayment As Double
Dim iYears As Integer

Private Sub Command1_Click()
Dim iRow, iMonth, iYear, iPayment As Integer
Dim iStartRow, iEndRow As Integer
Dim dTotInterest, dTotPrinciple, dTotPayments As Double
Dim dYrlyInterest, dYrlyPrinciple, dYryPayment As Double
Dim i As Integer

Dim strTemp As String   'Used for debugging
'Resize the form to include the MSFlexGrid
    Me.Width = 9390

'Convert the Loan amount to a double and store it in dLoan
    dLoan = CDbl(Trim(txtLoan.Text))
'Convert the Interest Rate to a double and store it in dRate
    dRate = CDbl(Trim(txtRate.Text))
'Convert dRate to a percent value (required by the Pmt function)
    If dRate > 1 Then dRate = dRate / 100
'Convert the Years of Loan to an Integer and store it in iYears
    iYears = CInt(cboTerm.Text)
'Use the Pmt function to calculate the Monthly Payment
    dPayment = Pmt(dRate / 12, iYears * 12, -1 * dLoan)
'Display the Monthly Payment in the lblPayment Label
    lblPayment.Caption = lblPayment.Caption & Format(dPayment, "$#,##0.00")

'Initialize the ProgressBar's Maximum property
'    and increment it 2 ticks as the process starts.
    ProgressBar1.Max = iYears * 12
    ProgressBar1.Value = 2
    ProgressBar1.Visible = True

' Create six columns.
    MSHFlexGrid1.Cols = 6

'Label the column headings for the flex grid
    MSHFlexGrid1.TextMatrix(0, 0) = "Event"
    MSHFlexGrid1.TextMatrix(0, 1) = "Date"
    MSHFlexGrid1.TextMatrix(0, 2) = "Payment Amt"
    MSHFlexGrid1.ColWidth(2) = 1100 'Resize Column Width to include Heading Text
    MSHFlexGrid1.TextMatrix(0, 3) = "Interest"
    MSHFlexGrid1.TextMatrix(0, 4) = "Principle"
    MSHFlexGrid1.TextMatrix(0, 5) = "Balance"
    MSHFlexGrid1.ColWidth(5) = 1220 'Resize Column Width to include Heading Text

'Populate the MSFlexGrid
'Enter Loan Details
MSHFlexGrid1.AddItem ""
MSHFlexGrid1.TextMatrix(1, 0) = "Loan"
MSHFlexGrid1.TextMatrix(1, 1) = DateSerial(Year(Now), Month(Now), 1)
MSHFlexGrid1.TextMatrix(1, 5) = Format(dLoan, "$#,##0.00")
iRow = 1
'Run Amortization Schedule
    For iYear = 1 To iYears
      iRow = iRow + 1
      iStartRow = iRow
        For iMonth = 1 To 12
          MSHFlexGrid1.AddItem ""
    'The Payment # Column
          iPayment = iPayment + 1
          MSHFlexGrid1.TextMatrix(iRow, 0) = iPayment
    'The Date Column
strTemp = DateSerial(Year(Now), Month(Now) + iPayment, 1)
          MSHFlexGrid1.TextMatrix(iRow, 1) = DateSerial(Year(Now), _
                  Month(Now) + iPayment, 1)
    'The Payments Column
          MSHFlexGrid1.TextMatrix(iRow, 2) = Format(dPayment, "$#,##0.00")
    'The Interest Column
strTemp = dLoan * (dRate / 12)
          MSHFlexGrid1.TextMatrix(iRow, 3) = _
              Format(dLoan * (dRate / 12), "$#,##0.00")
    'The Principle Column
          MSHFlexGrid1.TextMatrix(iRow, 4) = _
              Format((dPayment - MSHFlexGrid1.TextMatrix(iRow, 3)), "$#,##0.00")
    'The Balance Remaining Column
          dLoan = dLoan - MSHFlexGrid1.TextMatrix(iRow, 4)
          MSHFlexGrid1.TextMatrix(iRow, 5) = Format(dLoan, "$#,##0.00")
    'Increment Current Row
          iRow = iRow + 1
'======================
'DOESN"T WORK
        'Check to see if loan balance is less than payment
        'ADJUST FINAL PAYMENT DETAILS
        On Error Resume Next
'        If MSHFlexGrid1.TextMatrix(iRow - 1, 5) < dPayment Then
'          MSHFlexGrid1.TextMatrix(iRow, 4) = MSHFlexGrid1.TextMatrix(iRow - 1, 5)
'          dPayment = MSHFlexGrid1.TextMatrix(iRow - 1, 3) + MSHFlexGrid1.TextMatrix(iRow - 1, 4)
'          MSHFlexGrid1.TextMatrix(iRow, 3) = _
'            Format(dLoan * (dRate / 12), "$#,##0.00")
'        'MSHFlexGrid1.TextMatrix(iRow, 4) = _
            Format((dPayment - MSHFlexGrid1.TextMatrix(iRow, 3)), "$#,##0.00")
        'dLoan = dLoan - MSHFlexGrid1.TextMatrix(iRow, 4)
'          dLoan = 0
'          MSHFlexGrid1.TextMatrix(iRow, 5) = Format(dLoan, "$#,##0.00")
        'iRow = iRow + 1
'        End If
'=======================
    'Increment the ProgressBar
        If ProgressBar1.Value + 1 < ProgressBar1.Max Then
            ProgressBar1.Value = ProgressBar1.Value + 1
        End If
        Next iMonth
    iEndRow = iRow - 1
'Sum the Payments, Interest & Principle for current year
'Add Yearly Total to Running Total
      For i = iStartRow To (iEndRow - 1)
        dYryPayment = dYryPayment + CDbl(MSHFlexGrid1.TextMatrix(i, 2))
      Next i
      dTotPayments = dTotPayments + dYryPayment
      For i = iStartRow To (iEndRow - 1)
        dYrlyInterest = dYrlyInterest + CDbl(MSHFlexGrid1.TextMatrix(i, 3))
      Next i
      dTotInterest = dTotInterest + dYrlyInterest
      For i = iStartRow To (iEndRow - 1)
        dYrlyPrinciple = dYrlyPrinciple + CDbl(MSHFlexGrid1.TextMatrix(i, 4))
      Next i
      dTotPrinciple = dTotPrinciple + dYrlyPrinciple
        
        
strTemp = Right(MSHFlexGrid1.TextMatrix(iRow - 1, 1), 4)
        
      MSHFlexGrid1.AddItem ""
      MSHFlexGrid1.TextMatrix(iRow, 0) = strTemp & " Total"
      MSHFlexGrid1.TextMatrix(iRow, 2) = Format(dYryPayment, "$#,##0.00")
      MSHFlexGrid1.TextMatrix(iRow, 3) = Format(dYrlyInterest, "$#,##0.00")
      MSHFlexGrid1.TextMatrix(iRow, 4) = Format(dYrlyPrinciple, "$#,##0.00")
    'Reset Yearly Total to Zero
      dYryPayment = 0
      dYrlyInterest = 0
      dYrlyPrinciple = 0
    Next iYear
'Display the Grand Totals
    MSHFlexGrid1.AddItem ""
    MSHFlexGrid1.TextMatrix(iRow + 1, 0) = "Grand Total"
    MSHFlexGrid1.TextMatrix(iRow + 1, 2) = Format(dTotPayments, "$#,##0.00")
    MSHFlexGrid1.TextMatrix(iRow + 1, 3) = Format(dTotInterest, "$#,##0.00")
    MSHFlexGrid1.TextMatrix(iRow + 1, 4) = Format(dTotPrinciple, "$#,##0.00")
'Hide the progessbar, when the grid is populated
    ProgressBar1.Visible = False

End Sub

Private Sub Form_Load()

Dim i As Integer
    With cboTerm
        For i = 1 To 30 ' Add 30 years.
            .AddItem i ' 0
        Next i
    .ListIndex = 14
    End With
    
End Sub

Thanks in advance

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[AccessGuruCarl]

FYI....

The MSFlexGrid Amortization Schedule is working like a charm... All but one problem solved...

Printing the MSFlexGrid Results, and the info in the text and combo boxes.

I'll make another post regarding this in a few days if I can't solve it.

If anyone knows how to create a report from FORM results I'd love to hear from you.

Thanks in advance...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[SBerthold]

It probably would be easier to create an ADO recordset from scratch (no DB table or connection), adding the same fields as in the grid, adding the data, and then loading the grid from this.

Then you could just pass the recordset to a formated DataReport.

 

[Andrzejek]

I have this piece of code to print FlexGrid:

Printer.Orientation = vbPRORLandscape
[green]'Printer.PaperSize = vbPRPSLegal[/green]

Printer.PaintPicture MSHFlexGrid1.Picture, 0, 0, , , MSHFlexGrid1.Top, MSHFlexGrid1.Height

Printer.EndDoc

Also, all your variables in BLUE are Variants:

Private Sub Command1_Click()
Dim [blue]iRow, iMonth, iYear[/blue], iPayment As Integer
Dim [blue]iStartRow[/blue], iEndRow As Integer
Dim [blue]dTotInterest, dTotPrinciple[/blue], dTotPayments As Double
Dim [blue]dYrlyInterest, dYrlyPrinciple[/blue], dYryPayment As Double
Dim i As Integer

You may want to know about it.


Have fun.

---- Andy

 

[SBerthold]

>Printer.PaintPicture MSHFlexGrid1.Picture

I often got errors with that, or it didn't print all rows or columns, or some strange results. Sometimes fewer times it worked though.

Usually you need to carry the data around for some other purpose anyways, or search and filter the data, or whatever, therefore my suggestion to make it easier when doing so and use a recordset object.

 

[AccessGuruCarl]

Thanks for the posts..

I had trouble with the Printer.PaintPicture as well.
Getting a runtime 5 error.

Andrzejek - As long as none of them are string varibles, whats the difference? I was taught that variants should only be used when your not what the data result will be. Hence, they can hold a variable of most any data type, numeric, string, or even an entire array. Am I wrong?

SBerthold thanks, been working in db's for so long, I forgot about the ability to create a disconnected recordset. Pulled out the Access 2003 Programming and there it was.... Here's a Star....

I can't believe you can't make a report from form results in VB, what was MS thinking! But I'm new to VB and from what I've been reading the Data Reports have improved greatly from previous versions. Seems like a alot of extra coding just to print some results.

Thanks again...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[AccessGuruCarl]

SBerthold...

I created the recordset... QuickLoan_Recordset()
It stores everything nicely. Debug.Print...

How do I set the Datasource to it from the DataReport.
Keep getting error.. Invalid Data Source

Unfortunatly, Access 2003 doesn't show this.
They show how to save the recordset to disk, and retrieve it, but I tried that method as well and still got the same error.

Any Idea's or should I make a new post?

Thanks.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[JerryKlmns]

I double to declare these as integers
iRow, iMonth, iYear, iStartRow

And these as currency, since you do maths and need accuracy
dTotInterest, dTotPrinciple, dTotPayments, dYrlyInterest, dYrlyPrinciple, dYryPayment, dLoan

A Variant is a special data type that can contain any kind of data except fixed-length String data.

Except that it consumes extra storage size, suits in any case! So are you suggesting to get rid of all data type variables. I think that when you do maths on variants there is a behind the scenes evaluation of type and a convertion running.

 

[SBerthold]

>How do I set the Datasource to it from the DataReport.


What service pack of VB6 are you using? SP 6?

Using then an ADO recorset, this should work:

Set DataReport1.DataSource = RSADO