My code...I am just pasting part of the code as it is too long
Private Sub Listupdatebutton_Click()
If fgrid.rows >= 4 Then
If MsgBox("Are you sure...You want to Update '" & Format(Combo1.Text, "MMMM, YYYY") & "'" & " Data", vbCritical + vbYesNo + vbDefaultButton2, "User Message") = vbYes Then
If dm.State = adStateOpen Then dm.Close
dm.Open "select * from Demandlist where dldate=cdate('" & Format(Combo1.Text, "dd-MM-yyyy") & "')", db, adOpenKeyset, adLockOptimistic, adCmdText
Call listupdate
MsgBox "Demand List has updated on " & Format(Date, "dd-mm-yyyy"), vbInformation, "User Message"
End If
Else
MsgBox "First select show data", vbInformation, "User Message"
End If
End Sub
Public Function listupdate()
Dim a As Integer, i As Integer, b As Integer, c As Double, d As Double
If dm.RecordCount >= 1 Then
If ledger.State = adStateOpen Then ledger.Close
ledger.Open "select * from Loanledger where 1<>1", db, adOpenKeyset, adLockOptimistic, adCmdText
dm.MoveFirst
While dm.EOF = False
cbrece.AddNew
cbrece.Fields("staffno") = Val(dm.Fields("staffno"))
cbrece.Fields("entrydate") = Format(Date, "dd-MM-yyyy")
If IsNull(dm.Fields("total")) = False Then cbrece.Fields("bankamt") = Val(dm.Fields("total"))
cbrece.Fields("bankname") = "XYZ, abc"
If IsNull(dm.Fields("shamt")) = False Then cbrece.Fields("shareamt") = Val(dm.Fields("shamt"))
If IsNull(dm.Fields("tdamt")) = False Then cbrece.Fields("tdamt") = Val(dm.Fields("tdamt"))
If IsNull(dm.Fields("ll")) = False Then cbrece.Fields("ll") = Val(dm.Fields("ll"))
If IsNull(dm.Fields("hl")) = False Then cbrece.Fields("hl") = Val(dm.Fields("hl"))
If IsNull(dm.Fields("el")) = False Then cbrece.Fields("edl") = Val(dm.Fields("el"))
If IsNull(dm.Fields("cdl")) = False Then cbrece.Fields("tl") = Val(dm.Fields("cdl"))
If IsNull(dm.Fields("totint")) = False Then cbrece.Fields("intonloan") = Val(dm.Fields("totint"))
If IsNull(dm.Fields("total")) = False Then cbrece.Fields("totamt") = Val(dm.Fields("total"))
If IsNull(dm.Fields("cf")) = False Then cbrece.Fields("cf") = Val(dm.Fields("cf"))
cbrece.Fields("trans_status") = "Demand List " & Format(Date, "dd-MM-yyyy")
cbrece.Update
If temp.State = adStateOpen Then temp.Close
temp.Open "select * from Loanledger where staffno=" & dm.Fields("staffno") & " order by transdate desc", db, adOpenKeyset, adLockOptimistic, adCmdText
If temp.RecordCount >= 1 Then
ledger.AddNew
ledger.Fields("staffno") = Val(dm.Fields("staffno"))
ledger.Fields("transdate") = Format(Date, "dd-MM-yyyy")
If IsNull(dm.Fields("shamt")) = False Then
ledger.Fields("share_subscri") = Val(dm.Fields("shamt"))
Else
ledger.Fields("share_subscri") = 0
End If
If IsNull(dm.Fields("cf")) = False Then
ledger.Fields("cf") = Val(dm.Fields("cf"))
Else
ledger.Fields("cf") = 0
End If
If IsNull(dm.Fields("tdamt")) = False Then
ledger.Fields("td_received") = Val(dm.Fields("tdamt"))
Else
ledger.Fields("td_received") = 0
End If
temp.MoveFirst
If IsNull(temp.Fields("share_prog_total")) = True Then temp.Fields("share_prog_total") = 0
ledger.Fields("share_prog_total") = temp.Fields("share_prog_total") + Val(ledger.Fields("share_subscri"))
If IsNull(temp.Fields("cf_prog_total")) = True Then temp.Fields("cf_prog_total") = 0
ledger.Fields("cf_prog_total") = temp.Fields("cf_prog_total") + Val(ledger.Fields("cf_prog_total"))
If IsNull(temp.Fields("td_prog_total")) = True Then temp.Fields("td_prog_total") = 0
ledger.Fields("td_prog_total") = temp.Fields("td_prog_total") + Val(ledger.Fields("td_received"))
If IsNull(dm.Fields("ll")) = False Then ledger.Fields("ll_received") = dm.Fields("ll")
If IsNull(dm.Fields("hl")) = False Then ledger.Fields("hl_received") = dm.Fields("hl")
If IsNull(dm.Fields("el")) = False Then ledger.Fields("edl_received") = dm.Fields("el")
If IsNull(dm.Fields("ll")) = True Then dm.Fields("ll") = 0
If IsNull(temp.Fields("ll_outstand")) = False Then
ledger.Fields("ll_outstand") = Val(temp.Fields("ll_outstand")) - dm.Fields("ll")
Else
ledger.Fields("ll_outstand") = dm.Fields("ll")
End If
If IsNull(dm.Fields("hl")) = True Then dm.Fields("hl") = 0
If IsNull(temp.Fields("hl_outstand")) = False Then
ledger.Fields("hl_outstand") = Val(temp.Fields("hl_outstand")) - dm.Fields("hl")
Else
ledger.Fields("hl_outstand") = dm.Fields("hl")
End If
If IsNull(dm.Fields("el")) = True Then dm.Fields("el") = 0
If IsNull(temp.Fields("edl_outstand")) = False Then
ledger.Fields("edl_outstand") = Val(temp.Fields("edl_outstand")) - dm.Fields("el")
Else
ledger.Fields("edl_outstand") = dm.Fields("el")
End If
ledger.Fields("total_loan_out") = Val(ledger.Fields("ll_outstand")) + Val(ledger.Fields("hl_outstand")) + Val(ledger.Fields("edl_outstand"))
ledger.Fields("int_charged") = Round((Val(ledger.Fields("total_loan_out")) * Val(other.Fields("roi"))) / 1200, 0)
If IsNull(temp.Fields("int_charged")) = False Then ledger.Fields("int_received") = Val(temp.Fields("int_charged"))
ledger.Fields("int_bal_out") = ledger.Fields("int_charged")
ledger.Fields("trans_status") = "Demand List " & Format(Date, "dd-MM-yyyy")
ledger.Update
End If
dm.MoveNext
Wend