Option Compare Database
Option Explicit
Private formHnd As Form_frmAccountList
Private AccountId As String
Private Sub cboDateFilter_Change()
Call updateLedgerRecordset
End Sub
Private Sub cboDateFilter_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
cboDateFilter.Dropdown
End Sub
Private Sub cmdReconcile_Click()
Dim frm As Form
' Search if the form is already open
For Each frm In Application.Forms
If CurrentProject.AllForms(frm.NAME).IsLoaded = True Then
If frm.NAME = "frmReconcile" Then
' Error Message
MsgBox mmgrGetDialog("frmAccountList.Reconcile.AlreadyOpen"), vbExclamation
Exit Sub
End If
End If
Next frm
' Open reconciliation form
DoCmd.OpenForm "frmReconcile", acNormal, , , , acNormal, AccountId
End Sub
Private Sub cmdViewTransaction_Click()
Dim sArgument As String
Dim trId As Long
Screen.PreviousControl.SetFocus
If IsNull(Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]) Then
Exit Sub
End If
' Get the transaction ID to show
trId = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
' Generate argument string to pass to transaction form
sArgument = Join(Array(enumFormNewTransactionOpenMode.OpenView, trId))
' Open transaction form
DoCmd.OpenForm "frmTransaction", acNormal, , , , acNormal, sArgument
End Sub
Private Sub cmdDeleteTransaction_Click()
Dim dbs As Database
Dim qdf As QueryDef
Dim msg As String
If IsNull(Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]) Then
Exit Sub
End If
msg = mmgrGetDialog("frmAccountList.DeleteTrasanction.AskYN")
msg = mmgrSetParam(msg, 1, Me.subAccountLedgerList.Form.Recordset![DATE])
If MsgBox(msg, vbYesNo + vbExclamation) = vbYes Then
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryTransaction_Delete")
qdf.Parameters("lTransaction").Value = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
qdf.Execute
qdf.Close
dbs.Close
Me.subAccountLedgerList.Form.Requery
End If
End Sub
Private Sub cmdEditTransaction_Click()
Dim sArgument As String
Dim trIdNew As Long
Dim trIdOld As Long
Dim dbs As Database
Dim qdf As QueryDef
If IsNull(Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]) Then
Exit Sub
End If
' Add a new transaction with old transaction date
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryTransaction_CopyTransaction")
qdf.Parameters("lTransactionToCopy").Value = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
qdf.Execute
qdf.Close
dbs.Close
' Get ID from just inserted transaction
trIdNew = getLastInsertedId()
'Copy ledgers to new transaction
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryTransaction_CopyLedgersWithStatus")
qdf.Parameters("lTransaction").Value = trIdNew
qdf.Parameters("lTransactionToCopy").Value = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
qdf.Execute
qdf.Close
dbs.Close
' Generate argument string to pass to transaction form
trIdOld = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
sArgument = Join(Array(enumFormNewTransactionOpenMode.openEdit, trIdNew, trIdOld))
' Open transaction form
DoCmd.OpenForm "frmTransaction", acNormal, , , , acNormal, sArgument
End Sub
Private Sub cmdCopyTransaction_Click()
Dim dbs As Database
Dim qdf As QueryDef
Dim sArgument As String
Dim trId As Long
If IsNull(Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]) Then
Exit Sub
End If
' Add a new transaction with actual date
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryTransaction_AddNew")
qdf.Execute
qdf.Close
dbs.Close
' Get ID from just inserted transaction
trId = getLastInsertedId()
'Copy ledgers to new transaction
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryTransaction_CopyLedgers")
qdf.Parameters("lTransaction").Value = trId
qdf.Parameters("lTransactionToCopy").Value = Me.subAccountLedgerList.Form.Recordset![LEDGER1.TRANSACTION]
qdf.Execute
qdf.Close
dbs.Close
' Generate argument string to pass to transaction form
sArgument = Join(Array(enumFormNewTransactionOpenMode.openCopy, trId))
' Open transaction form
DoCmd.OpenForm "frmTransaction", acNormal, , , , acNormal, sArgument
End Sub
Private Sub cmdNewTransaction_Click()
Dim dbs As Database
Dim qdf As QueryDef
Dim sArgument As String
Dim trId As Long
' Add a new transaction with actual date
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qryTransaction_AddNew")
qdf.Execute
qdf.Close
dbs.Close
' Get ID from just inserted transaction
trId = getLastInsertedId()
' Generate argument string to pass to transaction form
sArgument = Join(Array(enumFormNewTransactionOpenMode.openNew, trId))
' Open transaction form
DoCmd.OpenForm "frmTransaction", acNormal, , , , acNormal, sArgument
End Sub
Private Sub updateLedgerRecordset()
Dim dbs As Database
Dim qdf As QueryDef
Set dbs = CurrentDb()
Select Case cboDateFilter.Value
Case 0 'Last month
Set qdf = dbs.QueryDefs("qryAccountList_LedgerListWithBalance_Limited")
qdf.Parameters("lDayView").Value = 30
Case 1 ' Last 3 months
Set qdf = dbs.QueryDefs("qryAccountList_LedgerListWithBalance_Limited")
qdf.Parameters("lDayView").Value = 90
Case 2 ' Last year
Set qdf = dbs.QueryDefs("qryAccountList_LedgerListWithBalance_Limited")
qdf.Parameters("lDayView").Value = 365
Case 3 ' All
Set qdf = dbs.QueryDefs("qryAccountList_LedgerListWithBalance_All")
End Select
qdf.Parameters("AccountId").Value = AccountId
' Set the new recordset
Set Me.subAccountLedgerList.Form.Recordset = qdf.OpenRecordset()
End Sub
Public Sub InitializeFormInstance(acctId As String)
Dim dbs As Database
Dim qdf As QueryDef
Dim sInactive As String
' Store the selected account id in module scope variable
AccountId = acctId
' Set the form handler
Set formHnd = Me
' Set the subform recordset
Call updateLedgerRecordset
' Disable transaction function for inactive accounts
If Not getAccountActive(AccountId) Then
Me.cmdNewTransaction.Enabled = False
Me.cmdDeleteTransaction.Enabled = False
Me.cmdCopyTransaction.Enabled = False
Me.cmdEditTransaction.Enabled = False
Me.cmdReconcile.Enabled = False
sInactive = " [Inactive]"
End If
' Disable Reconcile button for expenses and incomes accounts
If getAccountType(AccountId) = enumAccountType.typeExpenses Or _
getAccountType(AccountId) = enumAccountType.typeIncomes Then
Me.cmdReconcile.Enabled = False
End If
' Set form caption
Me.Caption = getAccountName(acctId) & sInactive
'Activate the form
Me.Visible = True
End Sub