Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim lt As ListItem
Dim memdate As Date
Dim SelDate As Date
Dim LastPDate As Date
Dim Edit As Boolean
Dim SelRowInd As Integer
Dim OpenPrev As Boolean
Private Sub ChkPaid_Click()
If Edit = False Then
If ChkPaid.value = 1 Then
CmdSave.Enabled = True
ElseIf ChkPaid.value = 0 Then
CmdSave.Enabled = False
End If
End If
End Sub
Private Sub CmbMemNo_Click()
Lrno.Enabled = True
Lcus.Enabled = True
DtpPDate.value = Date
TxtAmt.Text = Clear
ListPrevPaymts.ListItems.Clear
ListPrevPaymts.Visible = False
TxtRemarks.Text = Clear
ChkPaid.value = 0
If CmbType.List(CmbMemNo.ListIndex) = "m" Then 'selected is a memo
Set rs = GetRecordset("SELECT gad.roomno, gad.cusid,memos.date,gad.name FROM gad INNER JOIN (occupied INNER JOIN memos ON occupied.ocpid = memos.id) ON gad.id = occupied.id WHERE (((memos.mno)='" & CmbMemNo.Text & "'))")
If Not (rs.EOF And rs.BOF) Then
Lblrno.Caption = rs.Fields(0)
memdate = rs.Fields(2)
' If rs.Fields(1) > "nill" Then
' Set rs1 = GetRecordset("select name from customers where id='" & rs.Fields(1) & "'")
' Lblcus.Caption = rs1.Fields(0)
' Else
' Lblcus.Caption = "-"
' End If
Lblcus.Caption = rs.Fields(3)
End If
Set rs = GetRecordset("select * from memos where mno='" & CmbMemNo.Text & "'")
Lst.Enabled = True
Lbal.Enabled = True
Ltotal.Enabled = True
Ldate.Enabled = True
LblDate.Caption = Format$(rs.Fields(1), "DD/MM/YYYY")
If Not (rs.EOF And rs.BOF) Then
If rs.Fields(4) = "n" Then
LblStatus.Caption = "Not paid"
ChkPaid.value = 0
ElseIf rs.Fields(4) = "p" Then
LblStatus.Caption = "Partially paid"
ChkPaid.value = 1
ElseIf rs.Fields(4) = "c" Then
LblStatus.Caption = "Completed"
ChkPaid.value = 1
End If
LblAmtPyb.Caption = Round(rs.Fields(3), 2)
End If
Else
Set rs = GetRecordset("SELECT gad.roomno, gad.cusid,bills.bdate,gad.name FROM gad INNER JOIN (occupied INNER JOIN bills ON occupied.ocpid = bills.id) ON gad.id = occupied.id WHERE (((bills.billno)='" & CmbMemNo.Text & "'))")
If Not (rs.EOF And rs.BOF) Then
Lblrno.Caption = rs.Fields(0)
memdate = rs.Fields(2)
' If rs.Fields(1) > "nill" Then
' Set rs1 = GetRecordset("select name from customers where id='" & rs.Fields(1) & "'")
' Lblcus.Caption = rs1.Fields(0)
' Else
' Lblcus.Caption = "-"
' End If
Lblcus.Caption = rs.Fields(3)
End If
Set rs = GetRecordset("select * from bills where billno='" & CmbMemNo.Text & "'")
Lst.Enabled = True
Lbal.Enabled = True
Ltotal.Enabled = True
Ldate.Enabled = True
LblDate.Caption = Format$(rs.Fields(4), "dd/MM/yyyy")
If Not (rs.EOF And rs.BOF) Then
If rs.Fields(2) = "n" Then
LblStatus.Caption = "Not paid"
ChkPaid.value = 0
ElseIf rs.Fields(2) = "p" Then
LblStatus.Caption = "Partially paid"
ChkPaid.value = 1
ElseIf rs.Fields(2) = "c" Then
LblStatus.Caption = "Completed"
ChkPaid.value = 1
End If
LblAmtPyb.Caption = Round(rs.Fields(5), 2)
End If
End If
Set rs = GetRecordset("select pamt,pdate,autoid from memo_pay where id='" & CmbMemNo.Text & "'")
If Not (rs.EOF And rs.BOF) Then
CmbAutoId.Clear
LblPpay.Enabled = True
ListPrevPaymts.Visible = True
ListPrevPaymts.ListItems.Clear
While Not rs.EOF
CmbAutoId.AddItem rs.Fields(2)
'LblBal.Caption = rs.Fields(0)
Set lt = ListPrevPaymts.ListItems.Add()
lt.Text = Format$(rs.Fields(1), "dd/MM/yyyy")
LastPDate = rs.Fields(1)
lt.SubItems(1) = rs.Fields(0)
rs.MoveNext
Wend
End If
Dim paidamt As Integer
paidamt = 0
Set rs = GetRecordset("select pamt from memo_pay where id='" & CmbMemNo.Text & "'")
If Not (rs.EOF And rs.BOF) Then
While Not rs.EOF
paidamt = paidamt + rs.Fields(0)
rs.MoveNext
Wend
LblBal.Caption = "" & (Val(LblAmtPyb.Caption) - paidamt)
Else
LblBal.Caption = "" & Round(Val(LblAmtPyb.Caption), 2)
End If
If ListPrevPaymts.Visible = True Then
ListPrevPaymts.TabIndex = 4
CmdSave.TabIndex = 5
Else
CmdSave.TabIndex = 4
End If
If Edit = True Then
DtpPDate.Enabled = False
TxtAmt.Enabled = False
CmdSave.Enabled = False
Else
DtpPDate.Enabled = True
TxtAmt.Enabled = True
End If
End Sub
Private Sub CmdClear_Click()
Edit = False
'OpenPrev = False
ChkPaid.value = 0
'CmbMemNo.ToolTipText = "Contains numbers of all those memos that have payment dues"
CmdEdit.Enabled = True
CmdSave.Enabled = False
CmdOpen.Enabled = True
DtpPDate.Enabled = True
TxtAmt.Enabled = True
DtpPDate.value = Date
TxtAmt.Text = Clear
TxtRemarks.Text = Clear
ListPrevPaymts.ListItems.Clear
ListPrevPaymts.Visible = False
CmbMemNo.Clear
LblPpay.Enabled = False
LblPpay.Caption = "Show previous payments"
LblBal.Caption = ""
LblAmtPyb.Caption = ""
LblStatus.Caption = ""
Lblrno.Caption = ""
Lblcus.Caption = ""
LblDate.Caption = ""
LblPDate.Enabled = True
LblAmt.Enabled = True
Ldate.Enabled = False
Lst.Enabled = False
Lbal.Enabled = False
Ltotal.Enabled = False
Lrno.Enabled = False
Lcus.Enabled = False
CmbType.Clear
Set rs = GetRecordset("select mno from memos where type='c'")
If Not (rs.EOF And rs.BOF) Then
While Not rs.EOF
CmbMemNo.AddItem rs.Fields(0) 'cmbmemno contains all credit memos
CmbType.AddItem "m"
rs.MoveNext
Wend
End If
Set rs = GetRecordset("select billno from bills where type='c'")
If Not (rs.EOF And rs.BOF) Then
While Not rs.EOF
CmbMemNo.AddItem rs.Fields(0) 'cmbmemno contains all credit bills
CmbType.AddItem "b"
rs.MoveNext
Wend
End If
End Sub
Private Sub CmdEdit_Click()
CmdClear_Click
Edit = True
CmdEdit.Enabled = False
CmdSave.Enabled = True
LblPDate.Enabled = False
LblAmt.Enabled = False
DtpPDate.Enabled = False
TxtAmt.Enabled = False
CmbMemNo.Clear
CmbType.Clear
Set rs = GetRecordset("select mno from memos where (pstatus='p' or pstatus='c') and type='c'")
If Not (rs.EOF And rs.BOF) Then
While Not rs.EOF
CmbMemNo.AddItem rs.Fields(0)
CmbType.AddItem "m"
rs.MoveNext
Wend
End If
Set rs = GetRecordset("select billno from bills where (pstatus='p' or pstatus='c') and type='c'")
If Not (rs.EOF And rs.BOF) Then
While Not rs.EOF
CmbMemNo.AddItem rs.Fields(0)
CmbType.AddItem "b"
rs.MoveNext
Wend
End If
CmbMemNo.SetFocus
'CmbMemNo.ToolTipText = "Contains numbers of all those memos and bills that are once processed"
End Sub
Private Sub CmdExit_Click()
Unload Me
End Sub
Private Sub CmdOpen_Click()
' OpenPrev = True
' CmbMemNo.Clear
' CmdOpen.Enabled = False
' CmdEdit.Enabled = False
' CmdSave.Enabled = False
' Set rs = GetRecordset("select mno from memos where pstatus='c' and type='c'")
' If Not (rs.EOF And rs.BOF) Then
' While Not rs.EOF
' CmbMemNo.AddItem rs.Fields(0)
' rs.MoveNext
' Wend
' End If
' Set rs = GetRecordset("select billno from bills where pstatus='c' and type='c'")
' If Not (rs.EOF And rs.BOF) Then
' While Not rs.EOF
' CmbMemNo.AddItem rs.Fields(0)
' rs.MoveNext
' Wend
' End If
' CmbMemNo.SetFocus
End Sub
Private Sub CmdSave_Click()
If Edit = False Then
If DtpPDate.value < memdate Or DtpPDate.value <= LastPDate Then
MsgBox "Invalid date", vbExclamation, "Date error!"
DtpPDate.SetFocus
Exit Sub
End If
Else
If DtpPDate.value < memdate Then
MsgBox "Invalid date", vbExclamation, "Date error!"
DtpPDate.SetFocus
Exit Sub
End If
End If
If CmbMemNo.ListIndex > -1 And TxtAmt.Text > "" Then
If Edit = True Then
If Val(TxtAmt.Text) = 0 Then 'Or Val(TxtAmt.Text) Then
MsgBox "Please check the amount", vbExclamation, "Guest Log"
TxtAmt.SetFocus
Exit Sub
End If
Set rs = GetRecordset("select * from memo_pay where autoid=" & CmbAutoId.List(SelRowInd - 1))
If ChkPaid.value = 1 Then
rs.Fields(0) = CmbMemNo.Text
rs.Fields(1) = Val(Trim(TxtAmt))
rs.Fields(2) = DtpPDate.value
If Trim(TxtRemarks) > "" Then rs.Fields(5) = Trim(TxtRemarks) Else rs.Fields(5) = "-"
rs.Update
ElseIf ChkPaid.value = 0 Then
rs.Delete
End If
Else
If (Val(Trim(TxtAmt)) > Val(LblBal.Caption)) Or Val(Trim(TxtAmt)) = 0 Then
MsgBox "Please check the amount", vbExclamation, "Guest Log"
TxtAmt.SetFocus
Exit Sub
End If
Set rs = GetRecordset("select * from memo_pay")
rs.AddNew
rs.Fields(0) = CmbMemNo.Text
rs.Fields(1) = Val(Trim(TxtAmt))
rs.Fields(2) = DtpPDate.value
If Trim(TxtRemarks) > "" Then rs.Fields(5) = Trim(TxtRemarks) Else rs.Fields(5) = "-"
rs.Update
End If
Dim balance As Long
balance = Val(LblAmtPyb.Caption)
Set rs = GetRecordset("select sum(pamt) from memo_pay where id='" & CmbMemNo.Text & "'")
If Not (rs.EOF And rs.BOF) Then balance = Val(LblAmtPyb.Caption) - rs.Fields(0)
If CmbType.List(CmbMemNo.ListIndex) = "m" Then 'to fill balamt field of memos table
Set rs1 = GetRecordset("select * from memos where mno='" & CmbMemNo.Text & "'")
If Not (rs1.EOF And rs1.BOF) Then
If balance = 0 Then
rs1.Fields(4) = "c"
ElseIf balance = Val(LblAmtPyb.Caption) Then
rs1.Fields(4) = "n"
Else
rs1.Fields(4) = "p"
End If
rs1.Fields(5) = balance
rs1.Update
End If
Else 'to fill balamt field of bills table
Set rs1 = GetRecordset("select * from bills where billno='" & CmbMemNo.Text & "'")
If Not (rs1.EOF And rs1.BOF) Then
If balance = 0 Then
rs1.Fields(2) = "c"
ElseIf balance = Val(LblAmtPyb.Caption) Then
rs1.Fields(2) = "n"
Else
rs1.Fields(2) = "p"
End If
rs1.Fields(10) = balance
rs1.Update
End If
End If
If Edit = True Then MsgBox "Saved the changes made", vbInformation, "Guest Log" Else MsgBox "Saved the new payment details", vbInformation, "Guest Log"
CmdClear_Click
CmbMemNo.SetFocus
Else
If CmbMemNo.ListIndex = -1 Then
MsgBox "Select a credit bill number", vbExclamation, "Guest Log"
CmbMemNo.SetFocus
ElseIf Trim(TxtAmt) = "" Then
MsgBox "Enter the amount", vbExclamation, "Guest Log"
TxtAmt.Text = Clear
TxtAmt.SetFocus
End If
End If
End Sub
Private Sub Form_Load()
Me.Top = 50
Me.Left = 50
Me.Icon = FrmMain.Icon
Call Color_Change(FrmPayments)
GetConnection
CmdClear_Click
End Sub
Private Sub Frame1_DragDrop(Source As Control, X As Single, Y As Single)
End Sub
Private Sub ListPrevPaymts_ItemClick(ByVal Item As MSComctlLib.ListItem)
SelDate = Format$(Item, "dd/MM/yyyy")
If Edit = True Then
If Item.Index = ListPrevPaymts.ListItems.count Then
LblAmt.Enabled = True
LblPDate.Enabled = True
DtpPDate.Enabled = True
TxtAmt.Enabled = True
CmdSave.Enabled = True
DtpPDate.value = Format$(Item, "dd/MM/yyyy")
TxtAmt.Text = ListPrevPaymts.ListItems.Item(Item.Index).ListSubItems.Item(1).Text
Set rs = GetRecordset("select remarks from memo_pay where autoid=" & CmbAutoId.List(Item.Index - 1))
If Not (rs.EOF And rs.BOF) Then TxtRemarks.Text = rs.Fields(0)
Else
DtpPDate.value = Date
TxtAmt.Text = Clear
LblPDate.Enabled = False
LblAmt.Enabled = False
DtpPDate.Enabled = False
TxtAmt.Enabled = False
CmdSave.Enabled = False
End If
' ElseIf OpenPrev = True Then
' DtpPDate.value = Format$(Item, "DD/MM/YYYY")
' TxtAmt.Text = ListPrevPaymts.ListItems.Item(Item.Index).ListSubItems.Item(1).Text
' Set rs = GetRecordset("select remarks from memo_pay where autoid=" & CmbAutoId.List(Item.Index - 1))
' If Not (rs.EOF And rs.BOF) Then TxtRemarks.Text = rs.Fields(0)
End If
SelRowInd = Item.Index
End Sub
Private Sub TxtAmt_KeyPress(KeyAscii As Integer)
KeyAscii = Number_Only(KeyAscii)
End Sub