Separate speaker notes to accompany class data:
Previous versions of PrSavAcct have not held the data in an Access database for use. In version PrSavAcct07.vbp the Access database contains the data.
In writing this program I tried to move forward from the concept of a collection that we have seen in the previous version. Essentially I wanted to have the database act as the collection acted when I wrote this code. This would probably not be the way you would code this problem if this building process was not your goal.
This slide shows the results of bringing up the accounts and the transactions to accompany account 2222.
This shows the savacct database which is made up of two tables: SavingsAccounts and Transactions. The data currently in the database is shown.
Notice the excellent interest rate given to the customers of this bank!
In going through this program, I am going to show the code in the form and the different modules briefly and then I will go through the code that would be triggered by different click events on the form.
This code is the code in frmSavAcct07 which is the form that I am using. The form code is continued on the next slide.
This shows the code for the Deposit. Again note that it uses colSavAccts which is no longer associated with the collection, but is now associated with the database.
This shows the routine that displays the Savings Accounts on the form. Clearly this routine would need to be altered if I was dealing with a large database.
VB allows the For Each only under certain circumstances. In this code I went to using a standard For...Next which starts with the 0th element and goes to the count -1 element.
The code in cmdDsplyTrans_Click shows the routines to display the transactions associated with the active Savings Account on the screen.
The code in cmdReset_Click simply resets the program variables, buttons, etc. and sets the focus.
We are still looking at code associated with the form. These routines retrieve an account or remove an account according to the button clicked on the form. The use will be clearer when we follow each of the click events in the later part of this slide show.
More click events on the form that use the database.
The lstDsplayAcct_Click routine makes the transactions corresponding to the account clicked on come up automatically. This is a new routine that was not needed because of the database, it just made things work more smoothly.
This is the end of the code associated with the form. The next set of slides will show the code associated with each of the 5 class modules. Note that the collection class modules are no longer there, instead we have DBI and Connection modules.
This is the first slide of the SavAcct class module.
Notice that we now have a Let Balance. This was not used in the other versions. However we are now using a database and because of persistence in a database we need to be able to load values into the savings account. Note that Boolean indicators are originally defined as false. When it is false we take the vBalance passed to the Let and put it into curBalance and then set the Boolean indicator to true. This means we can load once but we still do not have the ability to change directly. Changes to the balance happen in the methods to deposit and withdraw etc.
This continues the code in SavAcct which is the class module of the individual savings account where you can access the information about an individual account.
The Property Get Transactions() As TransactionDBI gives reference to Transaction DBI associated with class (specifically the particular savings account that is active). Essentially it gives access through a particular savings account to transactions associated with it on the database.
This reads SavAcctConnection(SavAcctConnection07.cls). This module establishes the connection to the savacct.mdb database. The connection object.
This code shows the SavAcctDBI. In the general area, I have SavAcctConn as New SavAcctConnection. This instantiates the connection object as a SavAcctConnection for this particualr database0. I also have SavAcctRS as ADODB.Recordset.
Database tables are encapsulated into object. The program deals with savings account objects.
The Remove subroutine deletes associated transactions using the SQL and then deletes the account.
This routine accesses a particular savings account. Again remember, that I tried to take the code from the collection and keep the same logic when I converted to using a database.
Note that VarType tells what type of variable a variant contains. In this example I am trying to determine if I have a key or an index. vbLong tells me it is an index while vbString tells me it is a key.
Update retSavAcct works on the existing account prior to retrieval associated with Item.
This shows the routine to Update.
This is the Class Module Transaction. It is the same as previous versions.
This is the beginning of the Class Module TransactionDBI. In the General area SavAcctrConn as New SavAcct Connection and TransactionRS as ADODB.Recordset.
This function returns a transaction object. The function is used when displaying transactions on the form.
This code shows the subroutine that sets up the transaction with data from the TransactionRS (recordset), the count function which gives a cound of the recordset and the Add subroutine.
Remember, the form only deals with savings account objects and transaction objects. It does not deal with the database.
This code grabs only the transactions with the account number that is passed to LoadRecordSet.
This is the form. We are now going to follow the code when a particular button is clicked.
Form load instantiates colSavAcct as New SavAcct DBI and that causes the SavAcctDBI to be loaded into memory and two things to happen:
First, the SavAcctConn as New SavAcctConnection statement is processed which causes the instantiation of the SavAcctConnection class and the execution of SavAcctConntetion class intitialization routine (shown on next slide) which establishes the connection to the database and opens the database.
Second, the SavAcct DBI class initialization routine is executed which creates and fills the SavAcctRS from the database table.
Remember defining AS NEW is one of the causes of instantiation. The other cause is SET...NEW.
This sets up the path and makes the connection to the database and then opens the database and then it goes back to SavAcctDBI which sets up the record set (see next slide).
This sets up and creates the recordset SavAcctRS which is based on opening the results of the SQL Select.
Note that Set SavAcctRS = NEW ADODB.Recordset instantiates the record set and Open fills the recordset with the results of the Select since we have specified adCmdText.
This code will examine what happens when the user clicks on the Retrieve Account button on the form.
Remember that in Form_Load colSavAccts was Set as New SavAcctDBI so we now go to the Item Function in SavActDBI to retrieve the record which sets the reference of objSavAcct.
We pass Item wkAcctNbr and it returns the account from the database.
Item was passed wkAcctNbr which is vKeyIndex which is a variant. Note that the function is set up as SavAcct which means it will return a savings account object. SavAcct is a class with the properties of account number, balance and interest rate.
Before we do anything else, we update the retSavAcct which essentially updates the old record, if there is one, before we move on to the first record.
We are now going to use VarType to examine that variant and determine if it is an Index (vbLong) or a Key (vbString). In our case we are passing a Key so it will be a string.
To locate the record, we use the recordset and move to the first record. We then do a search based on a match of the vKeyIndex we received with the AcctNum in the database. If a match is found we call the routine called SetUpSavAcct which establishes retSavAcct. On our return we set Item = retSavAcct.
This shows the code to update the active record before dealing with the new record. Note that it checks to make sure there was an active record before executing the code which involves finding the record on the recordset and then updating it. Again note that the requery puts the recordset in order.
This code instantiates a new object retSavAcct and passes the fields from the database to properties of the SavAcct object called retSavAcct. This uses the Let property procedures in SavAcct for AccountNumber, IntRate and Balance.
After the three data fields there is retSavAcct.LoadTransactions which uses strAcctNbr which is the module level variable in SavAcct whichcontains the results of the Let AccountNumber and puts the transactions with the matching account number into colTransactions which is of type/class Transactions DBI.
LoadRecordSet receives the account number and sets up the select to retrieve the transactions with a matching account number. This transaction set is now TransactionsRS which was defined in Transactions DBI as ADODB.Recordset
We are looking at buttons the user can click on the form to cause processing. In this example, the user has clicked Add Account. This will send the wkAcctNbr and the wkIntRate to colSavAccts.Add which is of Class Module SavAcctDBI as seen in the DIM statement at the top of the screen. SavAcctDBI is shown on the next screen.
The current account that has been being processed is updated. This is done using the Update routine which is passed retSavAcct.
Then starting at With SavAcctRS, a new account is added. The field names receive the data that was passed to the procedure.The database record is updated to reflect the data that was put into it. The record is added at the end of the database table. The Requery updates the recordset putting the new record in its proper order.
This slide shows the code that will update the account that has just been used providing there has been some activity. The check for <> "" checks for activity. If there has been activity, SavAcctRS is used. The following screen shows the initialization of SavAcctRS.
Remove Accounts passes the wkAcctNbr to the remove method in colSavAccts which has been defined as SavAcctDBI.
The next slide will show Remove.
First the update of the existing active record (if any) is done - see slide under Add Account for detailed information.
After setting retSavAcct to nothing the code then attempts to retrieve the record with account number that was passed as vAcctNbr from the database table. If the retrieval was successful and the balance is 0 then the transactions associated with the account are deleted from the transactions database table using the select and then the SavAcctRS.Delete is executed to delete the record from the savings account database table.
The check is made to see if the delete has left an empty table before the requery is issued.
It seems like a good time to remind you that when you put a list box on the form you are in fact putting an object on that form and that that object has standard methods associated with it. In the case of lstDsplyAcct.Clear the lstDsplyAcct is a list box which will contain the accounts and Clear is one of the standard methods associated with the list box.
The if statement is checking colSavAccts.Count. The object colSavAccts is defined as an object of the type/class SavAcctDBI. Count is a property implemented as a function associated with SavAcctDBI (see next slide).
The count function uses the SavAcctRS object and uses a predefined RecordCount property. The Count is returning the number of records in the recordset.
Next, i is defined as long and the for loop is set to go through the record set from the 0th element to the count -1 element.
Now lets look at objDsplyAcct which is an object of the SavAcct class. The set statement uses the Item method (implemented as a function) to retrieve a SavAcct object indexed by i. This means the loop will start with the first savings account.
This time I am passing Item an index rather than a key. It will then move to the record using adBookmarkFirst. Remember that I am trying to do things the same way as I did the collection and using a collection you can retrieve by index or key, therefore I wanted to retrieve from the recordset by index or key. In other words, I wanted the SavAcctDBI to work the same way that the SavAccts collection worked.
Back to the code, Move vKeyIndex, adBookmarkFirst means start at the first record and then use vKeyIndex to move from there to the desired record. Remember I use this Item method for each pass in the For loop within cmdDsplyAccts_Click.
As long as it is not EOF it will call SetUpSavAcct and then set Item equal to the return. SetUpSavAcct is shown on the next slide.
SetUpSavAcct actually sets up the savings account with the information from a database record. Note that I set retSavAcct = New SavAcct which instantiates it. Then I take the AcctNum from the recordset and assign it to retSavAcct.AccountNumber. The same is done with the IntRate and the Balance.
This means that when the routine is done I will have retSavAcct available in the Item method (previous slide) to use in the Set Item = retSavAcct code.
Now lets look at the transaction processing. This processing deals with the transactions associated with a particular account and puts them in a transaction recordset called TransactionRS. Looking at the steps:
LoadTransactions creates something analogous to the collection of transactions associated with the account when I was simply using collections. SavAcct has in its General area Private colTransactions As TransactionDBI. This means that the access to TransactionDBI is through SavAcct. So, I want to get the recordset of transactions associated with the current value in strAcctNbr. I do this by using the LoadRecordSet method which is in TransactionsDBI passing it the appropriate account number so it can get the transactions and open them as a transaction recordset called TransactionRS.
This is done because for each account I need the associated recordset of transactions so that they can be placed in the transaction list box.
I am going to try and explain this by showing a diagram. The database has two tables Savings Accounts and Transactions. They are separate entities. Remember with the collection each savings account contained the transactions associated with it. Well in this case I have to retrieve the transactions from the data base, so I set up the transaction recordsets in each savacct object. Each SAVACCT will have a TransactionsDBI recordset associated with it and that is what I achieved in the code on the previous slide.
Note that when I execute this program and click on the account in the savings account list box on the left, the transactions list box on the right gets filled because the recordset associated with that savings account is used.
This routine causes the transaction list box to be filled based on selecting an account in the account list box.
First I call the cmdReset_Click which clears everything out.
Then I need to get the account number of the item in the list box that has been selected. This is done with Left(.List(.ListIndex),4)
Note that .List is the collection of items in the list box and .ListIndex is the pointer to get the correct item.
Note that cmdRetrvAcct_Click has been covered and that subsequent slides will cover cmdDsplyTrans_Click which is accessed either through this routine or through the cmdDsplyTrans_Clcik click event.
I am setting a With here that accesses a particular item and its transactions. Transactions is a property of the SavAcct and this gives you access to the transactions associated with the particular account. Remember that Item returns the savings account as SavAcct. It can then be used to get the associated transactions.
Note that the Property Get Transactions() sets Transactions to the reference for the transactions associated with the account.
The Item method is shown again on the next slide.
The cmdDsplyTrans_Click sends wkAcctNbr to the Item method shown. It comes in as vKeyIndex. It is an account number so it is a key and gets processed in that part of the code. After calling SetUpSavAcc Item is set to retSavAcct.
After the account number has been found, SetUpSavAcct() is called. Within this routine retSavAcct.LoadTransaction is used to retrieve the related transactions which are finally made available to cmdDslpyTrans_Click.
The for loop then displays the information about the transactions by going through the transactions one at a time and then displaying the line in the transaction list box.
Note that the display line set up here has transaction information.
The execution at the bottom-left shows the user entering an account number and then clicking on Display Transactions.
The execution at the top-right shows the user clicking on an account in the savings account list box and the resulting display of the associated transactions in the transaction list box.
The user clicks Deposit and cmdDpsit_Click() is executed. First the account needs to be retrieved using the Item method.
We are now at the Item method where the previous account is updated (if there was activity) using Update retSavAcct. Then the savings account number was retrieved (using the index). At this point, Call SetUpSavAcct was executed and the retSavAccount was used in Set Item = retSavAcct. This means I have the reference to the account.
Note that I also load the transactions associated with the account. I do this when ever there is account activity.
Relooking at the cmdDpsit_Click() the first thing is colSavAccts.Item(wkAcctNbr).Deposit wkTranAmt.
This does the deposit using the account number from the savings account object gotten through the Item method. It now does the Deposit method on that account.
The transaction amount is added to the balance. We are not writing the account out here. This will be dealt with in the update of previous accounts that we have seen repeatedly.
Next we do colTransaction.Add passing the account number, the DEP and the amount of the transaction.
The deposit receives the account number, the transaction type and the transaction amount.
The Add method develops a transaction number TranNbr and then does a TransactionRS.AddNew which puts up a new record. We then put data into the fields and Update which puts the transaction onto the transaction table in the database.
Deposits and withdrawals are done in memory but they are not actually written to disk until the next transaction. Note that this means that you also have to write the last transaction when you exit the program.
Until I was doing this presentation and testing, I had overlooked the fact that you have to Update retSavAcct as you leave the project or the last transaction will not be completed. The revised code is shown here. I added the code to Class_Terminate().
Withdrawal and applying interest follows the same path that was discussed in deposit.
cmdReset Initializes variables, locks fields and enables buttons. The focus is then set to txtAcctNbr.