' KEEP CKECK FOR LEAP YEAR IN PROGRAM
' accept qty for only equal to no.of. days in the month
'1. Form_Load
'2. fillMSF
'3. msf_click
'4. sub cmdSave_Click
'5. fillValues
'6. cmdExit_Click
'7
' 30/09/2008 added field remark before balamt field
Option Explicit
Dim rssal As ADODB.Recordset
Dim rssal2 As ADODB.Recordset
Dim rscustomer As ADODB.Recordset
Dim rsyears As ADODB.Recordset
Dim rsmonthse As ADODB.Recordset
Dim rscitye As ADODB.Recordset
Dim mstatus As String, sv As String
Dim mSql As String, customer_name As String
Dim i As Integer
Dim date1 As Date
Dim footer_items As Integer
Dim w, sno As Integer
Dim tq As Double
Dim flag As Integer
Dim leapyear_flag As Boolean
Dim answer As String

Private Sub cmdExit_Click()
Unload Me
End Sub

Private Sub combo4_Click()
If rsyears.State = 1 Then rsyears.Close
   rsyears.Open "select * from years where year ='" & Combo4.Text & "'", cn, adOpenDynamic, adLockOptimistic, adCmdText
    Combo4.Text = rsyears.Fields("year")
End Sub
Private Sub Combo2_Click()
If rs.State = 1 Then rs.Close
   rs.Open "select * from Monthse where month ='" & Combo2.Text & "'", cn, adOpenDynamic, adLockOptimistic, adCmdText
   Combo2.Text = rs.Fields("month")
End Sub
Private Sub Combo3_Click()
If rs.State = 1 Then rs.Close
   rs.Open "select * from citye where city ='" & Combo3.Text & "'", cn, adOpenDynamic, adLockOptimistic, adCmdText
   Combo3.Text = rs.Fields("city")
End Sub

Private Sub Combo3_LostFocus()
Call clearvalues
Call fillMSF
End Sub

Private Sub Form_Load()

Set rssal = New ADODB.Recordset
Set rssal2 = New ADODB.Recordset
Set rsyears = New ADODB.Recordset
Set rsmonthse = New ADODB.Recordset
Set rscitye = New ADODB.Recordset
Set rscustomer = New ADODB.Recordset
MSF.Row = 1
MSF.Col = 1
SetTextbox
'following is logic to read year data in combo4
If rsyears.State = 1 Then rsyears.Close
rsyears.Open "select * from years", cn, adOpenDynamic, adLockOptimistic, adCmdText
Combo4.Clear
flag = 0
While Not rsyears.EOF
    Combo4.AddItem rsyears.Fields("year")
    If flag = 0 Then
        Combo4.Text = rsyears.Fields("year")
        flag = 1
    End If
    rsyears.MoveNext
Wend


'following is logic to read Months data in combo2
If rsmonthse.State = 1 Then rsmonthse.Close
rsmonthse.Open "select * from Monthse", cn, adOpenDynamic, adLockOptimistic, adCmdText
Combo2.Clear
flag = 0
While Not rsmonthse.EOF
    Combo2.AddItem rsmonthse.Fields("month")
    If flag = 0 Then
        Combo2.Text = rsmonthse.Fields("month")
        flag = 1
    End If
    rsmonthse.MoveNext
Wend

'following is logic to read cities data in combo3
If rscitye.State = 1 Then rscitye.Close
rscitye.Open "select * from citye", cn, adOpenDynamic, adLockOptimistic, adCmdText
Combo3.Clear
flag = 0
While Not rscitye.EOF
    Combo3.AddItem rscitye.Fields("city")
    If flag = 0 Then
        Combo3.Text = rscitye.Fields("city")
        flag = 1
    End If
    rscitye.MoveNext
Wend
' logic to check whether year is a leap year or not
If (Val(Combo4.Text) Mod 4) = 0 Then
    leapyear_flag = True
Else
    leapyear_flag = False
End If
End Sub
Private Sub clearvalues()
'Text1.Text = " " ' quantity variable
'Text2.Text = Date
totqty.Text = " "
Text3.Text = 0
End Sub
Private Sub fillMSF() ' 2
'If rssal.State = 1 Then rssal.Close
'rssal.Open "sale", cn, adOpenKeyset, adLockOptimistic
'If Not rssal.BOF Then
'    rssal.MoveFirst
'    rssal.MoveLast
    ' made remark on 24/09/2008 following two lines
    'MSF.Rows = 2
    'MSF.Cols = 39
    ' fields year, month and city are to be stored in database directly
    MSF.TextMatrix(0, 0) = "srno"
    'MSF.TextMatrix(0, 1) = "year" ' 2008
    'MSF.TextMatrix(0, 2) = "month"
    'MSF.TextMatrix(0, 3) = "city
    MSF.TextMatrix(0, 1) = "name"
    'MSF.TextMatrix(0, 2) = "time"
    MSF.TextMatrix(0, 3) = "1"
    MSF.TextMatrix(0, 4) = "2"
    MSF.TextMatrix(0, 5) = "3"
    MSF.TextMatrix(0, 6) = "4"
    MSF.TextMatrix(0, 7) = "5"
    MSF.TextMatrix(0, 8) = "6"
    MSF.TextMatrix(0, 9) = "7"
    MSF.TextMatrix(0, 10) = "8"
    MSF.TextMatrix(0, 11) = "9"
    MSF.TextMatrix(0, 12) = "10"
    MSF.TextMatrix(0, 13) = "11"
    MSF.TextMatrix(0, 14) = "12"
    MSF.TextMatrix(0, 15) = "13"
    MSF.TextMatrix(0, 16) = "14"
    MSF.TextMatrix(0, 17) = "15"
    MSF.TextMatrix(0, 18) = "16"
    MSF.TextMatrix(0, 19) = "17"
    MSF.TextMatrix(0, 20) = "18"
    MSF.TextMatrix(0, 21) = "19"
    MSF.TextMatrix(0, 22) = "20"
    MSF.TextMatrix(0, 23) = "21"
    MSF.TextMatrix(0, 24) = "22"
    MSF.TextMatrix(0, 25) = "23"
    MSF.TextMatrix(0, 26) = "24"
    MSF.TextMatrix(0, 27) = "25"
    MSF.TextMatrix(0, 28) = "26"
    MSF.TextMatrix(0, 29) = "27"
    MSF.TextMatrix(0, 30) = "28"
    ' allow for February only in year 2008 i.e. leap year
    If (leapyear_flag = True And Combo2.Text = "February") Then
        MSF.TextMatrix(0, 31) = "29"
    Else
        MSF.TextMatrix(0, 31) = "29"
    End If
    If (Combo2.Text <> "February") Then
        MSF.TextMatrix(0, 32) = "30"
    End If
    If (Combo2.Text = "January" Or Combo2.Text = "March" Or Combo2.Text = "May" Or Combo2.Text = "July" Or Combo2.Text = "August" Or Combo2.Text = "October" Or Combo2.Text = "December") Then
        MSF.TextMatrix(0, 33) = "31"
    End If
    MSF.TextMatrix(0, 34) = "tot.qty"
    MSF.TextMatrix(0, 35) = "Rate/Ltr."
    MSF.TextMatrix(0, 36) = "Tot.Amt"
    MSF.TextMatrix(0, 37) = "Oth.Amt"
    MSF.TextMatrix(0, 38) = "Prv.Bal."
    MSF.TextMatrix(0, 39) = "Tot.Bal."
    MSF.TextMatrix(0, 40) = "Amt.Recd."
    MSF.TextMatrix(0, 41) = "Rec.Date"
    MSF.TextMatrix(0, 42) = "Remark"
    MSF.TextMatrix(0, 43) = "Bal.Amt."
    ' MSF size
    MSF.ColWidth(0) = 600 ' srno
    'MSF.ColWidth(1) = 550   'year
    'MSF.ColWidth(2) = 500   ' month
    'MSF.ColWidth(3) = 1000   'city
    MSF.ColWidth(1) = 2000   'party name
    'MSF.ColWidth(2) = 600   'timeflag
    MSF.ColWidth(3) = 600   'Q1
    MSF.ColWidth(4) = 600   'Q2
    MSF.ColWidth(5) = 600   'Q3
    MSF.ColWidth(6) = 600  'Q4
    MSF.ColWidth(7) = 600  'Q5
    MSF.ColWidth(8) = 600  'Q6
    MSF.ColWidth(9) = 600  'Q7
    MSF.ColWidth(10) = 600  'Q8
    MSF.ColWidth(11) = 600  'Q9
    MSF.ColWidth(12) = 600  'Q10
    MSF.ColWidth(13) = 600  'Q11
    MSF.ColWidth(14) = 600  'Q12
    MSF.ColWidth(15) = 600  'Q13
    MSF.ColWidth(16) = 600  'Q14
    MSF.ColWidth(17) = 600  'Q15
    MSF.ColWidth(18) = 600  'Q26
    MSF.ColWidth(19) = 600  'Q17
    MSF.ColWidth(20) = 600  'Q18
    MSF.ColWidth(21) = 600  'Q19
    MSF.ColWidth(22) = 600  'Q20
    MSF.ColWidth(23) = 600  'Q21
    MSF.ColWidth(24) = 600  'Q22
    MSF.ColWidth(25) = 600  'Q23
    MSF.ColWidth(26) = 600  'Q24
    MSF.ColWidth(27) = 600  'Q25
    MSF.ColWidth(28) = 600  'Q26
    MSF.ColWidth(29) = 600  'Q27
    MSF.ColWidth(30) = 600  'q28
    ' allow for February only in year 2008 i.e. leap year
    If (Combo4.Text = "2008" And Combo2.Text = "February") Then
        MSF.ColWidth(31) = 600  'q29
    Else
        MSF.ColWidth(31) = 600  'q29
    End If
    If (Combo2.Text <> "February") Then
        MSF.ColWidth(32) = 600  'q30
    End If
    If (Combo2.Text = "January" Or Combo2.Text = "March" Or Combo2.Text = "May" Or Combo2.Text = "July" Or Combo2.Text = "August" Or Combo2.Text = "October" Or Combo2.Text = "December") Then
        MSF.ColWidth(33) = 600  'q31
    End If
    MSF.ColWidth(34) = 1000 'totqty
    MSF.ColWidth(35) = 1000 'rate
    MSF.ColWidth(36) = 1200 'totamt
    MSF.ColWidth(37) = 1200 'othamt
    MSF.ColWidth(38) = 1200 'prevbal
    MSF.ColWidth(39) = 1200 'totbal
    MSF.ColWidth(40) = 1200 'recamt
    MSF.ColWidth(41) = 1200 'recdate
    MSF.ColWidth(42) = 1200 'Remark
    MSF.ColWidth(43) = 1200 'balamt
    If rs.State = 1 Then rs.Close
    rs.Open "select * from sale where year = '" & Combo4.Text & "' and month = '" & Combo2.Text & "' and city = '" & Combo3.Text & "'", cn, adOpenDynamic, adLockOptimistic, adCmdText
    'rs.Open "select * from sale where year = '" & Val(Combo4.Text) & "'", cn, adOpenDynamic, adLockOptimistic, adCmdText
    'rs.Open "select * from sale where year = '" & Val(Combo4.Text) & "' and month = '" & Combo2.Text & "'", cn, adOpenDynamic, adLockOptimistic, adCmdText
    'rs.Open "select * from sale where month = '" & Combo2.Text & "' and city = '" & Combo3.Text & "'", cn, adOpenDynamic, adLockOptimistic, adCmdText
    If rs.EOF = True Then
        MsgBox "Records Not Found, So appending names now"
        answer = MsgBox("Continue (Y/N)", vbYesNo, "Month & year correctly selected")
        If answer = vbYes Then
            ' Append names to temp table
            If rssal.State = 1 Then rssal.Close
            rssal2.Open "sale2", cn, adOpenKeyset, adLockOptimistic
            w = 0
            sno = 1
            If rscustomer.State = 1 Then rscustomer.Close
            rscustomer.Open "select * from customer where city = '" & Combo3.Text & "' order by srno", cn, adOpenDynamic, adLockOptimistic, adCmdText
            If Not rscustomer.BOF Then
                ' customer records for morning milk supply
                rscustomer.MoveFirst
                While Not rscustomer.EOF
                    If rscustomer!acclosed = "N" Then
                        rssal2.AddNew
                        rssal2!srno = sno
                        rssal2!acod = rscustomer!acod
                        rssal2!Year = Combo4.Text
                        rssal2!Month = Combo2.Text
                        'rssal2!timeflag = "M"
                        rssal2!Name = rscustomer!Name
                        rssal2!City = rscustomer!City
                        rssal2!Rate = rscustomer!mrate
                        rssal2!prevbal = rscustomer!cbc8
                        rssal2!recdate = Date
                        rssal2.Update
                    End If
                    rscustomer.MoveNext
                    w = w + 1
                    sno = sno + 1
                Wend
                MsgBox (Str(w) + " names appended")
             End If
             Call append_dta
             Call display_grid
         End If
        '
    Else
       Call display_grid
    End If
'Else
'    MsgBox ("No records in sale file")
'End If
End Sub
Private Sub append_dta()
' This procedure appends data from sale2 tabel to sale table
If rssal.State = 1 Then rssal.Close
rssal.Open "select * from sale", cn, adOpenKeyset, adLockOptimistic
If rssal2.State = 1 Then rssal2.Close
rssal2.Open "sale2", cn, adOpenKeyset, adLockOptimistic
rssal2.MoveFirst
While Not rssal2.EOF
    rssal.AddNew
    rssal!srno = rssal2!srno
    rssal!acod = rssal2!acod
    rssal!Year = rssal2!Year
    rssal!Month = rssal2!Month
    'rssal!timeflag = rssal2!timeflag
    rssal!Name = rssal2!Name
    rssal!City = rssal2!City
    ' qty fields not required as they are zero
    rssal!Rate = rssal2!Rate
    rssal!prevbal = rssal2!prevbal
    rssal!totamt = 0
    
    rssal!recdate = rssal2!recdate
    rssal.Update
    rssal2.MoveNext
Wend
' delete temp records from sale2
If rssal2.State = 1 Then rssal2.Close
rssal2.Open "delete * from sale2", cn, adOpenKeyset, adLockOptimistic
End Sub
Private Sub display_grid()
If rssal.State = 1 Then rssal.Close
rssal.Open "sale", cn, adOpenKeyset, adLockOptimistic

If rssal.State = 1 Then rssal.Close
rssal.Open "select * from sale where (year = '" & Combo4.Text & "' and month = '" & Combo2.Text & "' and city = '" & Combo3.Text & "') order by srno", cn, adOpenDynamic, adLockOptimistic, adCmdText

'rssal.Open "select * from sale order by srno", cn, adOpenKeyset, adLockOptimistic
'select id,isnull(recdate," "),isnull(amount,0),isnull(name," "),isnull(city,"city not found")
'Set MSF.DataSource = rssal


If Not rssal.BOF Then
    rssal.MoveFirst
End If
MSF.Rows = 2
While Not rssal.EOF
    MSF.TextMatrix(MSF.Rows - 1, 0) = rssal.Fields("srno")
    MSF.TextMatrix(MSF.Rows - 1, 1) = rssal.Fields("name")
    'MSF.TextMatrix(MSF.Rows - 1, 2) = rssal.Fields("timeflag")
    MSF.TextMatrix(MSF.Rows - 1, 3) = IIf(rssal.Fields("q1") = 0, " ", rssal.Fields("q1"))
    MSF.TextMatrix(MSF.Rows - 1, 4) = IIf(rssal.Fields("q2") = 0, " ", rssal.Fields("q2"))
    MSF.TextMatrix(MSF.Rows - 1, 5) = IIf(rssal.Fields("q3") = 0, " ", rssal.Fields("q3"))
    MSF.TextMatrix(MSF.Rows - 1, 6) = IIf(rssal.Fields("q4") = 0, " ", rssal.Fields("q4"))
    MSF.TextMatrix(MSF.Rows - 1, 7) = IIf(rssal.Fields("q5") = 0, " ", rssal.Fields("q5"))
    MSF.TextMatrix(MSF.Rows - 1, 8) = IIf(rssal.Fields("q6") = 0, " ", rssal.Fields("q6"))
    MSF.TextMatrix(MSF.Rows - 1, 9) = IIf(rssal.Fields("q7") = 0, " ", rssal.Fields("q7"))
    MSF.TextMatrix(MSF.Rows - 1, 10) = IIf(rssal.Fields("q8") = 0, " ", rssal.Fields("q8"))
    MSF.TextMatrix(MSF.Rows - 1, 11) = IIf(rssal.Fields("q9") = 0, " ", rssal.Fields("q9"))
    MSF.TextMatrix(MSF.Rows - 1, 12) = IIf(rssal.Fields("q10") = 0, " ", rssal.Fields("q10"))
    MSF.TextMatrix(MSF.Rows - 1, 13) = IIf(rssal.Fields("q11") = 0, " ", rssal.Fields("q11"))
    MSF.TextMatrix(MSF.Rows - 1, 14) = IIf(rssal.Fields("q12") = 0, " ", rssal.Fields("q12"))
    MSF.TextMatrix(MSF.Rows - 1, 15) = IIf(rssal.Fields("q13") = 0, " ", rssal.Fields("q13"))
    MSF.TextMatrix(MSF.Rows - 1, 16) = IIf(rssal.Fields("q14") = 0, " ", rssal.Fields("q14"))
    MSF.TextMatrix(MSF.Rows - 1, 17) = IIf(rssal.Fields("q15") = 0, " ", rssal.Fields("q15"))
    MSF.TextMatrix(MSF.Rows - 1, 18) = IIf(rssal.Fields("q16") = 0, " ", rssal.Fields("q16"))
    MSF.TextMatrix(MSF.Rows - 1, 19) = IIf(rssal.Fields("q17") = 0, " ", rssal.Fields("q17"))
    MSF.TextMatrix(MSF.Rows - 1, 20) = IIf(rssal.Fields("q18") = 0, " ", rssal.Fields("q18"))
    MSF.TextMatrix(MSF.Rows - 1, 21) = IIf(rssal.Fields("q19") = 0, " ", rssal.Fields("q19"))
    MSF.TextMatrix(MSF.Rows - 1, 22) = IIf(rssal.Fields("q20") = 0, " ", rssal.Fields("q20"))
    MSF.TextMatrix(MSF.Rows - 1, 23) = IIf(rssal.Fields("q21") = 0, " ", rssal.Fields("q21"))
    MSF.TextMatrix(MSF.Rows - 1, 24) = IIf(rssal.Fields("q22") = 0, " ", rssal.Fields("q22"))
    MSF.TextMatrix(MSF.Rows - 1, 25) = IIf(rssal.Fields("q23") = 0, " ", rssal.Fields("q23"))
    MSF.TextMatrix(MSF.Rows - 1, 26) = IIf(rssal.Fields("q24") = 0, " ", rssal.Fields("q24"))
    MSF.TextMatrix(MSF.Rows - 1, 27) = IIf(rssal.Fields("q25") = 0, " ", rssal.Fields("q25"))
    MSF.TextMatrix(MSF.Rows - 1, 28) = IIf(rssal.Fields("q26") = 0, " ", rssal.Fields("q26"))
    MSF.TextMatrix(MSF.Rows - 1, 29) = IIf(rssal.Fields("q27") = 0, " ", rssal.Fields("q27"))
    MSF.TextMatrix(MSF.Rows - 1, 30) = IIf(rssal.Fields("q28") = 0, " ", rssal.Fields("q28"))
    ' allow for February only in year 2008 i.e. leap year
    If (Combo4.Text = "2008" And Combo2.Text = "February") Then
        MSF.TextMatrix(MSF.Rows - 1, 31) = IIf(rssal.Fields("q29") = 0, " ", rssal.Fields("q29"))
    Else
        MSF.TextMatrix(MSF.Rows - 1, 31) = IIf(rssal.Fields("q29") = 0, " ", rssal.Fields("q29"))
    End If
    If (Combo2.Text <> "February") Then
         MSF.TextMatrix(MSF.Rows - 1, 32) = IIf(rssal.Fields("q30") = 0, " ", rssal.Fields("q30"))
    End If
    If (Combo2.Text = "January" Or Combo2.Text = "March" Or Combo2.Text = "May" Or Combo2.Text = "July" Or Combo2.Text = "August" Or Combo2.Text = "October" Or Combo2.Text = "December") Then
         MSF.TextMatrix(MSF.Rows - 1, 33) = IIf(rssal.Fields("q31") = 0, " ", rssal.Fields("q31"))
    End If
    MSF.TextMatrix(MSF.Rows - 1, 34) = IIf(rssal.Fields("totqty") = 0, " ", rssal.Fields("totqty"))
    MSF.TextMatrix(MSF.Rows - 1, 35) = IIf(rssal.Fields("rate") = 0, " ", rssal.Fields("rate"))
    MSF.TextMatrix(MSF.Rows - 1, 36) = IIf(rssal.Fields("totamt") = 0, " ", rssal.Fields("totamt"))
    MSF.TextMatrix(MSF.Rows - 1, 37) = IIf(rssal.Fields("othamt") = 0, " ", rssal.Fields("othamt"))
    MSF.TextMatrix(MSF.Rows - 1, 38) = IIf(rssal.Fields("prevbal") = 0, " ", rssal.Fields("prevbal"))
    MSF.TextMatrix(MSF.Rows - 1, 39) = IIf(rssal.Fields("totbal") = 0, " ", rssal.Fields("totbal"))
    MSF.TextMatrix(MSF.Rows - 1, 40) = IIf(rssal.Fields("recamt") = 0, " ", rssal.Fields("recamt"))
    If IsNull(rssal.Fields("recdate")) = True Then
        MSF.TextMatrix(MSF.Rows - 1, 41) = " "
    Else
        MSF.TextMatrix(MSF.Rows - 1, 41) = rssal.Fields("recdate")
    End If
    If IsNull(rssal.Fields("remark")) = True Then
        MSF.TextMatrix(MSF.Rows - 1, 42) = " "
    Else
        MSF.TextMatrix(MSF.Rows - 1, 42) = rssal.Fields("remark")
    End If
    MSF.TextMatrix(MSF.Rows - 1, 43) = IIf(rssal.Fields("balamt") = 0, " ", rssal.Fields("balamt"))
    Text3.Text = Text3.Text + rssal!balamt
    rssal.MoveNext
    MSF.Rows = MSF.Rows + 1
Wend
MSF.Rows = MSF.Rows - 1

End Sub


Private Sub MSF_EnterCell()
' Make sure the user doesn't attempt to edit the fixed cells
    If MSF.MouseRow = 0 Or MSF.MouseCol = 0 Then
        Text1.Visible = False
        Exit Sub
    End If
    ' following lines are added to prevent editing of columns 34,35,36,37,38,39 & 43
    If (MSF.MouseCol >= 34 And MSF.MouseCol <= 39) Or MSF.MouseCol = 43 Then
        Exit Sub
    End If

    
' clear contents of current cell
'    Text1.Text = ""
' place Textbox over current cell
    Text1.Visible = False
    Text1.Top = MSF.Top + MSF.CellTop
    Text1.Left = MSF.Left + MSF.CellLeft
    Text1.Width = MSF.CellWidth
    Text1.Height = MSF.CellHeight
' assing cell's contents to Textbox
    Text1.Text = MSF.Text
    Prev_qty.Text = MSF.Text
    totqty.Text = MSF.TextMatrix(MSF.Row, 34)
    srno.Text = MSF.TextMatrix(MSF.Row, 0)
' move focus to Textbox
    Text1.Visible = True
    Text1.SetFocus
End Sub

Private Sub MSF_LeaveCell()
    MSF.Text = Text1.Text
End Sub

'Private Sub Text1_KeyPress(KeyAscii As Integer)
'    If KeyAscii = 13 Then
'        If MSF.Row = MSF.Rows - 1 Then
'            If MSF.Col = MSF.Cols - 1 Then
'                Exit Sub
'            Else
'                MSF.Col = MSF.Col + 1
'            End If
'            MSF.Row = 1
'        Else
'            MSF.Row = MSF.Row + 1
'        End If
'    End If
'End Sub

Sub SetTextbox()
    Text1.Visible = False
    Text1.Top = MSF.Top + MSF.CellTop
    Text1.Left = MSF.Left + MSF.CellLeft
    Text1.Height = MSF.CellHeight
    Text1.Width = MSF.CellWidth
    Text1.Text = MSF.Text
    Text1.Visible = True
End Sub

Private Sub Text1_KeyPress(KeyAscii As Integer)
Dim SRow, SCol As Integer

If KeyAscii = 13 Then
    MSF.Text = Text1.Text
    ' added by dharam
    ' Dim i, j As Integer
    ' i = MSF.RowSel
    ' j = MSF.ColSel
    ' MsgBox i & vbCrLf & j, vbCritical, "test"
    
    
    ' formula is total= total - oldqty + newqty
    ' old method of totaling qty was as follows --
    '
    MSF.TextMatrix(MSF.Row, 34) = Val(totqty.Text) - Val(Prev_qty.Text) + Val(Text1.Text)
    totqty.Text = Val(totqty.Text) - Val(Prev_qty.Text) + Val(Text1.Text)
    
    ' following is logic to move qty of the date to text box
    
    rssal.MoveFirst
    If MSF.Rows > 0 Then
        If rssal.State = 1 Then rssal.Close
        rssal.Open "select * from sale where (year = '" & Combo4.Text & "' and month = '" & Combo2.Text & "' and city = '" & Combo3.Text & "')", cn, adOpenDynamic, adLockOptimistic, adCmdText
        rssal.Find "srno = " & srno.Text
        If rssal.EOF Then
            MsgBox ("Record not found")
        Else
        
    '        Call fillValues
            
            'MsgBox (Day(Text2.Text))
            Select Case (MSF.Col)
            Case 3
                rssal!q1 = Val(Text1.Text)
            Case 4
                rssal!q2 = Val(Text1.Text)
            Case 5
                rssal!q3 = Val(Text1.Text)
            Case 6
                rssal!q4 = Val(Text1.Text)
            Case 7
                rssal!q5 = Val(Text1.Text)
            Case 8
                rssal!q6 = Val(Text1.Text)
            Case 9
                rssal!q7 = Val(Text1.Text)
            Case 10
                rssal!q8 = Val(Text1.Text)
            Case 11
                rssal!q9 = Val(Text1.Text)
            Case 12
                rssal!q10 = Val(Text1.Text)
            Case 13
                rssal!q11 = Val(Text1.Text)
            Case 14
                rssal!q12 = Val(Text1.Text)
            Case 15
                rssal!q13 = Val(Text1.Text)
            Case 16
                rssal!q14 = Val(Text1.Text)
            Case 17
                rssal!q15 = Val(Text1.Text)
            Case 18
                rssal!q16 = Val(Text1.Text)
            Case 19
                rssal!q17 = Val(Text1.Text)
            Case 20
                rssal!q18 = Val(Text1.Text)
            Case 21
                rssal!q19 = Val(Text1.Text)
            Case 22
                rssal!q20 = Val(Text1.Text)
            Case 23
                rssal!q21 = Val(Text1.Text)
            Case 24
                rssal!q22 = Val(Text1.Text)
            Case 25
                rssal!q23 = Val(Text1.Text)
            Case 26
                rssal!q24 = Val(Text1.Text)
            Case 27
                rssal!q25 = Val(Text1.Text)
            Case 28
                rssal!q26 = Val(Text1.Text)
            Case 29
                rssal!q27 = Val(Text1.Text)
            Case 30
                rssal!q28 = Val(Text1.Text)
            Case 31
                rssal!q29 = Val(Text1.Text)
            Case 32
                rssal!q30 = Val(Text1.Text)
            Case 33
                rssal!q31 = Val(Text1.Text)
            Case 34
                ' this is tot qty field not editable
                '
            Case 35
                ' this is rate brought from customer master and is not editable
                ' make changes in customer master before bringing data in sale table
                '
            Case 36
                ' this is a calculated field of totamt and is not editable
                '
            Case 37
                ' this field is to be brought from other sale data
                ' this must be entered before entering milk sale data
                '
            Case 38
                ' this is previous balance field to be brought from customer opening balance of customer
                rssal!q31 = Val(Text1.Text)
            Case 39
                ' total balance is also a calculated field
                '
            Case 40
                ' received amount from customer this month
                rssal!recamt = Val(Text1.Text)
            Case 41
                ' receipt date a simple text field is used at present
                If Text1.Text <> "" Then
                    rssal!recdate = CDate(Text1.Text)
                Else
                    rssal!recdate = Date
                End If
            Case 42
                rssal!remark = Text1.Text
            Case 43
                ' net balance, a calculated field
            End Select
            ' new method of totaling is as follows ---
            tq = rssal!q1 + rssal!q2 + rssal!q3 + rssal!q4 + rssal!q5 + rssal!q6 + rssal!q7 + rssal!q8 + rssal!q9
            tq = tq + rssal!q10 + rssal!q11 + rssal!q12 + rssal!q13 + rssal!q14 + rssal!q15 + rssal!q16 + rssal!q17 + rssal!q18 + rssal!q19
            tq = tq + rssal!q20 + rssal!q21 + rssal!q22 + rssal!q23 + rssal!q24 + rssal!q25 + rssal!q26 + rssal!q27 + rssal!q28
            '
            ' allow for February only in year 2008 i.e. leap year
            If (leapyear_flag = True And Combo2.Text = "February") Then
                tq = tq + rssal!q29
            Else
                tq = tq + rssal!q29
            End If
            If (Combo2.Text <> "February") Then
                tq = tq + rssal!q30
            End If
            If (Combo2.Text = "January" Or Combo2.Text = "March" Or Combo2.Text = "May" Or Combo2.Text = "July" Or Combo2.Text = "August" Or Combo2.Text = "October" Or Combo2.Text = "December") Then
                tq = tq + rssal!q31
            End If
            '
            MSF.TextMatrix(MSF.Row, 34) = tq
            totqty.Text = tq
            rssal!totqty = totqty.Text
            rssal!totamt = totqty.Text * rssal!Rate
            
            ' Total balance below
            rssal!totbal = rssal!totamt + rssal!othamt + rssal!prevbal
            
            ' Net Balance Amount below rounded to nearest rupees
            If ((rssal!totbal - rssal!recamt) - Int(rssal!totbal - rssal!recamt)) > 0.49 Then
                rssal!balamt = Int(rssal!totbal - rssal!recamt) + 1
            Else
                rssal!balamt = Round((rssal!totbal - rssal!recamt), 0)
            End If
            
            MSF.TextMatrix(MSF.Row, 36) = rssal!totamt
            MSF.TextMatrix(MSF.Row, 37) = rssal!othamt
            MSF.TextMatrix(MSF.Row, 38) = rssal!prevbal
            MSF.TextMatrix(MSF.Row, 39) = rssal!totbal
            MSF.TextMatrix(MSF.Row, 40) = rssal!recamt
            MSF.TextMatrix(MSF.Row, 41) = rssal!recdate
            'MSF.TextMatrix(MSF.row, 42) = rssal!remark
            MSF.TextMatrix(MSF.Row, 43) = rssal!balamt
            Text3.Text = rssal!balamt
            rssal.Update
        End If
    End If
    '
    SRow = MSF.Row + 1
    SCol = MSF.ColSel
    If SRow = MSF.Rows Then
        SRow = MSF.FixedCols
        If SCol < MSF.Cols - MSF.FixedCols Then SCol = SCol + 1
    End If
    
    MSF.Row = SRow
    MSF.Col = SCol
    MSF.RowSel = SRow
    MSF.ColSel = SCol
    Text1.Text = MSF.Text
    Text1.SetFocus
    KeyAscii = 0
End If
End Sub