Functions

A function is a special type of Stored Procedure because it returns only one value. MS Access allows us to write procedures and programs (called 'code') - statements that hook up groups of actions and SQL commands. Let us write a program on MS Access as an exercise. To begin with, let us try to enter complete data into our database. Let us say that a new member walks in, makes a payment, and becomes a member. In this case, we will need to update three tables:

• The GymMembers table needs to be updated with his or her name, address, gender, and membership expiry date.
• The GymService table should be updated will all the services he or she is entitled to
• Information about the payment amount and type of scheme should be entered into The MemberPayment table.

Only when all the above updates are done will the database entry be complete. This exercise has been designed to give you a good idea on how all of this could be done on a DBMS using modules and functions. In the case at hand, it would be very hard for the attendant at the gym to enter data directly into the database. So, using forms will make data-entry and modification easy for him or her.

The two forms in the HealthyGym database form part of this exercise. To access these forms, click on the 'Forms' tab on the left hand side under 'Objects' in the main Healthy Gym menu. You will see a list containing two forms: SelectOperationType and EnterMember. These two forms are linked - that is, you first select an operation type and then enter a new member. Double click the select operation type form to open.

ScreenShot 8a: The SelectOperationType Form

Select 'Enter New Member' and click on the 'Enter Details' button. This will take you to the Member Entry form. The MemberID, and date fields will be automatically filled out. Enter the following data in the form.

Scheme Type	select 'Quarterly'
Name	Millicent Fauberge
Address	12A, River View Ln
Select Gender	Select 'F'
Date	*Do Not Change*
Amount	*Do Not Change*
Membership Start Date	01-Oct-2006
Membership Expiry Date	01-Jan-2007

You may have noticed that the amount field automatically changed when we selected a scheme type and that you can no longer change this amount. How did we tell these forms to perform such? How will a new member be added into the database once the 'Add Member' button is clicked?

Every control - every place in a form where you enter information or click - has associated 'events'. That is, every time something happens in one of these places, the cursor moves into them, information is entered into them, and so on; the associated event is 'triggered'. Now, if you want something done every time one of these 'events' happens, all you have to do is write a Procedure and hook it up to the event. For example, the 'afterUpdate' event associated with the Scheme Type selection list is triggered every time a new selection is made from the list - was set to perform an action that translates to 'Change the amount text entry field to reflect the cost of this scheme'.

Screen Shot 8b: Some MS Access events and explanations from MS Access help

Let us go to the 'Add/Modify Member' button at the bottom of the EnterMember form. Right now, nothing happens when you click on it. Let us make it work then. Basically, we need to ‘hook up’ some Procedure or Code to the Click event of this button. Every time someone clicks on the button, the onClick event will be triggered and all our associated code will be executed

• Right click on the 'Add/Modify member' button and select 'properties'.

Screen Shot 8c: Select Properties

• Now, click on the 'Events' tab. Click on the text field next to 'On Click'

Screen Shot 8e: Click on '...'

• Now, click on the '...'box next to the text field. Select 'Code Builder' on the pop up.

Screen Shot 8e: Select Code builder

• You will be directed to a window that looks like this:

Screen Shot 8f: VB Module

• Now, copy and paste the following code segments in order between 'Private Sub Add_Member_Click()' and 'End Sub'. These two lines simply indicate that they contain the program that is hooked up with the Add_Member button (note that although the caption on the button is Add /Modify Member, its name is just Add Member - this is how it is referenced in Access). Below each code segment is an explanation.

 Dim action As Variant
Dim MemberID, MemberName, MemberAddress, MemberGender, MemberExpire As Variant
Dim PaymentDate, PaymentAmount, PaymentActivate, PaymentExpire, SchemeID As Variant
Dim EnrolledServices(5) As Integer, i, totalServices
Dim rs As ADODB.Recordset

The statements above are called declarations. They are like saying 'Marlon is a man' or 'Ginger is a cat'. Basically, they are of the form Dim AS . The names are called 'variables' because the value they hold may change - basically, the name refers to a value that is stored on the computer. The variant datatype holds all sorts of data - text, numbers, or dates. Enrolledservices(5) is the declaration of a 5-element array of integers - that is, five integers that are saved in the computer under the names EnrolledServices(1), EnrolledServices(2) and so on. The last line declares a complex variable that holds the command and result of a select query or the command that executes Insert and Update queries.

If Forms![SelectOperationType]![actionRequest] = 1 Then
action = "Update"
Else
action = "Create"
End If
MemberID = Forms![EnterMember]![MemberID]
MemberName = Left(Forms![EnterMember]![MemberName], 50)
MemberAddress = Left(Forms![EnterMember]![MemberAddress], 50)
MemberExpire = Forms![EnterMember]![PaymentExpire]
MemberGender = Left(Forms![EnterMember]![MemberGender], 1)
PaymentAmount = Forms![EnterMember]![PaymentAmount]
PaymentActivate = Forms![EnterMember]![PaymentActivate]
PaymentExpire = Forms![EnterMember]![PaymentExpire]
PaymentDate = Forms![EnterMember]![PaymentDate]
SchemeID = Forms![EnterMember]![SchemeID]

Here, we put some of the data that was filled into the forms into the variables we declared in the previous step. First, we see if the user wants to add a member or update- the variable named 'action is set accordingly. The Forms![SelectOperationType]![actionRequest] here refers to the actionRequest control or entry field in the SelectOperationType form. The actionRequest field was the field where you selected 'Add New Member'. Similarly, the Forms![EnterMember]![MemberName] refers to the name you entered on the EnterMember Form. Note that the Left() function cuts this name to 50 characters since our database field (MemberName) can accommodate just this many characters.

totalServices = 2
EnrolledServices(1) = 1
EnrolledServices(2) = 2
If SchemeID >= 4 Then
totalServices = 4
EnrolledServices(3) = 4
EnrolledServices(4) = 5
End If
If SchemeID >= 7 Then
totalServices = 5
EnrolledServices(5) = 3
End If

The abovementioned steps deal with setting the number of enrolled services according to the plan the user has subscribed for. If you open the 'GymService' table, for instance, you will see that 'Cardio' has an ID 1, 'Weights' has the ID '2' and so on. Also, if we check the enrollment scheme table will tell you that the quarterly, half-yearly and annual plans include Cardio and Weights while the plus plans include sauna and massage along with cardio and weights and super plans include all five services. In the above section, we set the Enrolled services array to the services that the newly enrolled member is eligible for. All schemes qualify for service 1 and 2 - Cardio and Weights; while those with scheme IDs greater than or equal to 4 (plus, super, and monthly) qualify for sauna and massage in addition to the first two. Those with schemeIDs higher than or equal to 7 qualify for weekly aerobics also. Thus, if someone has opted for quarterly super with Scheme ID, the enrolled services array would contain 1,2,4,5, and 3 in the first, second, third, fourth and fifth places.

 Set rs = New ADODB.Recordset
If action = "Create" Then
rs.Open "Insert into GymMembers Values (" & MemberID & ",'" & MemberName & "','" & MemberAddress & "',#" & MemberExpire & "#,'" & MemberGender & " ')", CurrentProject.Connection
Else
rs.Open "Update GymMembers set MemberName = '" & MemberName & "', MemberAddress = '" & MemberAddress & "', MemberExpire = #" & MemberExpire & "#, MemberGender ='" & MemberGender & " ' WHERE memberID = " & MemberID, CurrentProject.Connection
End If 
If IsNumeric(PaymentAmount) Then
rs.Open "Insert into MemberPayment (MemberID,PaymentAmount,PaymentActivate,PaymentExpire,PaymentDate,SchemeID) Values (" & MemberID & "," & PaymentAmount & ",#" & PaymentActivate & "#,#" & PaymentExpire & "#,#" & PaymentDate & "#," & SchemeID & ")", CurrentProject.Connection
End If 
If action = "Update" Then
rs.Open "Delete from MemberService where MemberID = " & MemberID, CurrentProject.Connection
End If 
For i = 1 To totalServices
rs.Open "Insert into MemberService (MemberID,ServiceID) Values (" & MemberID & "," & EnrolledServices(i) & ")", CurrentProject.Connection
Next
MsgBox ("Information updated!")
DoCmd.Close acForm, "EnterMember", acSaveNo

Finally, we will perform the actual database operations. First, we should initialize the recordset object declared earlier. Next, we check to see if the action we want to perform is adding a new member or updating an old member and, accordingly, issue an INSERT or UPDATE command. Note that we substitute variables like MemberID and MemberName that we had declared and set to equal values from the form into the command we send to the database. Whenever the program sees the name of a variable, it substitutes its value into the string. To illustrate, if we issue the following commands

name= "Vincent" 
mySentence = "My name is " & name

The string called mySentence would be set to "My name is Vincent". Back to our program: after we insert into or update the GymMembers table, we add an entry in the MemberPayments table for this payment if it is not just an update of a name or address. That is, we insert this line if the payment amount field contains a number - we assume that this means that a payment is made. Finally, we delete any existing records in the MemberServices table for this member and insert the list of services the new enrollment entitles him or her to. The words Current_Project.Connection tells the DBMS to execute the commands on the same database that the forms are saved on - that is, Healthy Gym. Once everything goes well, we print a message that says 'Information Updated' and close the Enter member form.

Once you have copied all the pieces of codes between 'Private Sub Add_Member_Click()' and 'End Sub', hit the 'save' floppy drive icon on the top pane. Navigate back to the EnterMember form using alt-tab. Now, click the Add/Modify Member Button. You should get the 'Information Updated' message. If you do not, check and make sure you have copied the code exactly as it appears above or use this link to cut and paste the entire program at once and try again.

The above exercise should have given you an idea of what DBMS can do even if you did not really follow the intricacies of the programming. As you can see, a DBMS can be used as a powerful tool that does a lot more than just accept or modify data. To continue, the next chapter will sdiscuss how a DBMS can accept data submitted on the internet.